Articles in this section

Google Sheets

Integromat Support Team Updated January 08, 2020 19:39

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

Getting Started with Google Sheets
Connecting Modules using the Google Account Sign In
Connecting Instant Triggers using the Integromat Google Sheets Add-on
Triggers
Watch Rows
Perform a Function
Perform a Function Example
Watch Changes
Actions
Add a Row
Clear a Cell
Clear a Row
Update a Cell
Delete a Row
Perform a Function - Responder
Add a Sheet
Get a Cell
Create a Spreadsheet
Update a Row
Searches
Search Rows
Get a Range Content
Search Rows (Advanced)
Usage Limits

Getting Started with Google Sheets

Prerequisites

A Google account
Integromat Google Sheets extension (optional - required for instant triggers)

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 Modules using the Google Account Sign In

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.

Connecting Instant Triggers (Perform a Function, Watch Changes) using the Integromat Google Sheets Add-on

In order to use instant triggers, the Integromat add-on must be installed in your spreadsheet and a connection between the Integromat module and Google Sheets must be established.

Integromat Add-on Installation

1. Open the spreadsheet you want to install the extension to.

2. Go to Add-ons -> Get add_ons...

3. Search for the Integromat add-on.

4. Click on the +Free button to install the Integromat add-on.

5. Click on the Allow button to grant access rights.

6. The Integromat add-on is now installed.

Connecting the Instant Trigger Module to a Google Sheets Spreadsheet

1. Copy the provided webhook address to the clipboard and click OK.

2. Open your spreadsheet.

3. Open the Integromat add-on settings.

4. Paste the webhook URL you have copied in step 1 to the Webhook URL field in the Watch Updates settings section or Perform a Function section, depending upon which module you are using.

5. Click the Save button to save the changes in the Integromat add-on.

Did you know?

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

Triggers

Watch Rows

Retrieves values from every newly added row in the spreadsheet.

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 the spreadsheet using your Google account.
Spreadsheet	Select the spreadsheet that contains the sheet you want to watch.
Sheet	Select the sheet you want to watch for a new row.
Table contains headers	Select whether the spreadsheet contains the header row. If the Yes option is selected, the module doesn't retrieve the header row as output data and variable names in the output are then called by the headers. If the No option is selected, then the module also retrieves the first table row and variable names in the output are then called just A, B, C, D, etc.
Row with headers	Enter the range of the header row. E.g. `A1:F1`
Value render option	Formatted value The values will be calculated and formatted in the reply according to the cell's formatting. Formatting is based on the spreadsheet's locale, not the requesting user's locale. For example, if `A1` is `1.23` and `A2` is `=A1` and formatted as currency, then `A2` would return `"$1.23".` Unformatted value The values will be calculated, but not formatted in the reply. For example, if `A1` is `1.23` and `A2` is `=A1` and formatted as currency, then `A2` would return the number "`1.23"`. Formula The values will not be calculated. The reply will include the formulas. For example, if `A1` is `1.23` and `A2` is `=A1` and formatted as currency, then A2 would return `"=A1"`.
Date and time render option	Serial number Instructs date, time, datetime, and duration fields to be outputted as doubles in "serial number" format, as popularized by Lotus 1-2-3. The whole number portion of the value (left of the decimal) counts the days since December 30th 1899. The fractional portion (right of the decimal) counts the time as a fraction of the day. For example, January 1st 1900 at noon would be 2.5, 2 because it's 2 days after December 30th 1899, and .5 because noon is half a day. February 1st 1900 at 3pm would be 33.625. This correctly treats the year 1900 as not a leap year. Formatted string Instructs date, time, datetime, and duration fields to be outputted as strings in their given number format (which is dependent on the spreadsheet's locale).
Limit	Set the maximum number of results that Integromat will work with during one execution cycle.

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

Perform a Function

Integromat allows you to use the custom functionINTEGROMAT in Google Sheets similarly as built-in functions like AVERAGE, SUM etc. It allows you to perform the function in Integromat and return the result back to the sheet. The function INTEGROMAT accepts as many parameters as you need.

How to perform a custom function

You can simply use the function like built-in functions in Google Sheet.

Create a new scenario with the following modules:

Perform a Function - module receives the parameters passed to the function
Perform a Function - Responder - module returns the result of the function execution back to the sheet

Webhook URL

Establish a connection to the spreadsheet using the Integromat add-on.

Perform a Function Example

Sample sheet

The Total - EUR amount SUM will be converted, according to the current exchange rate, to the Total - USD amount and will be inserted to the desired field using Integromat.

1. Create the scenario. Use the following modules:

