How to dynamically set Google Sheet and Worksheet custom values

  • 17 August 2020
  • 10 replies
  • 916 views
How to dynamically set Google Sheet and Worksheet custom values
Userlevel 3

The Goal

Sometimes we want to update a specific Google Sheet and Worksheet dynamically using Custom Values.

These ID values may be passed from or created by an earlier step of the Zap.

What we notice when we try this in the Zap Editor is that the Columns from the Sheet aren’t pulled in, we aren’t able to map fields from the previous step and we see the following message:

Zapier had trouble retrieving custom fields from Google Sheets.

mBMUd5BnfuQmc_aXFlaNc7bRrv5MasUNFyRKqXZzfMXfD9HMaNkCQxzWFLYbz9g4wHzBBz8HL7mqIoMCjK5cMi-8eaZt3b4Z_vSe5pSsE02M4SGVCNxDSHYq3V98l_teRwPoExoA

There is a way to make this work.

The only requirement is that *your Sheets must have the exact same column headers in the exact same order.*

The steps below currently work for the Create Spreadsheet Row and Update Spreadsheet Row actions. To make this work with the Create Spreadsheet Row(s) action would require a multi-Zap setup.

The Setup

Let’s step through a simple example of how this might work.

In this example, we have 3 different Google Sheets (one for each of our Sales Agents)

f7qcK6pMjMgiajygHhTbOC7f8u-c1Egnj0NpiXsd2EWtQe-O8gjBm7WLtsftMlDzdCDLkC_Lqn-pvs8FLjFU_MQGIoBXmfQEoT-ebXLTJGDIPGqHAvLvTbyJR7OtEwd5b7YZGw62

All 3 Sheets have the exact same headers (Name, Email and Phone)

fTd3gcIiK1Viyc9RKQ-Wfbochws5reeR9YufkocYMiNOwMbbRuYbG0Q6gNahRx4yTRkZHU08qMjh6N_LV2XHL0fIq6ZpSTvQ_ZbCd2P4aoz8Z_T6n8OulimntxBMeBKut9WWjwUd

We also have a Lookup Sheet where we have both the Sheet ID and Worksheet ID for each Agent’s Sheet.

8g-t6KDYHJbaEAZsw3wWkgLhPYtXFkp-5wPWORdL9L-kiyQTHnc14A2V0aGr0YsJBQbifxYouuB8QN-VuCEu3IrLPFDa3XTVRkarP-ulKxISuquuWm5oLvVOZv6eLPyysHET5Kv-

Our Zap triggers when we receive a Webhook with New Lead data.

Steps 2 and 3 of our Zap use a Round Robin setup to assign the Lead to 1 of our 3 Sales Agents.

Step 4 of our Zap does a search on the Lookup Sheet to get the Sheet ID and Worksheet ID for the Agent selected by the Round Robin.

So far - So Good. 

tAh1TESZlcr91pM-3OzzgDCOzBD-T1p5Xb2E0czwYTeOLrqxqAP3bsAVtYEO_pZehXLXKlM0qtSRUWXDsyXb9JUn-goGkszpdqKtCLdSojpq_KZRrTD-QSobzxhnsoFZJ1r1bPX5

The Challenge - Zapier had trouble retrieving custom fields from Google Sheets

We now want to add the New Lead to the matching Sheet and Worksheet for the Agent assigned via the Round Robin in Step 3.

To do that, we add a Create Spreadsheet Row step to the Zap and we map in the ID values from Step 4 as Custom Values for the Sheet ID and Worksheet ID.

But we get the following Message in the Zap Editor.

Zapier had trouble retrieving custom fields from Google Sheets.

XqvT5XC70Pwg_9WpJJD169t0u8cHeQzdhuSpcnMAPTqoeq3SOVMFWHZqNSgyNJoIfjCSRuy_ZSYrNmkw9cmNHMwOc3xqlSwwDFr_z7IPYH8cf6hXR5YBF2diBT2bYCGpcjKOZ5np

We haven’t been able to pull in the Columns from the Sheet using the Dynamic Values for Sheet ID and Worksheet ID.

And because we don’t have the Columns in the Zap Editor - we can’t map the Lead Data (Name, Email, Phone) from the trigger step.

The Solution

