Zapier Google Sheets Lookup Value

When trying to pull a specific row from your Google Sheet into Zapier you can use a lookup value dynamically populated with a variable from a previous step. This is useful but the real power of using the Google Sheets lookup value in Zapier comes when you need to import large amounts of data into your zap.

As demonstrated in the Zapier Google Sheets Quick-Start Guide, the Google Sheets app in Zapier will only allow you to pull in a maximum of 20 rows. This means we have to get creative if we want to pull in more rows than this.

One way to do this is to use a Google Script to send a webhook to Zapier containing the data, as detailed in the Zapier Import Multiple Rows from Google Sheets post. This is an effective method for importing less than 10MB of data but if you need to import than 10MB that is where using a lookup value to pull in string fields containing concatenated row values is a gamechanger!

This post will use the Customer Database  Google Sheet as an example. This sheet contains the First NameOrder #Email Address, and Phone Number of each customer along with the Delivery Today? checkbox field to designate whether their order will be delivered today.

Customer database in Google Sheets containing the data to be brought into Zapier using a lookup value
Customer database Google sheet

Packaging Columns As Concatenated Strings

The first thing to do is to create a new tab in the Google Sheet e.g. Concatenated Columns, and then create a new JavaScript file where you will copy over the code from the concatenate_rows.js file in GitHub. If you have never created a Google Script before then take a look at the Creating a Google Script guide.

The first part of the Google Script involves getting a reference to the “Customer Database” sheet, extracting the row values from this sheet, and then declaring the variables to store the concatenated row values.

/*Zapier Google Sheets Lookup Value Part 1*/

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Customer Database");

var rows = sheet.getDataRange().getValues();

var names_string="";
var orders_string="";
var emails_string="";
var phones_string="";

Then the next step is to loop through each of the rows and transfer the value in each column to the corresponding variable defined above. This code snippet features a conditional statement that will only store the row values if the 5th column value i.e. “Delivery Today?” is TRUE. You can remove this IF statement if you simply want to import all rows in the sheet.

The IF-ELSE statement within the loop ensures that once the variables have been populated that the next time they are being populated an asterisk will be used to separate the value(s) already in the variable from the new value being appended.

/*Zapier Google Sheets Lookup Value Part 2*/

for (var i=1;i<rows.length;i++){

if(rows[i][4]==true){

if (names_string==""){
names_string = rows[i][0];
orders_string = rows[i][1];
emails_string = rows[i][2];
phones_string = rows[i][3];
}

else{
names_string = names_string+"*"+rows[i][0];
orders_string = orders_string+"*"+rows[i][1];
emails_string = emails_string+"*"+rows[i][2];
phones_string = phones_string+"*"+rows[i][3];
}
}
}

The final piece of code obtains a reference to the “Concatenated Columns” tab and then creates a variable for each of the 4 columns and the timestamp to store a reference to a corresponding cell in the first empty row available.

Later on, these reference variables are then used to update the cells’ values with the timestamp and the previously obtained names, orders, emails, and phones string values (from “Zapier Google Sheets Lookup Value Part 2”). Finally, the webhook is sent to Zapier to trigger the Zap to run and the timestamp value is passed so that this can be used as a lookup value in the Google Sheets app (see below).

Concatenated row values stored in Google Sheets along with the timestamp that will be used as the lookup value in Zapier
Concatenated row values stored in the “Concatenated Columns” sheet
/*Zapier Google Sheets Lookup Value Part 3*/

var submissions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Concatenated Columns");

var row = submissions.getLastRow() + 1;

var timestamp = submissions.getRange(row, 1);
var names = submissions.getRange(row, 2);
var orders = submissions.getRange(row, 3);
var emails = submissions.getRange(row, 4);
var phones = submissions.getRange(row, 5);

timestamp.setValue(new Date().toLocaleString());
names.setValue(names_string);
orders.setValue(orders_string);
emails.setValue(emails_string);
phones.setValue(phones_string);

var url = "https://hooks.zapier.com/hooks/catch/65051/ojrsspj/";
var options = {
"method": "post",
"headers": {},
"payload": {
"Timestamp": ts
}
};

var response = UrlFetchApp.fetch(url, options);

Triggering the Google Script using a Button

If you have never created a button in Google Sheets before and assigned a script to it then you can follow the instructions in the Triggering a Google Script using a Button guide to assign the new “concatenateRows” script to an aptly named button.

Button in Google Sheets to trigger the Google Script to store concatenated row values in the "Concatenated Columns" sheet
Button in Google Sheets to trigger the Google Script to store concatenated row values in the “Concatenated Columns” sheet

Using the Google Sheets Lookup Value in Zapier

Once the Zap has been triggered via the “Catch Hook” trigger event, then the “Lookup Spreadsheet Row” action from the Google Sheets app is used to lookup the timestamp value from the incoming webhook in the “Concatenated Columns” tab. Since we are always looking for the latest row the “Bottom-Up” setting should be set to TRUE so that the lookup value search always begins from the bottom of the sheet.

Timestamp being used as the Google Sheets lookup value in Zapier
Timestamp being used as the Google Sheets lookup value in Zapier

As opposed to using a webhook to transfer the data to Google Sheets, as demonstrated in the Zapier Import Multiple Rows from Google Sheets post, here the webhook merely tells Zapier that the data is ready for collection. Then Zapier will use the Google Sheets app to retrieve the concatenated row values using the timestamp that the webhook was sent as the lookup value with the net result being that now more than 10MB of data can be imported into Zapier.

What’s Next?

Since you’ve made it to the end of this post I am going to go out on a limb and say you might be importing a MB or two into Zapier from Google Sheets every so often! If this is the case and you want to learn about the best ways to import data from Google Sheets depending on the number of rows or the amount of data you need to import then take a look at the Zapier Google Sheets Quick-Start Guide.

If you are an eager beaver and want to get started using all this data you just imported then you can take a look at the Zapier loop through array from Google sheets post to see how you can iterate through rows of data to perform certain actions. Also if you want to know how to use nested loops in Zapier then take a look at the Zapier For Each Loop Quick-Start Guide.

About The Author — Tyron Pretorius
Tyron Pretorius

Greetings fellow MO Pros! As a previous mechanical engineer turned marketing operations lead, I now fancy myself as a full-stack marketer capable of designing websites, writing SEO-optimized content, and doing all the tracking and automation on the backend. That being said my bread and butter is marketing operations and I love to get down and dirty with the Marketo API, Python, Zapier, and Google Scripts.

Related free workshops

Conquer the four hardest parts of marketing planning 

“So, what are our plans for next year?”  This probably sounds like a familiar refrain right about now. The weather turns cooler, the pumpkins proliferate, and marketing organizations start to line up their priorities for next year.  But the reality on the ground is that marketing is often an exercise

Become a member

It’s time to clear the fog of marketing 

Have you been pouring dollars and process improvements into your marketing stack for years, but find that you still can’t tell if your plans are succeeding?  Aligning planning—where resources, money, time, and people are allocated to achieve goals—with outcomes themselves often proves elusive for marketing departments.  Spreadsheets and slides provide

Become a member