Google Sheets > Perform a Function, Currency > Convert an Amount between Currencies, Google Sheets > Perform a Function - Responder

Google Sheets > Perform a Function

Generate a webhook and paste it into the Integromat add-on in Google Sheets.

Currency > Convert an Amount between Currencies

Converts the mapped EUR amount to USD.

Google Sheets > Perform a Function - Responder

Inserts the converted amount into the sheet cell.

2. Run the scenario

3. Enter the INTEGROMAT function into the desired cell to load the converted amount.

When a user changes the amount, the INTEGROMAT function re-calculates the Total - USD according to the current exchange rate:

The function is executed only when a user made the changes directly in the Google Sheets app. It means that if the parameter of the function is, for instance, the sum of rows, and you update one of the summed rows using the API, then the function will not be executed.

Watch Changes

This module watches for changes in all of the cells of a spreadsheet. It means when you update numerous cells in one row, one-by-one, then Integromat will receive multiple updated events.

The module watches only for changes made by a user in the Google Sheets app. Script executions and API requests do not trigger this module.

The module does not watch for newly added rows to the sheet.

Webhook

Establish a connection to the spreadsheet using the Integromat add-on.

Actions

Add a Row

Adds a row to a sheet.

Connection	Establish a connection to the spreadsheet using your Google account.
Mode	Select whether you want to select the spreadsheet and sheet manually or by mapping. Manual mapping is useful, for example, when a new spreadsheet is created in an Integromat scenario and you want to add data in the newly created spreadsheet directly in the scenario.
Spreadsheet	Select the Google spreadsheet.
Sheet	Select the sheet you want to add a row to.
Values	Enter (map) the desired cells of the row you want to add.
Value input option	User entered The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc., following the same rules that are applied when entering text into a cell via the Google Sheets UI. Raw The values the user has entered will not be parsed and will be stored as-is.
Insert data option	Insert rows Rows are inserted for the new data. Example Behavior when the Insert rows option is selected (the Add a Row module is executed 3 times): Overwrite The new data overwrites existing data in the areas it is written. (Note: adding data to the end of the sheet will still insert new rows or columns so the data can be written.) Example The behavior when the Overwrite option is selected (the Add a Row module is executed 3 times):

Clear a Cell

Deletes a value from a specified cell.

Connection	Establish a connection to the spreadsheet using your Google account.
Spreadsheet	Select the Google spreadsheet.
Sheet	Select the sheet you want to delete a cell from.
Cell	Enter the ID of the cell you want to delete. E.g. `A5`

Clear a Row

Deletes values from a specified row.

Connection	Establish a connection to the spreadsheet using your Google account.
Spreadsheet	Select the Google spreadsheet.
Sheet	Select the sheet you want to delete data from.
Row number	Enter the number of the row you want to delete. E.g. `23`

Update a Cell

Connection	Establish a connection to the spreadsheet using your Google account.
Spreadsheet	Select the Google spreadsheet.
Cell	Enter the ID of the cell you want to update. E.g. `A5`
Value	Enter the new value.
Value input option	User entered The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc., following the same rules that are applied when entering text into a cell via the Google Sheets UI. Raw The values the user has entered will not be parsed and will be stored as-is.

Delete a Row

Deletes a specified row.

Connection	Establish a connection to the spreadsheet using your Google account.
Spreadsheet	Select the Google spreadsheet.
Sheet	Select the sheet you want to delete a row from.
Row number	Enter the number of the row you want to delete. E.g. `23`

Perform a Function - Responder

This module is to be used together with the Perform a Function module.

Response type	Select whether you insert text or a number to the sheet.
Value	Map the value from the previous module you want to insert into the sheet.

Add a Sheet

Creates a new sheet in a selected spreadsheet.

Connection Establish a connection to the spreadsheet using your Google account.

Spreadsheet Select the Google spreadsheet.

Properties

Title

Enter the name of the new sheet.

Index

Enter the sheet position. The default is 0 (places the sheet in the first place)

Get a Cell

Retrieves a value from a selected cell.

Connection	Establish a connection to the spreadsheet using your Google account.
Spreadsheet	Select the Google spreadsheet.
Sheet	Select the sheet that contains the cell you want to retrieve data from.
Cell	Enter the ID of the cell you want to retrieve data from. E.g. `A6`

Create a Spreadsheet

Connection Establish a connection to the spreadsheet using your Google account.

Title Enter the name of a new spreadsheet.

Locale

The locale of the spreadsheet in one of the following formats:

an ISO 639-1 language code such as en
an ISO 639-2 language code such as haw, if no 639-1 code exists
a combination of the ISO language code and country code, such as en_US