In our Create Spreadsheet Row step - we want to do the following.

  1. We select one of the Sheets and Worksheets directly from the Drop Down lists and notice the Columns from the Sheet (Name, Email, Phone) show up now in the Zap Editor.

    tv6fyIrb4xueDo4WNEF08UHdwUg_HzCW98L6_9uPQpwml72MYZOGiSdkC-t10h_uW91vRpJtfB5NYDgEm_rfRXNnhe1Gt8xyvKkk5Sh1PgziP0M3wFFv9gwej8Ms2dKI0DCePp8s
  2. Next we want to map the fields from the trigger step like we normally would (and click CONTINUE)

    pUO08Mn4NqYlbtRuOnvCsHhxWMH_YQ9R-Ltr2f_RIHZbbJtL7SCv7RqQdGlnnHGUudBpuqwOfnFOmAc-L8Z9l0EbDgYWCzq2NYEBshjr74wYNuOpcPX5IRfHgd1nAmdPyKHR6eG9
  3. We aren’t going to test this step of the Zap. Instead - we are going to click to go back into the Customize Spreadsheet Row section.

    xE5IL8DLUWtsrvRqt6IDvO_q3ffRn3NPzHLS2iQHZsDF0V0Y2yxhS6Q_FJ6WKWC65wfnG46HB8vLyVjxOkQfEKOX1fjbm9xlYVGQm0ERYFYRWBDXBVnQCjtmYJ18jPzMbnU3TtMY
  4. And we are going to change the Sheet ID and Worksheet ID to be Custom values again. We now see a message about Extra Fields which we can ignore and click CONTINUE.

    yTnX9wM0Ft4jQxDG_O9DZCW9KV76SbwTgF_NDY9B5o-Y7VSdTpvJnyE8GxYwvHw8pPu5EVzgrNc9Bogg2bN6xfhEzPkKyIC2GQ_Vu3N3OtR-3zwhxmwWnyLdMM8FBfTSFcDYUjUH
  5. Finally - we can SKIP TEST and turn our Zap ON. (You can also try the test here and it should work)

    QujJezctdVvSquixTWjiT-DXASmJmJGZm-F3ycAvq1jiEs_FGK5nlozq45xoz6y4SZI9zrTlFKO8QtTF5CFh0ecvBYmx-y9BUV64zDoLqWOuTGDEeeJOAOrJH9wpGPIAePU1YGfk

The Result

Now when this Zap runs live - the Sheet and Worksheet IDs will be dynamically updated based on the Lookup in Step 4.

v-PIL7bGGRXtTYrupL0zohpyA-j333_BV491agfvBWK6dp-PSDPyHoLKm1dhMKMZe7YeeX-OGUM2xFnizBzhab9Vw-FVESBTfmHsoocA-pVywxMW2BupBmoMa-12rh6q0auN271t

Step 5 will work because we are able to send Google the Sheet ID, Worksheet ID and column ID’s that are needed to add the record to the Sheet.

And the Sheet is Updated Dynamically. :)

qCnRYwBoyWbdbt2MjnOoT7UYInzcvhRB1QQmkjV7-Rx5C01eD9_CTKC5-HHg1fit-N4kjqnJR4rwKjCLuom_DUNNAFQZz92-UYo3JhqjM8JY_O-RyxEEyqvCrgLU4CzJfYDj8403

The key to this workflow is selecting one of the Sheets and Worksheets from the Dropdown first. This pulls in the columns from the Sheet and allows us to map the values from the trigger to the correct columns.

Once the columns are mapped - changing the Sheet and Worksheet back to custom values enables the Zap to update the Sheet and Worksheet dynamically when the Zap runs live.

Does this work for Apps other than Google Sheets?

You may see a similar message in Apps other than Google Sheets.

Zapier had trouble retrieving custom fields from APP NAME

If you are trying to dynamically assign the item to be updated in that action - this same approach may help as well.

It won’t work for every situation. But the steps to try are always the same.

  1. Choose an actual option (not a Custom Value) from the dropdown menu and map the fields as you normally would.
  2. After the fields are mapped - change it back to use the Custom Value.
  3. Ignore the Extra Fields Message.
  4. Turn the Zap ON and live test it to make sure it works.

Thank you for reading :)

I hope this post has shed some light on how to dynamically update the Sheet and Worksheet custom values and enables you to build more powerful Google Sheets Zaps!

 


10 replies

Userlevel 4
Badge +2

Thanks for writing this up, @Jared ! It’s great to have this documented here in the community in such a clear way :)

@Jared  @TimS Not working for me. Even Spiking the Test the step is getting an error.

Error Message:

32 Google Sheets: The app returned "Requested entity was not found.".
Userlevel 3

Hey @IKE - thanks for giving this a try! My first guess with that error is going to be that we don’t have the correct value for either the Spreadsheet ID or the Worksheet ID. Google is basically responding that the can’t find the Sheet or “entity” with the values we’ve given them.

If you look at the URL when on the Sheet and Worksheet you want - you should see the values displayed there.

One other reason we could see that error would be that the Sheet isn’t in a Google Account that can be accessed with the Google Account connected in Zapier.

It would be unusual to get an error when Skipping the Test as this generally just skips it without doing any type of request to Google.

