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.
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.
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.
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.
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).
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.
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.
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.
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!
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.
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.
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: