If your Google Sheets trigger stops working suddenly, first check if you’ve accidentally inserted a blank row anywhere in the spreadsheet. Zapier interprets a blank row as the end of the spreadsheet and may have trouble finding any new rows below it.
In addition to this, making the following changes to your spreadsheet while the Zap is turned on can cause errors:
- Deleting existing rows or columns.
- Adding rows anywhere other than to the bottom of your sheet.
- Resorting the sheet.
- Renaming/adding/rearranging columns.
- Renaming the sheet.
- Filtering the sheet.
If you need to make any of those changes to your Google Sheet, turn your Zap off while you work on the spreadsheet, and then turn it back on again.
If you are getting an error that says something like cannot parse range or 400 Error: Requested writing within range ['DO NOT EDIT: Feed via Zapier'!A74], but tried writing to column [B], there are a couple of possible causes:
- There is 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.
- There are no fields mapped in the Zap. This error sometimes occurs when the Zap attempts to add a completely blank row.
- The spreadsheet/worksheet name has changed. Make sure you turn the Zap off while you make the change, and then turn it back on again after.
The triggers for Google Sheets are unique among Zapier triggers. When there is a trigger event in the spreadsheet, Zapier gets a notification webhook from Google about this. After that, Zapier sends Google Sheets a request for new data, so it uses both the polling and instant trigger methods. This process takes about 3 minutes overall.
While not being "instant", these triggers are faster than regular polling ones, as they don't depend on the polling interval of the plan your account uses.
This might happen if columns in your spreadsheet were renamed after you set it up to work with Zapier. When this happens the Zap may no longer be able to locate your columns.
To resolve this, turn your Zap off 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 New or Updated Spreadsheet Row trigger and choose Any column to monitor, any change to a row will trigger your Zap. If you select a specific column, then the Zap will only trigger when there are changes on that column.
Zaps using New or Updated Spreadsheet Row will also trigger for any new rows in the spreadsheet, even if the specified column is blank. To avoid triggering in those cases, add a Filter step to your Zap.
When using the New Spreadsheet Row trigger the Zap may trigger in the middle of entering data for a new row, causing incomplete data to be sent to other steps in your Zap. This can happen if Google Sheets autosaves while you are still filling out a row.
It's best to use the New Spreadsheet Row trigger when data is being entered into all columns of a row at the same moment.
If you need to manually type information into certain columns in your spreadsheet OR if you’re needing to collect data for the same row over time and only trigger your Zap when all data has been entered, try using the New or Updated Spreadsheet Row. With this trigger, you can have it monitor a specific column of your spreadsheet that tells it data is ready to go (something like “Send to Zapier”).
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 the Zap has already seen X in that column.
We do our best to take information that looks like dates or times and convert them to a format that Google Sheets will understand. Currently, Zapier supports the following formats:
- 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): The read operation timed out. This usually happens because your spreadsheet is too big.
Google spreadsheets are not optimized for large volumes of information (spreadsheets with around 10,000+ rows), and this can make it a challenge to access them via Zapier. Here are some things to keep in mind:
- Consider starting a new spreadsheet with less data in it, or moving/deleting any columns not being used on the Zap. If you choose to move/delete rows, make sure the Zap is off while you do this. Once you're done, you can turn your Zap back on.
- If this error pops up with Google Sheets as the trigger, there is little concern about data loss since the Zap will probably catch the data the next time it runs. That said, over time these errors can become more frequent and potentially cause your Zap to be turned off, as there are too many errors.
- If this error appears with Google Sheets as the action, then there will likely be data loss. The Zap might not be able to complete sending data to Google Sheets due to the size of the spreadsheet. To recover from this, try replaying that specific task from your task history.
If you see this error, it means that the connection from Google Sheets to Zapier failed. To fix this:
- Go to the Google Sheets connections page.
- Click the specific connection.
- Click Reconnect.
Google Sheets can be configured so numbers, dates, times, and different currencies are automatically formatted when entered into a column. If your data isn't coming across from the Zap as you expected, try changing the format in Google Sheets.
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. It may look like this:
To avoid this, format your column using a different 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:
- There is no information mapped to the Google Sheets action. Add trigger fields or other data into the Action Fields.
- The sample used to test the Zap is blank. Some apps provide a default sample for each field, but others may present the fields as blank. To fix this, add a new item (for example, submit 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, try re-selecting the Worksheet and Spreadsheet fields in your Zap step. If that does not help, be sure to let us know.
If you see this error in a Zap, check if your worksheet is locked or protected. This prevents Zapier from being able to write to the sheet, so you'll need to unlock it.
The Update Spreadsheet Row action can't be used to clear out fields. If you have text in a cell and you want to update it, you'll need to send some character along, like a dash or a formula that will result in a blank cell. When a field is blank, Zapier doesn't send anything.
You can also use the Delete Spreadsheet Row action to clear all data out of a row.
You should use it whenever your trigger provides line items, so that the Zap can create multiple rows in your spreadsheet. It's important to 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 to this action, but other fields have line items, it will copy the single value for every row added.
If your trigger doesn't provide line items you can use Create Spreadsheet Row action instead.
This action is useful when the previous action provides a line item group and you need to find some information associated with each of those items.
Let's say you have a Zap that triggers off orders, but the trigger only offers the product names, and you need the product ids for your inventory app, in another action step, as it cannot match the products using their names. You can use the Find Spreadsheet Row(s) (with line item support) action to search for up to 10 product ids at the same time, and pass those to the next step.
This error usually means there's a problem with the worksheet. In almost all cases, re-selecting the worksheet in the Zap step fixes this issue.
Some common reasons why you might see this error:
- A different spreadsheet was selected without reselecting the worksheet.
- The worksheet was deleted, moved or its name changed.
- The worksheet is set to a custom value. At this moment, it's not possible to use custom values in the Worksheet field.
If you find that the columns look like ids instead of having the names that appear in the spreadsheet, this may mean that there is a blank first row in your worksheet. To fix this:
- In Google Sheets, delete the blank row.
- In your Zap step, reselect the Spreadsheet and Worksheet fields, and the column names should refresh to match your spreadsheet.
Find out how to correctly format your sheet to work with Zapier.
The Zap may seem to skip blank rows if they have formulas in them. If you use the Create Spreadsheet Row action, rows will be added to the first blank line that the Zap sees. If a row has a formula in it, the Zap doesn't consider it as blank and will skip it. If the formula is referencing data from within the same row you can build the formula directly into the Zap.
You can use any formulas available in Google Sheets in your Zap, if the variables in the formula can be mapped from other Zap steps.
This solution won't work if not all variables in the formula are available in the Zap, as it wouldn't be possible to reference specific cells dynamically. For example, let's say you have a sheet like the one in the image below. The Zap will populate the Number and Other Number columns, but column C will be manually updated later.
In that case, you can't reference
C2 in the Zap, as this would mean that every time the Zap runs, it would make a reference to C2, instead of using the appropriate row number. In this case, you should create a new worksheet on the same spreadsheet and use the
=IMPORTRANGE formula. This way you'll have a sheet connected to the Zap and another sheet that imports data from the original sheet, and allows you to use formulas, sort and filter the data, among other things, without having errors on the Zap.
The most common reasons rows are added to the top of a spreadsheet instead of the bottom of the sheet are:
- Column A is missing a header
- Column A is being hidden
Making column A visible again, and making sure it has a header usually resolves this problem.
The Zapier Support team may, at times, ask for read-only access to a Google Sheet connected to a Zap, to better assist you. The best way of doing this is to set the Google spreadsheet as available to view by anyone with the link, so that anyone on the Zapier Support team can help. You can do that by following the instructions to share a file publicly, in this Google Sheets help guide.
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.
Zapier relies on Google Drive's notification system to identify when there are changes in a spreadsheet. This currently works well for Google Sheets in your own Google Drive, but can be a problem for spreadsheets within a Team Drive.
In order to help Google prioritize that issue, please visit Google’s issue tracker, and click on the "star" at the top, to indicate that you would like the problem to be fixed.
In the meantime, Zapier has created separate triggers that work with Team Drives. These are marked with (Team Drive) in the Trigger Event dropdown menu, in the Choose App & Event part of your trigger step.
Currently, it’s not possible to dynamically choose a spreadsheet in Google Sheets the same way you can choose options for other fields with custom values. This is because there isn’t a way for us to pick up the fields to be updated unless we know specifically which spreadsheet/worksheet combination will be used when the Zap runs.
If you'd like to be able to dynamically select worksheets in a Zap step, reach out to the Support team so that you can be added to a feature request for this.
Due to how Google Sheets creates rows, the Zap can have trouble if multiple runs are in progress at the same time, or if you have multiple Zaps writing to the same spreadsheet.
If you see this issue on your Zap, add a Delay after Queue step before your Create Spreadsheet Row(s) step to to help spread out the row creation timing.