Zapier Import Multiple Rows from Google Sheets

Anyone who has worked with Google Sheets in Zapier is likely to have run into the issue of needing to import more than the 20-row maximum imposed when using the Google Sheets app. This post will show you how to overcome this limitation and import multiple rows from Google Sheets into Zapier using some JavaScript and webhooks.

The method described below of using JavaScript and Webhooks to send multiple rows from Google Sheets to Zapier is effective when importing 10MB of data or less since, according to Zapier, the maximum webhook size you can receive in your Zap is 10MB. If you exceed this limit you will receive a 413 status code error.

If you need to import more than 10MB of data or you only need to import less than 20-rows at a time then take a look at the Zapier Google Sheets Quick-Start Guide to see the different options available based on how many rows or how much data you need to import.

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 multiple rows to be imported into Zapier
Customer database Google sheet

Creating a Google Script

If it is your first time using Google Scripts then follow the steps below to get started:

  1. Click on “Tools” in the navigation bar of your Google Sheet
  2. Select “Script editor”
    1. A new tab will open with a “Code.gs” script already created and an empty “myFunction” function
  3. Delete everything in the “Code.gs” script
  4. Rename the “Code.gs” script by clicking on the three vertical dots that appear when mousing over the script name
  5. Copy the send_rows.js” JavaScript function from Github
  6. Paste this code into your renamed “.gs” script
  7. Click the “Save” icon

Send Multiple Rows from Google Sheets Via Webhook

The first part of the Google Script involves getting a reference to the “Customer Database” sheet, extracting the multiple row values from this sheet, and then declaring the variables that we eventually want to send using the webhook.

/*Zapier Google Sheets Multiple Rows 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 for the first time 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 Multiple Rows 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 part of the Google Script gets a timestamp value and then sends this timestamp along with the variables in the payload of a webhook to the URL of the Zapier “Catch Hook” trigger (see the “Catch Hook to Receive Multiple Rows in Zapier” section below to find this URL).

/*Zapier Google Sheets Multiple Rows Part 3*/

var ts = new Date().toLocaleString()

var url = "https://hooks.zapier.com/hooks/catch/65051/ojrsspj/";

var options = {
"method": "post",
"headers": {},
"payload": {
"Timestamp": ts,
"Names": names_string,
"Orders": orders_string,
"Emails":emails_string,
"Phones":phones_string
}
};

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

Triggering a Google Script using a Button

Now that you have your Google Script ready, we are going to create a snazzy button to trigger this script to run and send the multiple rows of information to Zapier.

  1. Click on “Insert” > “Drawing”
  2. Select the “Text Box” icon from the navigation bar
  3. Create the text box to the size you want your button to be on the sheet
  4. Put a descriptive label in the text box e.g. “Send to Zapier”
  5. Format the label and text box as desired
  6. Click “Save and Close”
  7. Right click on the button
  8. Click the three vertical dots that appear
  9. Select “Assign Script”
  10. Type in the name of the function in your Google Script e.g. “sendRows”, which is the default if you didn’t change the code from Github
  11. Click “Ok”

Now, whenever you click that shiny button it will send a webhook to Zapier containing the multiple rows that you want to import to Google Sheets.

Button in Google Sheets to trigger a Google Script to send multiple rows to Zapier
Button in Google Sheets to trigger a Google Script to send concatenated row values to Zapier

Catch Hook to Receive Multiple Rows in Zapier

Once you have selected the “Catch Hook” event from the “Webhooks by Zapier” app, navigate to the “Set up trigger” section and copy the “Custom Webhook URL”. This is the URL that will be used in the Javascript shown in the “Google Script to Send Multiple Rows Via Webhook” section.

Custom Webhook URL used as the destination in the Google script
Custom Webhook URL used as the destination in the Google script

Then you can test the trigger by clicking the “Send to Zapier” button and then checking the output in the “Test Trigger” section. Looking at the image below we can see that each column from the Google sheet was successfully populated with values wherever the “Delivery Today?” value is TRUE.

Output from the "Catch Hook" trigger containing the multiple rows of data imported to Zapier from Google Sheets
Output from the “Catch Hook” trigger containing the data imported from Google Sheets

What’s Next?

These text variables representing the column values can then be used in subsequent actions such as the “Create Loop From Text” action as shown in the Zapier loop through array from Google sheets post.

Bear in mind that the looping by Zapier action is limited to 500 iterations so if you have brought in more than 500 rows of data then you will need to used nested loops in Zapier to iterate through all these rows (see the Zapier For Each Loop Quick-Start Guide).

As previously mentioned the Zapier Google Sheets Quick-Start Guide, will walk you through the best option to import multiple rows from Google Sheets into Zapier depending on how many rows or how much data you want to import.

Woah, 2 future posts already earmarked so I’ve got my work cut out for me and you have a lot to look forward to! If you want to receive an email when these upcoming posts launch then be sure to subscribe!

About The Author — Tyron Pretorius
Tyron Pretorius

Greetings fellow MOPs 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

Google Sheets data being accessed in a Zap action in Zapier

How to use Zapier with Google Sheets

Integrating Google Sheets with Zapier will allow you to unlock a whole new array of automation possibilities for the data in your spreadsheets. This post will show you how to use Zapier with Google Sheets by walking through the 3 different options for importing data into Zapier using the Google Sheets app.

Become a member
Timestamp being used as the Google Sheets lookup value in Zapier

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.

Become a member

MarketingOps.com Launches Career and Community Platform for Marketing Operations Professionals

Community-led platform offers networking opportunities, career development tools, and educational resources for more than 3,000 “MO Pros” IRVINE—MarketingOps.com, a new platform for marketing operations professionals, launched today. The website aims to serve the 12 million global professionals in the industry—starting with the 2,800 members of the existing MO Pros community:

Become a member