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

Zapier Import Multiple Rows Walkthrough Video

Google Sheets Example Database

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.

Creating a Google Script

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

1. Click on “Extensions” in the navigation bar of your Google Sheet
2. Select “Apps Script”
   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

Creating Variables

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="";

Looping Through Multiple Rows

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];
    }
  }
}

Send Webhook to Zapier

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.

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.

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.

What’s After Importing Multiple Rows from Google Sheets?

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