To export or import data into a Google spreadsheet, we recommend sourcing flow steps from the marketplace in Flow Builder or installing the Google Sheets Helper integration template from the integrator.io Marketplace (NA | EU).
This integration template contains one import and export that can be cloned and re-used for other flows:
-
Export: Read all data from spreadsheet
-
Import: Update existing rows and append new rows in spreadsheet
Use this export to read data from a spreadsheet and output easy-to-use records. You can clone this step any number of times for your different flows and use cases.
After you connect to Google Sheets you can configure your pre-filled export.
To configure the settings:
-
Spreadsheet ID (required): Enter your spreadsheet ID. For example, if the spreadsheet URL is https://docs.google.com/spreadsheets/d/1vBy7f9a8uvnhydRsSfxp24XpocJhOrsQy-Htz1kTfUo/edit#gid=904268349, the ID is '1vBy7f9a8uvnhydRsSfxp24XpocJhOrsQy-Htz1kTfUo'.
-
Range (using A1 notation) (required): Enter the range you would like to export, using A1 notation.
Notice
To export the entire sheet, enter the sheet name: 'Sheet1' . To exclude the first row and include only the first 100 rows, your range would be: 'Sheet1'!2:100 . You must wrap your sheet name with single quotes.
-
First record contains headers (optional): If there's a column header row in the range you're exporting from, the column headers will be used as the field IDs, and the header row itself will be discarded. If not, the zero-based column indexes will be used as the field IDs.
-
Headers (optional): This is calculated after you save your custom settings. These are the column headers discovered in the spreadsheet.
Warning
Required: If your sheet(s) contain headers, make sure every column has one. Missing headers in any column can lead to errors.
-
Calculated after save (updated range without column header row): This updated range will be used to export data from your spreadsheet. This range does not include the column header row. This range will be re-calculated every time you save your export.
-
Click Save.
Output filters can be added to the export after it is configured; for example, to support search criteria to only export specific rows in your spreadsheet.
Use this prebuilt import to upload data into a spreadsheet. This step updates existing rows it finds based on the key column you configured. If an existing row cannot be found, it will append new rows to the bottom of the spreadsheet.
The step is preconfigured with the exception of the Settings at the bottom of the drawer.
To configure the settings:
-
Spreadsheet ID (required): Enter your spreadsheet ID. For example, if the spreadsheet URL is https://docs.google.com/spreadsheets/d/1vBy7f9a8uvnhydRsSfxp24XpocJhOrsQy-Htz1kTfUo/edit#gid=904268349, then the id would be '1vBy7f9a8uvnhydRsSfxp24XpocJhOrsQy-Htz1kTfUo'.
-
Sheet name (required): Enter the name of the sheet you'd like to update.
Notice
'Sheet1' . Be sure to wrap your sheet name with single quotes!
-
Does your sheet have column headers? (optional): Is there a column header row in the sheet you are updating? If there's a column header row in the sheet you're updating, the headers will be used as the field IDs. If not, the zero-based column indexes will be used as the field IDs.
-
Headers (optional): This is calculated after you save your custom settings. These are the column headers discovered in the spreadsheet.
Warning
Required: If your sheet(s) contain headers, make sure every column has one. Missing headers in any column can lead to errors.
-
Column to find existing rows (required): Enter the column header that should be used to find existing rows. For example: ID , email , task_id, etc...
-
Source record field to find existing rows (required): Enter the field from the source record that should be used to lookup existing rows in the spreadsheet. For example, my_record_id.
Click Save.
If you have column headers, mappings will be auto-generated, and you only need to populate the source field column. Then, run your flow!
Note
You may encounter an 'Invalid JSON payload' error when you don't provide mappings in an import. The Google Sheets import requires a POST body.
integrator.io's connectors are incredibly powerful and give you full access to the underlying APIs. If you need to do any other API tasks related to Google Sheets, we recommend reading the following high-level guides and contacting support for help:
Set the Google Sheets URI according to the action you want to perform.
-
append: Appends values to a spreadsheet.
-
batchClear: Clears one or more ranges of values from a spreadsheet.
-
batchClearByDataFilter: Clears one or more ranges of values from a spreadsheet.
-
batchGet: Returns one or more ranges of values from a spreadsheet.
-
batchGetDataByFilter: Returns one or more ranges of values that match the specified data filters.
-
batchUpdate: Sets values in one or more ranges of a spreadsheet.
-
batchUpdateByDataFilter: Sets values in one or more ranges of a spreadsheet.
-
clear: Clears values from a spreadsheet.
-
get: Returns a range of values from a spreadsheet.
-
update: Sets values in a range of a spreadsheet.
In this example, you’re clearing your data from the Google Sheet you want to update with new data.
-
Choose the POST method.
-
Set your Relative URI to point to your desired sheet. Use the following format:
v4/spreadsheets/{spreadsheetId}/values/{range}:{method}
For example:
v4/spreadsheets/abcd123hijk456/values/A1:G300:clear
-
If you have multiple sheets in your range should include your sheet name:
v4/spreadsheets/abcd123hijk456/values/Sheet1!A1:Z9000:clear