Recalculation interval

The amount of time to wait before volatile functions are recalculated:

On change

Volatile functions are updated upon every change.

On change and every minute

Volatile functions are updated upon every change and every minute.

On change and hourly

Volatile functions are updated upon every change and hourly.

Time zone Select the time zone of the spreadsheet.

Number format

Select the default format of all cells in the spreadsheet.

`TEXT`	Text formatting, e.g `1000. 12`
`NUMBER`	Number formatting, e.g, `1,000.12`
`PERCENT`	Percent formatting, e.g `10. 12%`
`CURRENCY`	Currency formatting, e.g `$1,000.12`
`DATE`	Date formatting, e.g `9/26/2008`
`TIME`	Time formatting, e.g `3:59:00 PM`
`DATE time`	Date+Time formatting, e.g `9/26/08 15:59:00`
`SCIENTIFIC`	Scientific number formatting, e.g `1. 01E+03`

Sheets Add sheets to the new spreadsheet.

Update a Row

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

Connection	Establish a connection to the spreadsheet using your Google account.
Mode	Select whether you want to select the spreadsheet and sheet manually or by mapping. Manual mapping is useful, for example, when a new spreadsheet is created in the Integromat scenario and you want to add data to the newly created spreadsheet directly in the scenario.
Spreadsheet	Select the Google spreadsheet.
Sheet	Select the sheet you want to update a row in.
Row number	Enter the number of the row you want to update.
Values	Enter (map) the values to the desired cells of the row you want to change (update).
Value input option	User entered The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. Raw The values the user has entered will not be parsed and will be stored as-is.

Searches

Search Rows

Searches rows using the filter options.

Connection	Establish a connection to the spreadsheet using your Google account.
Spreadsheet	Select the Google spreadsheet.
Sheet	Select the sheet you want to search the rows in.
Table contains headers	Select whether the spreadsheet contains the header row. If the Yes option is selected, the module doesn't retrieve the header row as output data and variable names in the output are then called by the headers. If the No option is selected, then the module also retrieves the first table row and variable names in the output are then called just A, B, C, D, etc.
Filter	Set the filter for the row to be searched by. Set filter values. You can also use logical operators, AND/OR in order to specify your selection. Example: In the following dialog, the row which contains the number 1 or 2 in the "column2" column will be searched.
Value render option	Formatted value The values will be calculated and formatted in the reply according to the cell's formatting. Formatting is based on the spreadsheet's locale, not the requesting user's locale. For example, if `A1` is `1.23` and `A2` is `=A1` and formatted as currency, then `A2` would return `"$1.23".` Unformatted value The values will be calculated, but not formatted in the reply. For example, if `A1` is `1.23` and `A2` is `=A1` and formatted as currency, then `A2` would return the number "`1.23"`. Formula The values will not be calculated. The reply will include the formulas. For example, if `A1` is `1.23` and `A2` is `=A1` and formatted as currency, then A2 would return `"=A1"`.
Date and time render option	Serial number Instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by Lotus 1-2-3. The whole number portion of the value (left of the decimal) counts the days since December 30th 1899. The fractional portion (right of the decimal) counts the time as a fraction of the day. For example, January 1st 1900 at noon would be 2.5, 2 because it's 2 days after December 30th 1899, and .5 because noon is half a day. February 1st 1900 at 3pm would be 33.625. This correctly treats the year 1900 as not a leap year. Formatted string Instructs date, time, datetime, and duration fields to be outputted as strings in their given number format (which is dependent on the spreadsheet's locale).

Get a Range Content

Connection	Establish a connection to the spreadsheet using your Google account.
Spreadsheet	Select the Google spreadsheet.
Sheet	Select the sheet you want to get the range content from.
Range	Enter the range you want to get. E.g. `A1:D25`.
Table contains headers	Row with headers Enter the range of the table headers. E.g. `A1:F1`. If you leave the field empty, Integromat will suppose that the header is in the first row of the specified range.
Value render option	Formatted value The values will be calculated and formatted in the reply according to the cell's formatting. Formatting is based on the spreadsheet's locale, not the requesting user's locale. For example, if `A1` is `1.23` and `A2` is `=A1` and formatted as currency, then `A2` would return `"$1.23".` Unformatted value The values will be calculated, but not formatted in the reply. For example, if `A1` is `1.23` and `A2` is `=A1` and formatted as currency, then `A2` would return the number "`1.23"`. Formula The values will not be calculated. The reply will include the formulas. For example, if `A1` is `1.23` and `A2` is `=A1` and formatted as currency, then A2 would return `"=A1"`.
Date and render option	Serial number Instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by Lotus 1-2-3. The whole number portion of the value (left of the decimal) counts the days since December 30th 1899. The fractional portion (right of the decimal) counts the time as a fraction of the day. For example, January 1st 1900 at noon would be 2.5, 2 because it's 2 days after December 30th 1899, and .5 because noon is half a day. February 1st 1900 at 3pm would be 33.625. This correctly treats the year 1900 as not a leap year. Formatted string Instructs date, time, datetime, and duration fields to be outputted as strings in their given number format (which is dependent on the spreadsheet's locale).

