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.
As this post will demonstrate, the Google Sheets app in Zapier is an easy way to import 20 rows or less into your Zap. If, however, you need to import more than 20 rows 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.
Without further ado let’s dive into the different options for importing rows into Zapier using the Google Sheets app.
Lookup Spreadsheet Row
Once you have selected the Google Sheets app and the “Lookup Spreadsheet Row” action you will then need to navigate to the “Set up action” section where you will specify the “Spreadsheet” and the “Worksheet” within that you want to search.
Next, you will choose a “Lookup Column” along with a “Lookup Value” to search for within this column. As shown in the image below this lookup value can be variable obtained from a previous step in your zap so you can dynamically change the row that will be returned by this search.
Dynamically changing the lookup value by using a variable from a prior action
If desired you can specify a “Supporting Lookup Column” and “Supporting Lookup Value” so that the Zap will only return rows for searches where the lookup values are both found in the same row. Additionally, the “Bottom-Up” field can be used to tell Zapier to search from the bottom of the sheet, which can save time if you are continually adding data to this sheet and know that Zapier should start searching from these newly added rows.
Finally, you can specify whether this retrieval action should be considered a success when it does not find a row matching the lookup criteria. If “False” is selected then subsequent steps will be skipped, whereas, if “True” is selected then the Zap will continue so that you can use your own filters and paths to control the flow of the Zap.
Alternatively, you can select the “Create Google Sheets Spreadsheet Row if it doesn’t exist yet?” checkbox and then populate the columns from your sheet with the desired value. This way this lookup action will always be successful and your zap will always continue.
Creating a new row in Google Sheets if the lookup value(s) are not found
Now that you know how to use Zapier with the “Lookup Spreadsheet Row” action in Google Sheets you can take a look at the Zapier Google Sheets Lookup Value post to see how you can use this action to pull in more than 10MB of data in at once from Google Sheets.
Find Many Spreadsheet Rows
While the “Find Many Spreadsheet Rows” action can not pull in as many rows as the “Get Many Spreadsheet Rows”, it has the advantage of being able to pull in the data column by column, which makes it very easy to use in subsequent actions, especially whenlooping through line items.
The “Set up action” section has the same parameters as the “Lookup Spreadsheet Row” action so you can use the section above to see how to fill out these parameters to find your desired rows. In this case, though, this action will return every row, up to a maximum of 10 rows, that matches the lookup criteria. If you want an example of this then check out the “Get Line Items for Google Sheets” section of theZapier Loop Through Line Items Examplepost.
Google Sheets columns available for use in subsequent actions when using the “Find Many Spreadsheet Rows” method
Using the columns from the Google Sheets import in the “Loop Through Line Items” action
Get Many Spreadsheet Rows
Contrary to the 2 previous actions, which use lookup values to find desired rows, the “Get Many Spreadsheet Rows” action uses a range of columns to pull in a maximum of 20 rows. Since there are typically column headers the “First Row” field is set to 2. While using this range is convenient for pulling in a lot of rows at once it does not have the ability to selectively pull in rows like the “Lookup Spreadsheet Row” or “Find Many Spreadsheet Rows” actions can.
“Get Many Spreadsheet Rows” action setup
Additionally, the “Get Many Spreadsheet Rows” action pulls the Google Sheets information into string variables (see image below) which cannot be used as easily as the columns returned from the “Find Many Spreadsheet Rows” action. This means that these string values will need to be parsed in order to extract the data in the format you need it (see the “Getting Google Sheets Data into Columns Using Python” section below for an example).
Google sheets rows available as strings when using the “Get Many Spreadsheet Rows” method
Getting Google Sheets Data into Columns Using Python
If you want to transform the string variable returned from the “Get Many Spreadsheet Rows ” action into arrays for each of the columns in the sheet then you can use the Python code below to do so.
This Python code is based on the example Customer DatabaseGoogle Sheet containing 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.
Customer database Google sheet
After the maximum of 20 rows have been pulled in using the “Get Many Spreadsheet Rows” action then the “Raw Rows” output is used as the input to the Python code.
The “Raw Rows” output fromthe Google Sheets action being used as the input to the Python Code
Iterating Through Row Values
Once the Python code has transformed the data into column format, it is then very easy to use in subsequent actions such as the “Create Loop from Text” action in the “Looping by Zapier” app.
Columns obtained from the Python code being used in the “Create Loop From Text” action
What’s Next?
Now that you know how to use Zapier with Google Sheets to pull in the data you need you are ready to put this data to work!
Alternatively, if you want an overview of all the looping options available in Zapier (including nested looping!) then take a look at the Zapier For Each Loop Quick-Start Guide.
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