Working with Google Sheets

Kathyana Queeman

Updated May 09, 2024 13:43

The easiest way to get started working with Google Sheets is to utilize pre-built flow steps from the marketplace. You can source flow steps from the marketplace directly in flow builder. Here are the most common use cases.

Export: Read all data from spreadsheet

Use this pre-built 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 select a connection to your Google Sheets account, the following pre-filled form will be displayed for you to configure your export.

To configure your export:

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.

Example: 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.
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.

Import: Update existing rows and append new rows in spreadsheet

Use this pre-built 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.

After you select a connection to your Google Sheets account, the following pre-filled form will be displayed for you to configure your import.

To configure your import:

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.

Example: '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.
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 all you need to do is populate the source field column. Then, run your flow!

Going deeper using Google's native APIs

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.

Methods

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.

Clear all data from your Google Sheet

In this final example, you’re clearing data from a Google Sheet.

To configure your import:

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'.
Range (using A1 notation) (required): Enter the range you would like to clear, using A1 notation.

Example: To clear 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.