Search Rows (Advanced)

Connection	Establish a connection to the spreadsheet using your Google account.
Spreadsheet	Select the Google spreadsheet.
Sheet	Select the sheet you want to search the rows.
Query	Searches rows using Google Charts Query Language. The language is similar to SQL and it is possible to make complex queries. Unfortunately, the response doesn't contain IDs of returned rows. Due to Google Charts, service is intended for data visualization where the row numbers aren't needed. More information about the query language you can find in the documentation.

Usage Limits

If the error 429: RESOURCE_EXHAUSTED occurs, you have exceeded the API rate limit.

The Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately. There is no daily usage limit.

See more details at developers.google.com/sheets/api/limits.

Tips & Tricks

How to get Empty Cells from a Google Sheet

Use the Search Rows (Advanced) module & use this formula to get the columns which are empty.

select * where E is null

Here "E" is the column & "is null" is the condition. You can create a more advanced query using Google Query Lang

Add a button in a sheet to run a scenario

In Integromat, insert the Webhook > Custom webhooks module/trigger in the scenario and configure it (see Webhooks).
Copy the webhook's URL.
Execute the scenario.
In Google Sheets, choose "Insert" -> "Drawing..." from the main menu bar.
Click the "Text box" icon:
Design a button and click the "Save and Close" button in the top-right corner:
The button will be placed in your worksheet. Click the three vertical dots in the button's top-right corner:
Choose "Assign script..." from the menu.
Enter the name of your script (function), e.g. runScenario and click "OK":
Choose "Tools" -> "Script editor" from the main menu bar.
Insert the following code:
- The name of the function must correspond to the name you specified in step 9.
- Replace the https://hook.integromat.com/xxx...xxx URL with the webhook's URL you copied in step 2.
```
function runScenario() {
  UrlFetchApp.fetch("https://hook.integromat.com/xxx...xxx");
}
```
Press Ctrl+S to save the script file, enter a project name and click "OK".
Switch back to Google Sheets and click your new button.
Grant the required authorization to the script:
In Integromat, verify that the scenario has successfully executed.

Storing dates in a spreadsheet

If you store a Date value in a spreadsheet without any formatting,

it will appear in the spreadsheet as text in ISO 8601 format. However, Google Sheets formulas or functions that work with dates do not understand this text. E.g. formula =A1+10 will display the following error:

To help the GS to understand the date, format it with the formatDate() function. The correct format passed to the function as the second argument depends on the spreadsheet's locale settings. Choose File ▶ Spreadsheet settings from the main menu to verify/set the locale:

Once you have verified/set the proper locale, determine the corresponding date and time format by choosing Format ▶ Number from the main menu. The format is displayed next to the Date time menu item:

To compose the correct format that should be passed to the formatDate() function, refer to the list of tokens for date/time formatting.

The following example shows the use of M/D/YYYY HH:mm:ss format for the United States locale:

Exploiting Google Sheets functions

If you miss a built-in function, but it is featured by Google Sheets, you may exploit it: see Using Functions, section Exploiting Google Sheets functions.

Was this article helpful?

15 out of 28 found this helpful

Google Sheets

Table of Contents

Getting Started with Google Sheets

Connecting Modules using the Google Account Sign In

Connecting Instant Triggers (Perform a Function, Watch Changes) using the Integromat Google Sheets Add-on

Integromat Add-on Installation

Connecting the Instant Trigger Module to a Google Sheets Spreadsheet

Did you know?

Triggers

Watch Rows

Perform a Function

How to perform a custom function

Perform a Function Example

Watch Changes

Actions

Add a Row

Clear a Cell

Clear a Row

Update a Cell

Delete a Row

Perform a Function - Responder

Add a Sheet

Get a Cell

Create a Spreadsheet

Update a Row

Searches

Search Rows

Get a Range Content

Search Rows (Advanced)

Usage Limits

Tips & Tricks

How to get Empty Cells from a Google Sheet

Add a button in a sheet to run a scenario

Storing dates in a spreadsheet

Exploiting Google Sheets functions