Creating a lookup table with Google Sheets

  • 27 January 2021
  • 2 replies
Creating a lookup table with Google Sheets
Userlevel 7
Badge +11
  • Community Manager
  • 5458 replies

Hello there, Sam from Zapier Support here with a handy workflow idea for you!


In support we sometimes come across apps that require values to be supplied in a certain format that the trigger app doesn’t provide. And when they don’t receive values in the correct format it can cause unwanted errors.

In the following example we’ve got client details added via Google Form that need to be added into Wave. The problem is that the country part of their address is specified in full and Wave doesn’t read countries like that; it’s expecting to see a country code instead:


You could use a Formatter (Text > Lookup Table) step to convert the country name into the relevant country code. But you can’t make that same lookup table instantly available for other Zaps to use. It’s possible to copy the Zap and tweak the other steps but what if 3 weeks later you spot a typo in the lookup table?

Yep, that’s right. You’d have to go into each of the Zap copies and fix the typo in each one. And life is way too short to be wasted duplicating changes across multiple Zaps!

The solution 

A single lookup table built in a third-party app like Google Sheets. Using this Google Sheets enables the lookup table to be easily referenced and updated across multiple zaps in an instant!

Setting up the Google Sheet spreadsheet is fairly straightforward. You just need a couple of columns filled out. One with the values you receive from the trigger app and another with the values you need to convert them to for your action step.

In this example we can see there’s a column for the country names and another for the desired Alpha-2 ISO country codes. 


Short on time? No worries, you can just make a copy of that lookup table spreadsheet here. I’ve even included a bonus extra column containing three-letter ISO country codes as well in case you need them. You’re welcome! 1f609.png

Once that lookup table spreadsheet is set up, we’ll want to add a step to the zap to search it. Google Sheets has a “Lookup Spreadsheet Row” step that we can use for that so we’ll add one in just after the trigger step. Next, we’ll select the spreadsheet name and worksheet. Then in the lookup value field we’ll select the country name from the Google Forms trigger step. 


If this works correctly we should be able to search for "United States of America" and find a row containing the code “US":


Fantastic! Now we just have to head over to the Wave step select the country code that the "Lookup Spreadsheet Row” Step found:


OK, great. Let’s see what Wave has to say about it now:



So there you have it, one master lookup table that you can use across multiple Zaps. All you need to do is add a “Lookup Spreadsheet Row” step to any Zap to access it! And to help save a bit more time you can make a copy of the Zap in this example here.

What I love about using lookup tables is that they aren’t just limited to converting country names into country codes. They can be used to help Zaps automatically convert all sorts of useful information. Such as Tags names into Tag IDs, Mailing list names into Mailing List IDs or even product names into SKUs!

I hope you find this useful. I’m happy to answer any questions you might have about this in the comments below.

Happy Zapping!

2 replies

Userlevel 1

Hey, thanks for sharing this. I have a similar case but slight different. 

I’m trying to look up rows from a google sheet > create a new sheet > email it over as an attachment using gmail. It would be best to just email over the rows that have been looked up as a table, but unfortunately it returns as a string.

Therefore, I think the best way is to create a google sheet instead and attach it to an email before sending off. Currently I’m stuck on creating a new sheet with the looked up values (not sure how to go about it). Below is my current flow where i’ve yet to create a new sheet. 


Ideal flow:

  1. User fills in a form and it gets recorded on a new sheet (done)
  2. Zapier to look up a column in a master database against one of the answers from the google form (done)
  3. Create a new sheet with the looked up rows (not done, need help)
  4. Attach the new sheet in email and send off to the form responder (not done, but I know how)

Hope someone is able to advise. Thank you!

I am trying to use this method, but need to look up more than one item (line items on an invoice).  This method works with the utility table but it didn’t work properly with the google spreadsheet.  Is there a way to do this using a spreadsheet table?