If those don’t get it working - you can always send in a message through the Help Form and we can take a look at the specific Zap!

Thanks Jared, filled the help form yesterday, but no response yet. 

Just double check the spreadsheet ID and Worksheet ID

 

I am finding the file id by name the step previous, 

For some reason the ID’s are cleaned and used an empty value 

Documentation page (https://zapier.com/help/doc/common-problems-google-sheets-zapier) says at the very end “The only surefire way to update Google Sheet data is to select a static Google Sheet for your Zap. If you’re looking for dynamic Worksheet selection, make sure to reach out to the Support team to let them you’d like to be added to this feature request!” can be this my case? 

Attaching some screenshots showing the ID’s used. Have a great day. 

Userlevel 3

Thanks for the extra details @IKE!

I don’t see anything that is obviously off there. So at this point - I’d think the best course of action is to wait for us to reply to the support ticket so we can take a look at the Zap and log files specifically.

The warning in our help documentation is mostly because the only way around this at the moment is using a workaround like I’ve laid out above.

Based on what I can see - I’m confident this can work for you. 

Support is recommending the steps detailed in this article, ‘in some cases’ it can work… , not sure if they take a look to the Zap, I created a Google Script to copy the google spreadsheet, move it to a folder, delete the rows of the original sheet , except the first row with the headers, and a timed trigger every hour. So, my Zap that feed the spreadsheet have a fixed ID, and it is recycled every hour.  Will be awesome to have all the required functionality with Zapier.

If I can get this work, I will deactivate the Google Script part; but meet the deadline was a priority.

Thanks, have a great day.

Userlevel 3

Thanks for sticking with me here @IKE! So I think I might have figured out the issue.

Are you using a Create Spreadsheet Row(s) action with Line Items?

I went in and tested 3 different Actions.

  1. It works with Create Spreadsheet Row.
  2. It works with Update Spreadsheet Row.
  3. It doesn’t work with Create Spreadsheet Row(s)

And the errors I’m seeing with Create Spreadsheet Row(s) match the errors you referenced above. 

So at the moment - the workaround above does not work (directly) when using the Google Sheets Create Spreadsheet Row(s) action. (I’m updating the post above to better reflect this).

However - I’m always ready to try another workaround 🙂 - and I think we could still make this work with Create Spreadsheet Row(s) using a 2 Zap setup.

Basically - we would create a Trigger Sheet that would look something like this (building from my example above). We’ve added SheetID and WorksheetID as columns on our Trigger Sheet.

And we would have 2 Zaps.

Zap 1 would trigger and update the Trigger Sheet using Create Spreadsheet Row(s). You would select the Trigger Sheet directly from the Dropdown menu.

Because we are using Create Spreadsheet Row(s) here - it would create a row on the Trigger Sheet for each Line Item and also include the SheetID and WorkSheetID we want to update dynamically.

Then Zap 2 will trigger with the New Row on the Trigger Sheet.

And Create a New Row on the Dynamic Sheet using the steps in my initial post.

And the result - is my Agent A Sheet is updated with both new records.

So the key to this second workaround for Create Spreadsheet Row(s) is that we need 2 Zaps. Zap 1 is the Zap that finds the dynamic Sheet Values and writes the rows to our Trigger Sheet using the Create Spreadsheet Row(s) action.

Zap 2 is the Zap that triggers off the new row on the Trigger Sheet and actually creates the row on our dynamic sheet.

​​​​​​*Edited to add: I must have missed some little detail. I tried again and it appears to have worked. 

 

@Jared This isn’t working for me. For starters, I don’t have the same headings on the steps of my zap. For example, I have “set up action” rather than “customize spreadsheet row”. I believe I went through the steps as laid out here, but I still get an error when I try to finally test the zap. 

For further clarification and context, our business deals with enrolling students in courses. I want my zap to locate the correct spreadsheet for the course the student has enrolled in and create a new row in that spreadsheet. (aka I need to dynamically set google sheet and worksheet custom values, right?). 

Can someone advise on how to get my zap to successfully find and add a row to the *correct* spreadsheet for each client’s selected course? 

 

Thanks!

Userlevel 1

This solution only works if you know what the sheets are up front. So is there a way to dynamically create several worksheets and have them update automatically instead of having a trigger googlesheet where you have to keep manually putting in new sheet and worksheet guids. It just seems to be a headache if you want an automated solution.

Userlevel 3

Hi @jonnydee, I love that question! There might be a solution where a Zap could trigger off a new worksheet that is made, which could then write the worksheetId to another spreadsheet.

Then the Zap in question could do a lookup on the spreadsheet that has the worksheetIds to pull in the worksheet you’d like to retrieve dynamically. Hope that helps 🙂  

Reply