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.

On a related note are you interested in learning more about APIs? APIs are the foundational building blocks that power modern software ecosystems like Zapier and the Demystify the API Crash Course will explain the fundamentals and give you a hands-on introduction to making your first API requests. Check out the course using the button below 🙂

Introduction

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 Name, Order #, Email Address, and Phone Number of each customer along with the Delivery Today? checkbox field to designate whether their order will be delivered today.

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).

/*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.

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.

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.