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!