If you're using Google Spreadsheets for the Trigger side of a Zap and it suddenly stopped working, verify that you didn't accidentally insert a blank row anywhere. Zapier interprets a blank row as the end of the spreadsheet.
In addition to this, the following changes to your spreadsheet whilst your Zap is turned on can cause a disconnect with your Zap:
- Deleting existing rows or columns.
- Adding rows anywhere other than to the bottom of your sheet.
- Resorting the sheet.
- Renaming/adding/rearranging new columns.
- Renaming the sheet.
- Filtering the sheet.
If you need to make any of those types of changes to your Google Sheet, you will need to turn your Zap off while you make the change, and then turn it back on again after.
Errors that mention range (cannot parse range, requested writing within range) might be because of a colon in the title of the spreadsheet/worksheet
Are you getting an error that says something like
cannot parse range or something like
400 Error: Requested writing within range ['DO NOT EDIT: Feed via Zapier'!A74], but tried writing to column [B]?
- Do you have a colon (:) in your worksheet or spreadsheet title? If so, try removing it - colons in spreadsheet and worksheet titles can sometimes cause a Zap to malfunction.
- Do you have fields mapped in your Zap? This error sometimes occurs when the Zap attempts to add a completely blank row.
Do you have a colon (:) in your worksheet or spreadsheet title? If so, try removing it - colons in spreadsheet and worksheet titles can sometimes cause a Zap to malfunction.
- Has the spreadsheet/worksheet name changed? Make sure you turn the Zap off while you make the change, and then turn it back on again after.
The new and updated spreadsheet row triggers for Google Sheets are unique in that when there is a trigger event in the spreadsheet, Zapier gets a notification webhook from the Google about this following that notification, Zapier sends Google Sheet a request asking for new data. After this, the trigger works using the normal polling mechanism and these new or updated rows returned will trigger the Zap. This process takes about 3 minutes overall so although the Google Sheets trigger is marked "instant" it really is a hybrid of both webhook and polling techniques. While being slower than any other "instant" trigger, it's still faster than all polling triggers which would take 5 or 15 minutes.
We highly recommend not renaming columns in your spreadsheet after you set it up to work with Zapier once. If you do this, the Zap may no longer be able to locate your columns in order to insert data.
A common symptom of this ailment is when fields suddenly go missing or become empty in Zapier. To resolve this issue, please turn your Zap off from your Dashboard and remap the field into the Action in your Zap. Then, turn the Zap back on to get back on track.
If you’re using the “Updated Row” trigger and choose “Any column” to monitor, any change to a row including adding a new row will trigger your Zap. The best time to use this trigger is when you want to look for changes on a specific column in a spreadsheet. For example, triggering when an email address is added so we can add the contact to a mailing list.
Please note that if you create an empty row in the trigger column, it will still trigger your Zap - this is because that new row is seen by Zapier as an 'update'. You'll want to add a Filter to your Zap if you don't want it to run when the trigger column is empty.
If you are using the “New Row” trigger and manually type new data into each new row in your spreadsheet, the Zap may trigger in the middle of entering that data before you get the chance to finish, causing incomplete data to be sent to the following step in your Zap.
It is best to use the “New Row” trigger when data is being entered into all columns of a row almost simultaneously. This way, we will be able to capture all data entered.
If you need to manually type rows in your spreadsheet OR if you’re needing to collect data over time and then trigger your Zap once all data has been entered, you may be best suited using the “Updated Row” trigger.
The best thing to do here is to add a new column to your spreadsheet called something like “Send to Zapier”.
When setting up your Zap, you will then want to choose the “Sent to Zapier” column to monitor. Before testing, go ahead and place an “X” or anything you’d like in that column to trigger the Zap. The test should bring back the data from the row where the “X” was placed.
If you're watching for changes to a column, we only see new values in that column. If you update the column of an existing row to a value that Zapier previously saw in that column in that row, we won't see the new value. You will need to either choose a column that will only have unique values, or make a new one which will.
For example, if you had X in the Trigger column, then changed it to Y, the Zap would trigger. If you then changed it back to X it would not trigger again, because we've already seen X in that column.
We do our best to take things that look like dates or times and convert them to a format that Google Spreadsheet will understand. The currently supported formats are:
- Fri, 14 Jun 2013 14:38:13 +0000 (or -0000)
- Fri, 14 Jun 2013 14:38:13 GMT
Note the final item in the list. To use this, you'll need to add a ts to the front of unix timestamps to ensure that they are recognized by us properly. This prevents big numbers from being improperly interpreted as dates or times.
This error can also look like: ExternalHTTPSConnectionPool(host='sheets.googleapis.com', port=443): Read timed out.
The larger your spreadsheet the more likely you are to see this error. Google Spreadsheets have not been optimized for large volume spreadsheets which makes accessing them via the API a challenge. Here are some things to keep in mind:
- The bigger the spreadsheet, the more likely these errors are to crop up. Consider starting a new spreadsheet with less data in it, or moving/deleting any columns your Zap isn't using. If you choose to move/delete rows, make sure the Zap is off while you do this. Once you're done, you can turn it back on.
- If this error pops up with Google Spreadsheets as the Trigger, there is little concern about data loss since the likelihood that Zapier catches data the next time around is quite high. That said, over time these errors will grow more frequent, potentially causing your Zap to be turned off for a high error ratio, and you should reference item 1.
- If this error pops up with Google Spreadsheets as the Action, then there will likely be data loss on the action side. To recover from this, try replaying that specific task from your task history.
In general, Google Spreadsheets API isn't built for the amount of data you're likely trying to push through. If you're considering an alternate solution, an app like Airtable, or a database, like MySQL, is a popular next step for many people with this amount of data. If you aren't sure what to do, just send us a message with what you are trying to do and we can help come up with a solution.
If you see this error, it means that your Google Sheets connection somehow failed. To fix this, go to your connected accounts page in Zapier and click reconnect on the failing Google Sheets connection.
Your Google Spreadsheet can be configured so numbers, dates, times, and different currencies can be recognized differently when entered into a column. If your data isn't coming across how you hoped it would, you might want to try changing the format, which you can learn more about here.
If your date is showing up as a five-digit number, or five digits, then a decimal, then five digits, it's due to the date formatting in Google Sheets (or Excel, or Numbers, etc.). It may look like this:
To avoid this, format your column to another date format.
If you see this error, then your Zap tried to create a completely blank row in the spreadsheet. Some common reasons (and solutions) for this:
- No trigger fields matched to the columns on the spreadsheet. Add trigger fields or other data into the Action Fields.
- Testing the Zap, but using a sample trigger that was blank. Some apps provide a default sample for field matching, but the data is blank. Add a new item (for example, fill out a new form entry with all the fields filled in) and use that for testing, instead of the blank default entry.
If you see this error in a zap, try re-selecting your 'Worksheet' and 'Spreadsheet' in your zap. If that does not help, be sure to let us know.
If you see this error in a zap, check to see if your spreadsheet is locked or has "protection" on. This prevents Zapier from being able to write to the sheet, so you'll need to unlock it.
Unfortunately, the Update Spreadsheet Row action won't clear out fields. If you have text in a cell and you want to replace it, you'll need to send something along, like a dash or a formula that will result in a blank cell, in order to update it. If a field is blank, we don't send anything.
An example of formula that you can send along is: =iferror("",""). This will overwrite whatever is in the cell but result in the cell looking like it's blank.
Alternately, you can use the "Delete Spreadsheet Row" action to clear all data out of a row.
You should use it only when your trigger provides line items, and note that:
- If one of the line items is missing a value that others have, we create a row with a blank value in that cell
- If you map a non-line item field from the trigger, we duplicate it for every row added
If your trigger doesn't provide line items, don't use this action, use Create Spreadsheet Row instead.
Let's us an example let's say you have a Zap that triggers off of a form like, looks up a package (which contains several products) in a Google Sheet, and sends an invoice for those multiple products that would work fine for up to 10 products in a package per invoice or form.
Whenever someone submits an order form with the package "Blue" selected and you have a sheet with like this:
And could use that to create an invoice like this:
This error usually means there's a problem with the Worksheet.
In almost all cases, re-selecting the Worksheet fixes this issue.
Some common reasons why you might see this error:
- A different Spreadsheet was chosen without choosing a new Worksheet.
- The Worksheet was deleted or moved.
- The Worksheet is set to a custom value. Unlike most other fields, it's unfortunately not possible to set a Worksheet field to a custom value.
If you find that the columns look like this in your action:
This is likely because you have a blank first row in your sheet. You'll want to delete the blank row, then reselect the Spreadsheet and Worksheet in your action, and the column names should refresh to match your spreadsheet. You can find more info on how to correctly format your sheet here.
If you use the Create Spreadsheet row action, rows will be added to the first blank line that Zapier sees. If a row has a formula in it, it's not considered blank and will be skipped. If the formula is referencing all data from within the row, you can build the formula directly into the Zap. For example, your spreadsheet looks like this:
Instead of having the pre-filled formula in the sheet and mapping my fields like this:
You can instead include the formula in the Zap like this:
You can use any formulas that are available in Google Sheets in your Zap, provided all the variables in the formula are going to be available in the Zap.
If, however, all the variables in the formula aren't going to be available in the Zap, then this won't work. For example, lets say you have a sheet that looks like this where the Zap will populate the "Number" and "Other Number" columns but there is a manual step to populate the third column. The "Total" column needs to add up all three:
In that case, you can't reference
C2 in the Zap because that data will be manually added later to the sheet. In this case, you should create a new worksheet on the same spreadsheet and use the
=IMPORTRANGE formula. This will result in one sheet that is connected to the Zap and another sheet that imports data from the original sheet. This second sheet can have formulas, it can be sorted, filtered, etc and not mess with the Zap.
So your sheet connected to the Zap will look like this. For the purposes of this explanation, let's refer to this as "Zap Sheet":
Create another sheet on that same spreadsheet will look like this. We'll refer to this as "Imported Sheet":
Notice that cell A1 on the Imported Sheet has the following formula
=IMPORTRANGE("1B3-BTahH9tZU37VgT1WDtpdcpjyrAM_RdUu3UmfCAYY","Sheet4!A:B"). This pulls in columns A through B from the Zap Sheet. Then you can add the additional columns you want and pre-fill any formulas:
When the Zap runs, it'll populate the Zap Sheet. The Imported Sheet should update based on the values in the Zap Sheet, but your other formulas will work as needed.
The most common reasons rows are added to the top of a Google Sheet instead of the bottom of the sheet are:
- Column A is missing a header
- Column A is being hidden
Un-hiding Column A and making sure it has a header usually resolves this problem.
Sometimes troubleshooting Zaps involving Google Sheets required Zapier support to have access to the Google Sheet. While your Zap is being investigated this requires sharing the Google Sheet with our team. The best way is to set the Google Sheet available to anyone with the link so that anyone on our team can help. Here's how to do this:
- Open a file in Google Docs, Sheets, or Slides.
- In the top right corner, click Share.
- Click "Get shareable link" in the top right of the "Share with others" box.
- To choose whether a person can view, comment, or edit the file, click the Down arrow next to "Anyone with the link" . Usually, we'll ask for a link with read-only access. Please choose "view"
- Finally, press the "Copy Link" button, and a file link will be copied to your clipboard. Paste the link in an email or anywhere you want to share it.
This can happen when you have a large spreadsheet and Autoreplay is turned on. Consider starting a new spreadsheet with less data in it, or moving/deleting any columns your Zap isn't using. If you choose to move/delete rows, make sure the Zap is off while you do this. Once you're done, you can turn it back on.
Zaps use a notification system in Google Drive, to identify when there are changes in a Google Sheet. This currently works well for Sheets in your own Google Drive, but there's an issue for any Sheet that resides within a Team Drive.
In order to help Google prioritize that issue, please visit this link, and click on the "star" at the top, to indicate that you would like the to fix the problem: https://issuetracker.google.com/issues/130736018
After that, you can get things working using the following process:
In the Zap editor, go back to the part where you selected the trigger:
Click on "show less common options":
Select the "Team Drive" version of the trigger that you would like to use:
Continue with the trigger setup, and you should be able to select the Team Drive.
Please contact our support team if you have any questions, or need help getting that working.
Sometimes you want your "Create" or "Update Spreadsheet Row" action steps to create/update rows on different sheets or worksheets based on information from a previous step.
Currently, though, it’s not possible to dynamically choose a spreadsheet in Google Sheets the same way you can dynamically choose options for other custom-value type dropdowns. This is because there isn’t a way for us to generate a good action template unless we know specifically which spreadsheet/worksheet combination will be used each time the Zap runs. As such, selecting a custom value for a Google Spreadsheet or Worksheet will result in an error code, where the spreadsheet cannot be found.
The only surefire way to update Google Sheet data is to select a static Google Sheet for your Zap. If you’re looking for dynamic Worksheet selection, make sure to reach out to the Support team to let them you’d like to be added to this feature request!