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 theZapier 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 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:
Click on “Tools” in the navigation bar of your Google Sheet
Select “Script editor”
A new tab will open with a “Code.gs” script already created and an empty “myFunction” function
Delete everything in the “Code.gs” script
Rename the “Code.gs” script by clicking on the three vertical dots that appear when mousing over the script name
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.
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/";
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.
Click on “Insert” > “Drawing”
Select the “Text Box” icon from the navigation bar
Create the text box to the size you want your button to be on the sheet
Put a descriptive label in the text box e.g. “Send to Zapier”
Format the label and text box as desired
Click “Save and Close”
Right click on the button
Click the three vertical dots that appear
Select “Assign Script”
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
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
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.
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 theZapier For Each Loop Quick-Start Guide).
As previously mentioned theZapier 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
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.
While AI and marketing may seem like science fiction, there are already many products and organizations using AI in different ways. As a marketing ops professional it’s critical you have a strong understanding of the applications of AI marketing so you can set your organization up for the future. What
“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
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