How to dynamically set Google Sheet and Worksheet custom values
Update
The Original Post below should no longer be needed - the Zap Editor can now handle these dynamic mappings.
For more details on the improved functionality see this post by Nick!
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.
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)
All 3 Sheets have the exact same headers (Name, Email and Phone)
We also have a Lookup Sheet where we have both the Sheet ID and Worksheet ID for each Agent’s Sheet.
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.
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.
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.
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.
Next we want to map the fields from the trigger step like we normally would (and click CONTINUE)
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.
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.
Finally - we can SKIP TEST and turn our Zap ON. (You can also try the test here and it should work)
The Result
Now when this Zap runs live - the Sheet and Worksheet IDs will be dynamically updated based on the Lookup in Step 4.
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. :)
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.
Choose an actual option (not a Custom Value) from the dropdown menu and map the fields as you normally would.
After the fields are mapped - change it back to use the Custom Value.
Ignore the Extra Fields Message.
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!
Page 1 / 1
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:
Google Sheets: The app returned "Requested entity was not found.".
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.
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.
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.
It works with Create Spreadsheet Row.
It works with Update Spreadsheet Row.
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!
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.
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
Thank you for this detailed article.
My problem is that I don’t want to update a row, I’d like to retrieve a value from a row!
I’m using:
Trigger: 1. Updated File in Google Drive
Action: 2. Lookup (in Google Sheets).
I hard coded the name of the worksheet as it is always the same (if I don’t it seems to look for an id that is super long).
No success.
Here is the image (before I decided to hard-code the sheet name).
Has anyone got any advice? Anything? Zapier or an alternative?
The alternative could be an importrange from gsheet to ghseet (bypassing zap) but I would very quickly reach my max amount of importranges allowed and go over my google workspace quota.
I understand this method will work to update and create a single spreadsheet ROW (which is great) will it work to lookup a spreadsheet row?
FYI, I incorporated the above in a Google Row Lookup, and it worked just fine. So it seems to work in creating a row, updating a row and looking up a row.
Hi @tourismtribe - sorry for my delayed reply here.
As @BruceF mentions this should work with Lookup Spreadsheet Row as well.
For example - I have one configured like this.
And when the Zap Runs - those fields are dynamically updated to find the Row.
When we make that request above to Google it looks something like this.
GET https://sheets.googleapis.com/v4/spreadsheets/1XVNbRKjVSoxbYEjcBpzce4rK2bfbo4Pqsu-pRh1QnOk/values/'Copy%20of%20Sheet1'!B:B"
The important parts to note here are these.
1 - We are sending the Code to identify the correct Sheet.
2 - My Email Column is in Column B on my Worksheet - and we can see B:B in the request URL. So the Lookup Column always has to be in the same column on all of the dynamic Worksheets. In my case that means the Email would always have to be in Column B. But the Column name could be different on the different Worksheets since we are just using the column letter (B) in the request.
3 - We are using the Name of the Worksheet in the request. In my case the Worksheet is named “Copy of Sheet1” - and we can see this towards the end of the request URL with some encoding (%20) for the spaces. This means that the Worksheet would have to be named exactly the same on all the dynamic Sheets.
Assuming those are true - the dynamic lookup should work.
Hello Zapier @Jared ,
I’m having big issue to dynamic update or create a new spreadsheet row.
It has been working well as I have followed some advice here,but now it’s not working properly.
All Google sheets with custom ID can’t map the original worksheet id.
Already contact to Zapier support and nothing to fix.
Please help!
Thanks in advance,
Jaran
Hi there, @Jaran K! Thanks for reaching out.
I can see you opened up a ticket a few hours ago - which is perfect! Our support team is truly the best and should be able to dig into this a bit more with you. Please continue to keep us posted on how this works out. We’d love to know!
THANK YOU! THANK YOU! THANK YOU! THANK YOU!
This post meant everything to me… It saved my whole project!
Thanks so much for letting us know @Accintive! So glad the Community could help
I don’t know what you guys did, but I used to be able to do make this work, but with this new update or whatever this is supposed to be, it doesn’t work anymore.
Also this is 0 user friendly, you’re basically “cheating” your own system, this needs to be fixed differently.
Hi!
I found a fix to the update spreadsheet row broken workaround:
Map all the fields to a specific spreadsheet like before.
When you switch out the spreadsheet to the id from a previous search step the validation for the worksheet and row will fail now.
The fix is to use custom value gid=53116461 ← this number from the end of your spreadsheet url as the worksheet name/id
Set the row number as custom value
It will work like before
Hi!
I found a fix to the update spreadsheet row broken workaround:
Map all the fields to a specific spreadsheet like before.
When you switch out the spreadsheet to the id from a previous search step the validation for the worksheet and row will fail now.
The fix is to use custom value gid=53116461 ← this number from the end of your spreadsheet url as the worksheet name/id
Set the row number as custom value
It will work like before
Jun 05, 2022
For those reading this later on, IT DOES WORK - BUT!
Here’s a few clarifications expanding on what OP means:
Fulfil the action of creating a spreadsheet row using a static sheet that has the same headers. Test it and review the data. Test and confirm this works before moving on.
Now it’s time to change from a static File ID to a custom File ID. Here select the “ID.” field from a previous search action result.
This is where I was getting confused.
What OP is talking about is the value/ID of the worksheet so the Zap knows which sheet to work with in the action you are creating. You may have seen it below the name of the worksheet back when this action properly worked.
To find this open the file itself in a new tab.
The “gid=XXXXXX” will be the last values in the url string.
Copy just the numerical values after ‘=’.
Return to the zap action where you want to manually enter the custom value for the worksheet.
Paste in the numerical values only.
I tried gid=XXXXX and it didn’t work.
I pasted just the value (in my case ‘0’) and it did work.
Limitations:
I’m unsure how to dynamically find this “gid=XXXX”, within the Zap, instead of manually opening the doc in a new tab.
I had to manually copy and paste said value into the worksheet field.
I’m unsure how one would handle the Zap accessing sheets that don’t always have the same static worksheet gid=XXXX value.
In my instance, the sheets I am creating rows on are all copied from a template sheet.
As far as I can tell, that’s resulted in my worksheets all having the same gid= value of ‘0’.
Yours may not.
Result:
It looks up a custom File ID (the entire Google Sheet workbook file ID) but uses a static value for the worksheet (See photo).
Like the other Google Sheets issue in this thread, it’s a workaround, and needs a proper fix.
I believe this now has been resolved (without the workaround). See also:
Hope that helps!
Hi everyone
Thank you so much for sharing the solution. Means a lot. Otherwise You can set up your named range to be based on a formula. This can be a powerful tool for spreadsheet users.
- and I think we could still make this work with Create Spreadsheet Row(s) using a 2 Zap setup.
