How to use Zapier with Google Sheets

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.

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 🙂

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 when looping 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 the Zapier Loop Through Line Items Example post.

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.

import re

string = input['string']

names = re.findall('\["(.*?)",\s".*?",\s".*?",\s".*?",\s".*?"\]', string)
orders = re.findall('\[".*?",\s"(.*?)",\s".*?",\s".*?",\s".*?"\]', string)
emails = re.findall('\[".*?",\s".*?",\s"(.*?)",\s".*?",\s".*?"\]', string)
phones = re.findall('\[".*?",\s".*?",\s".*?",\s"(.*?)",\s".*?"\]', string)
deliveries = re.findall('\[".*?",\s".*?",\s".*?",\s".*?",\s"(.*?)"\]', string)

return {'names': names, 'orders': orders, 'emails':emails, 'phones':phones, 'deliveries':deliveries}

This Python code is based on the example Customer Database Google 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.

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!

If you want an in-depth walk-through of how to use the “Looping by Zapier” action, mentioned previously, to iterate through your Google Sheets data then you can check out the Zapier Loop Through Line Items Example and the Zapier loop through array from Google sheets posts.

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.