Creating a lookup table with Google Sheets

  • 27 January 2021
  • 0 replies
  • 137 views
Creating a lookup table with Google Sheets
Userlevel 1

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

Intro

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:

07aa4f6a086f7321f7eb886ed870c636.png

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. 

eab527596c515bfa76e584c440195fb5.png

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. 

96ccf0096db246578817daa9f7332419.png

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

3e5a919887ad69e426ff58ee2d6afc37.png

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

b05b5d1c94bb61165b56045245a6b133.png

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

aa0fd41712dc31049f8e4329898132be.png

Success!

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!


0 replies

Be the first to reply!

Reply