If you’re reading this, you have probably tried to load a large amount of data into a Zapier task using Google Sheets. Unfortunately, Google Sheets has a max of 20 rows that can be passed to Zapier at a time.

In this post, you will learn how to solve these use-cases:

1. Pass a large quantity of data into another system, instead of going row-by-row. (If that system will accept API calls)
2. Create a python script in a Zap that can be configured via a Google Sheet instead of manually going into Zapier and changing the code.
3. Create an automation that will be easy to update in the future.
4. Bypass some of the limitations of Python in Zapier tasks (such as the inability to use any module other than requests)

The core of it is this: you need to access a google sheet from python code within a Zapier task.

Set up your Zap

The trigger and actions don’t really change the example, so set up whatever workflow you need. For example: trigger zap when contact is added to CRM.

Next, add the action “Code by Zapier” into your Zap. Choose Python as the coding language.

You have an option to input data into your python code from the Zap. Zapier will create a dictionary for you called input_data. The key will be the names you choose on the left, and the value will be whatever you put in on the right. Of course, you can insert any data from a previous step in the Zap.

Protip: Python in Zapier can only use the requests module. If you need to manipulate dates, do it in a Formatter action earlier in the Zap—then pass the result in through the input_data variable.

Turn your google sheet into publicly-accessible json

While you can certainly interface with the google sheets API directly, I have yet to do it because it seems delightfully complicated to set up. This alternative works quite well for reading data.

Credit for this step goes to a medium article by Scott Lewis.

Step 1. Open the Google sheet you want to interface with.

Step 2. Publish it. File > Publish to the web. The default option to publish the entire document as a web page will work fine.

Step 3. Grab your spreadsheet ID from the URL bar.

It’s between the d/ and /edit.

Step 4. Find your worksheet ID. Go to this URL: https://spreadsheets.google.com/feeds/worksheets/[spreadsheetID]/public/basic?alt=json. Replace [spreadsheetID] with the ID you grabbed in Step 3.

The result will be a jumble of json. You can find the worksheet ID from here (usually the first sheet will be called od6.) It’s easier if you copy all of the json, and paste it in this online tool: https://jsonformatter.curiousconcept.com/. Click “Process”, and take a look at the formatted json below.

Under feed, then entry, there will be “id”. Here you will find a URL like this: https://spreadsheets.google.com/feeds/worksheets/1lOgo105Kk5nCVwWtOonB7bOrxXD-csZP9O0ZlGPKioA/public/basic/od6. The very last portion (od6) is your worksheet id.

If the worksheet you are pulling data from is not the first one, you can keep scrolling to find the proper id.

Step 5. Use this URL to access your spreadsheet:

https://spreadsheets.google.com/feeds/list/[spreadsheetID]/[worksheetID]/public/values?alt=json

Read the spreadsheet using python

You’re going to use the requests module in python to access the json, then turn it into a dictionary that will be easy to use.

import requests

worksheet = requests.get("https://spreadsheets.google.com/feeds/list/[spreadsheetID]/[worksheetID]/public/values?alt=json").json() # Replace the ids

The json that you get will have some extra information that’s not necessary for reading the google sheet. For my purposes, it was easier to create a new dictionary than to deal with the default one.

rownames = [] 

for item in worksheet['feed']['entry'][0]: 
  if 'gsx$' in item: 
    rownames.append(item) 

sheetcontents = [{}] 
number = 0 

for row in worksheet['feed']['entry']: 
  sheetcontents[number]['row'] = number + 1 
  for item in rownames: 
    sheetcontents[number][item[4:]] = row[item]['$t']
  sheetcontents.append({}) 
  number += 1

I’m sure I could have written it better, but I’m a self-taught coder. And this works. So.

This code strips some of the strange junk from Google’s json and makes it easier to reference each row/column that you need to.

It creates a variable called sheetcontents which is a list of dictionaries. Each item in the list represents a row, and the dictionary key is the column name.

If your data looks like this:

Then sheetcontents will look like this:

[
  {'row': 1, 'texttype': 'Text1', 'numbers': '1.00', 'dates': '1/20/2019'}, 
{'row': 2, 'texttype': 'Text 2', 'numbers': '2.00', 'dates': '1/30/2020'}
]

As you can see, the json removes spaces from column names (see: texttype). It also shows values as they are seen in Google sheets as strings, not as they are actually stored. (See: the dates).

At this point, you can do whatever you want with the data. In my case, I went through every row where column A matched a certain value to add tasks to a deal via the API in ActiveCampaign. I will likely write another article specifically on that topic.

You could also use this method to “remote control” your python script. Using if statements in your code, you can switch on/off certain parts of the script by changing the values in specified columns of your google sheet.

Or, you can make your script react differently based on external data such as customer information.

Things to keep in mind

• Zapier has a time limit on script run-times. Free users have a 1 second, 128mb of RAM limit. Paid users get 10 seconds and 256mb of RAM. While this usually wouldn’t be an issue, ActiveCampaign’s API has a rate limit of requests. Adding too many tasks took over 10 seconds as a result, so I had to use a workaround.
• The other thing to note is that your spreadsheet is now technically publicly accessible. Although I don’t know if it can be found via Google or any other method, I would not use this for customer information or internal company info that can’t be seen.
• This method does not allow you to write to the Google sheet, only read from it. You could use Zapier to write to a sheet, though.
• Zapier’s platform limitations apply. If you’re not using the script to interface with an API, you will likely not be able to add a bunch of rows of information with a Zapier action. Ex: you can’t add 20 rows of customers that you just pulled from python.
• If you want to read the output of your python script, you can create a dictionary called output_data. Zapier will read the contents of that variable.

Let me know in the comments what you end up building using this knowledge!