Collect new Typeform responses as rows on Google Sheets
Typeform + Google Sheets
When creating a Google Sheets Zap, you will be asked to connect a new account:
Click on that. You should then get a pop-up window from Google asking you to log-in to authorize the connection between that Google Sheets account and Zapier.
If you are already logged in to your Google account on your browser, you will be asked to approve access to Zapier.
After logging in and approving access, you'll be returned back to your Zap and your Google Sheet account will now be successfully connected.
To use Google Sheets with Zapier you need to set up your spreadsheet in a very specific way. If you don't set up your spreadsheet this way, you're likely going to have problems. Pay close attention.
These are the things you'll need from your Google Sheet to optimize it for use with a Zap:
Zaps can only add rows to the end of the spreadsheet and not any other row.
We recommend all your columns have text in the header/top row, but you must have text in the first column header, especially if you are using the Create Spreadsheet Row action. Without any text there, the Zap will send your data to the top of the sheet rather than to the bottom.
Do not add or delete rows to your spreadsheet, especially in the middle of data your Zap has already recognized. This can cause errors with your Zap.
If you are using the Updated Spreadsheet Row trigger, your Zap will trigger even if you update a column that you are not using in the action side of your Zap. Do Not Add New Columns.
Editing your spreadsheet while a Zap is already live is a bit like trying to fix a leaky faucet with the water turned on. Needless to say, you're going to get wet. So if you plan on editing your spreadsheet, then Turn Off your Zap first!
For triggers, each column should be represented by a field you can select in your Zap
For actions, you should have a different action field available for each column
If you’re looking to update a row in Google Sheets, you will need to first insert a search action to find the row ID. Updating a row will not work without adding this step first unless you plan on updating the exact same row every time the trigger fires. Let's walk through how to do this.
The first step here is to create a Zap and select your trigger app. For this example, let’s create a Zap that uses Eventbrite as the trigger that will update a sheet every time there is a new attendee to an event. This way, we can keep an up to date sheet of attendee contact information in a Google Sheet and track who is attending the event.
After setting up the trigger step, you’ll want to add a new step for Google Sheets and choose the "Lookup Spreadsheet Row,” action.
The “Lookup Value” field tells the Zap which data we are going to use from the trigger step to perform the search. For this example, we'll pick “Email" because we want to conduct a search using the email address of the attendee who registered for your event through Eventbrite.
In the “Lookup Column" section, you’ll need to choose which column you want to search by in the drop-down menu. In this case, we'll want to pick the “Email Address” column because we want to check if the email addresses of the Eventbrite Attendees match any of the email addresses in the “Email Address" column of our Google Sheet.
The idea here is that we want to use a value to scan through the spreadsheet with to see if any rows have a matching value. If there is a match, the step will be successful and the Zap will move onto the next action step, with the ability to use data from the row that was found.
Note: If there are multiple matches, the Zap will return the first row it finds from the bottom up.
You'll finish up Step 2 by testing it to find a matching row. If the row likely won’t exist yet, you can check the "Create Google Sheets Spreadsheet Row if it doesn't exist yet?” box and then tell the Zap how the row should be created.
Now that the Zap has found your row, you’ll want to use the row ID that was returned to dynamically update records. You’ll want to click on the “Add a step” button to create another step in your Zap.
For this next step, you’ll want to again, choose Google Sheets as your app, but this time, choose "Update Spreadsheet Row" as the action. In this step, you’ll want to choose the same Spreadsheet and Worksheet as your previous step.
When you get to the “Row ID” field, you will want to choose the row number or ID from the list of options returned from your “Find or Create Row” step. This will allow you to dynamically update the found row in your spreadsheet because of the previously added search step.
Under that field, you will want to be sure to fill out any and all fields you wanted to update in your spreadsheet, using the data from your trigger step. The last steps here then are to test your Zap, make sure the row was updated in your spreadsheet, and turn on your Zap.
Note: This workflow will find and return a single row, and update that single row. This Workflow does not find and update multiple rows at once.
Typeform + Google Sheets
Facebook Lead Ads + Google Sheets
Gmail + Google Sheets
Google Sheets + Trello
Google Sheets + Gmail
Google Sheets + Trello
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:
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.
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]?
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:
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:
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:
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 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:
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:
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:
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!
Get started with a Free account