Articles in this section

Google Sheets (legacy)

Integromat Support Team Updated October 11, 2020 07:22

This is the documentation for the legacy Google Sheets (legacy) app.
Here is the documentation for the new Google Sheets app.

Getting Started with Google Sheets

Prerequisites

a Google account

In order to use Google Sheets with Integromat, it is necessary to have a Google account. If you do not have one, you can create a Google account at accounts.google.com.

Connecting Google Sheets to Integromat

To connect Google Sheets to Integromat you must connect your Google account to Integromat. To do so, follow the general instructions for Connecting to services.

After you click the Continue button, Integromat will redirect you to the Google website where you will be prompted to grant Integromat access to your account.

Confirm the dialog by clicking the Allow button.

Did you know?

You can find over 100 predefined Google Sheets sample templates at www.integromat.com/en/templates/google-sheets.

Triggers

Watch a worksheet

This module retrieves the values added to a worksheet's rows.

The module retrieves only new rows that have not been filled in before. The trigger will not process an overwritten row.

Connection	Establish a connection to your Google account.
File	Select the spreadsheet containing the worksheet you want to watch.
Worksheet	Select the worksheet you want to watch.
Maximum number of returned rows	Set the maximum number of results that Integromat will work with during one cycle.

Choose where to start dialog

The first row of a worksheet is always considered to be a header row (columns, captions, etc.) and the trigger does not register it as one to process.

Select whether you want to process all rows from the beginning or set the row number where you want to begin to retrieve.

Select the first row

The first row in the file (header row) is assumed to contain the column names and is not taken into account. If you enter a value of 1, Integromat will watch entries starting from the first row after the header row.

If the worksheet contains a blank row, no rows after the blank row will be processed!

Actions

Add a row

Adds a row to the worksheet.

Connection	Establish a connection to your Google account.
File	Select the Google spreadsheet.
Worksheet	Select the sheet you want to add a row to.
Column headers	Enter (map) the desired cells of the row you want to add.

Example

This simple scenario adds a row using the information from a received email.

Result in Google Sheets

Update a row

This module allows you to change the cell content in a selected row.

Connection	Establish a connection to your Google account.
File	Select the Google spreadsheet.
Worksheet	Select the sheet you want to update a row in.
Column headers	Enter (map) the desired cells of the row you want to change (update).
Row ID	Enter the row ID. You can use the Google Sheets > Select rows module for mapping the Row ID (add it to the flow before this module), or you can input the Row ID manually.

Delete a row

Connection	Establish a connection to your Google account.
File	Select the Google spreadsheet.
Worksheet	Select the sheet you want to delete a row from.
Row ID	Enter the row ID. You can use the Google Sheets > Select rows module for mapping the Row ID (add it to the flow before this module), or you can input the Row ID manually.

Select rows

Every time the Select rows action runs, it goes through all the rows in a file according to the specified criteria. This distinguishes this action from the trigger, Watch a worksheet that only watches for new rows added since the last scenario run.

Connection	Establish a connection to your Google account.
File	Select the Google spreadsheet.
Worksheet	Select the sheet you want to retrieve the row data from.
Order by	Select the sort order.
Sort direction	Select the sort direction.
Continue the execution of the route even if the module returns no results	If enabled, the scenario will not be stopped by this module.
Filtering	Define a filter. Filters are written in the Google Spreadsheet API syntax. You can use standard comparison operators (>, >=, =, !=, <, <=), logical operators (and, or), and enclose filter expressions in parentheses. Example: Suppose, you have at your disposal a file containing among other characteristics, names of people, their age (column age), and height (column height). Let's say you want to select all people who are over 25 years old, with a height of up to 185 cm and named Peter or Charles (column name). The filter below selects only data that meet these criteria. `age > 25 and height < 185 and (name = "Peter" or name = "Charles")` To specify the name of the column that is to be filtered, use the name that is displayed in Integromat in parenthesis. For example, suppose you have a column named `Employee age (employeeage)`. The filter will look like:`employeeage > 32`
Maximum number of returned rows	Set the maximum number of results that Integromat will work with during one cycle.

Update a cell

Connection	Establish a connection to your Google account.
File	Select the Google spreadsheet.
Worksheet	Select the sheet you want to update a cell in.
Row number	Enter the number of the row you want to update.
Column number	Enter the number of the column you want to update. Column A=number 1, B=2, C=3, etc.
Value	Enter a value you want to replace the current value with.

Add a worksheet

Adds a new worksheet to the selected spreadsheet.

Connection	Establish a connection to your Google account.
File	Select the Google spreadsheet.
Title	Enter the name of the new worksheet.
Predefined count of rows	Enter the number of worksheet rows.
Predefined count of columns	Enter the number of worksheet columns.

Delete a worksheet

Connection

Establish a connection to your Google account.

File

Select the Google spreadsheet.

Worksheet ID

Enter the ID of a worksheet you want to delete. The ID can be obtained for example from the List worksheets module.

Get a cell

Connection	Establish a connection to your Google account.
File	Select the Google spreadsheet.
Worksheet	Select the sheet you want to get a cell from.
Cell	Enter the cell ID. E.g. `A5` or `G23`.

List worksheets

Connection	Establish a connection to your Google account.
File	Select the Google spreadsheet.
Continue the execution of the route even if the module returns no results	If enabled, the scenario will not be stopped by this module.
Maximum number of returned rows	Set the maximum number of results that Integromat will work with during one cycle.

Detailed Google Sheets documentation can be found on the Google Docs Editors Help Center.

Troubleshooting

Clearing a worksheet

Deleting all the rows in your worksheet can be tricky because if you delete a row, all the rows below the deleted one move up and their IDs change as the IDs are tied with row numbers. Thus, to delete all the rows the following flow shall be used:

The first module gets the ID of the first row. Maximum number of returned rows is set to 1.
The second module gets all the rows. Maximum number of returned rows is set to a number larger than the number of rows in the worksheet.
The last module deletes all the rows one by one by repeatedly deleting the first row. The Row ID shall be set to the ID of the first row, which is provided by the first module:

API v3 deprecation

As you may have already heard, Google Sheets v3 API that our Google Sheets (legacy) app is based on will be shut down on March 3, 2020. To keep your scenarios using the Google Sheets (legacy) legacy modules running after that date, replace them with the Google Sheets new modules based on the Google Sheets v4 API.

Please refer to the Replacing Legacy Modules With New Modules article to upgrade your legacy scenario to the latest version.

Was this article helpful?

13 out of 29 found this helpful