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.

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 🙂

Take Tyron’s Course on Demand

Explore the Demystify the API Crash Course

Introduction

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

The New Four Pillars of Marketing Operations

The New Four Pillars of Marketing Operations

Created with the assistance of DALL·E Introduction and Background In 2018, Edward Unthank introduced the four pillars of marketing operations, setting the stage for the management of effective marketing technology (Martech) functions. These pillars have since been the bedrock for many successful careers in marketing operations. My journey to redefine

Become a member
The Role of Front-End Martech Tools in the Age of Generative AI

The Role of Front-End Martech Tools in the Age of Generative AI

Introduction: Blending Human and Technical Perspectives in Martech Caption: Future gardens of martech will blend human and AI perspectives As a co-host of the Humans of Martech podcast, I’ve had the privilege of interviewing some of the brightest minds in marketing technology. This unique experience has provided a blend of

Become a member