Google sheet trigger on new row

The Google Sheets trigger on my Zap stopped working

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:

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.

I'm seeing an errors that mention 'range' (cannot parse range, requested writing within range)

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:

The Google Sheets trigger is marked "instant" but it still takes a few minutes to trigger

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.

Some of my fields have gone missing in the Zap editor

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.

Zap triggers unexpectedly/rows trigger too soon

“New or Updated Spreadsheet Row” trigger behavior

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.

"New Spreadsheet Row" trigger behavior

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.

Manually triggering rows in Google Sheets

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”).

The "Updated Spreadsheet Row" trigger stopped triggering

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.

Interpreting dates, times and timestamps in Google Sheets

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:

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.

SSLError The read operation timed out

*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:

1. 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.
2. 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.
3. 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're considering an alternate solution, an app like Airtable, or a database, like MySQL, can help better handle large databases.

Error: Access_token not found inside refresh_token response

If you see this error, it means that the connection from Google Sheets to Zapier failed. To fix this:

1. Go to the Google Sheets connections page.
2. Click the specific connection.
3. Click Reconnect.

The format of my data changes when entered into my spreadsheet

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.

My dates are coming through incorrectly

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.

Information sent by the Zap appears in the spreadsheet as #ERROR

If you see #ERROR in a cell where a value was sent from Zapier, it means Google is interpreting the value as a formula. Phone numbers containing a leading "+", for example, can cause this. Google Sheets then tries to calculate it and returns an error, as it's not a valid formula.

This can be fixed by adding a single leading apostrophe character ' to the value being sent. This will force Google Sheets to treat the value as text.

400 Error: Blank rows cannot be written; use delete instead

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:

404 not found error

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.

400 bad request

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.

I'm trying to delete data using the "Update Spreadsheet Row" action, but nothing happens

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.

When/how do I use the "Create Spreadsheet Row(s) (with line item support)" action?

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 your trigger doesn't provide line items you can use the Create Spreadsheet Row action instead.

When/how do I use "Find Spreadsheet Row(s) (with line item support)"?

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.

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.

​

Error: "Invalid query parameter value for grid_id"

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:

Column names don't match the actual column header on the sheet

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:

1. In Google Sheets, delete the blank row.
2. 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 is skipping blank rows

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.

Rows are being added to the top of the spreadsheet instead of the bottom

The most common reasons rows are added to the top of a spreadsheet instead of the bottom of the sheet are:

Making column A visible again, and making sure it has a header usually resolves this problem.

Sharing a Google Sheet with Zapier Support

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.

The Zap is creating duplicate rows in my Google Sheet

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.

Triggering on new/updated rows in a sheet on another Team Drive

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 Event dropdown menu, in the Choose App & Event part of your trigger step.

Errors when using a custom value for spreadsheet or worksheet ID

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.

"Create Spreadsheet Row(s)" Action results in mixed row values or rows overwriting each other.

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.

The spreadsheet I want to use doesn't appear in the dropdown menu

If your spreadsheet doesn't appear in the Spreadsheet dropdown menu, check its file format. Google Sheets can open and edit .XLSX files, but Zapier can't open them. To fix this:

1. In Google Sheets, save the file as a Google spreadsheet.
2. In Zapier, click the Spreadsheet dropdown menu.
3. If your spreadsheet doesn’t appear in the dropdown menu, click Load More.
4. Select your spreadsheet name.