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
Delete a Sheet
Make an API Call
Searches
Search Rows
Get Range Values
Search Rows (Advanced)
Usage Limits
List Sheets
How to post and get images to and from Googles Sheets
Prerequisites
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.
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.
When 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.
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.
1. Open the spreadsheet where you want to install the extension.
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.
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.
You can find over 100 predefined Google Sheets sample templates at www.integromat.com/en/templates/google-sheets.
Retrieves values from every newly added row in the spreadsheet.
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 variables in the output are then called by the headers. If the No option is selected, the module also retrieves the first table row, and the variables in the output are then called simply 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 in the reply will be calculated and formatted according to the cell's formatting. Formatting is based on the spreadsheet's locale, not the requesting user's locale. For example, if Unformatted value The values will be calculated, but not formatted in the reply. For example, if Formula The values will not be calculated. The reply will include the formulas. For example, if |
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 (to the left of the decimal) counts the days since December 30th 1899. The fractional portion (to the 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 3 pm 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. |
Integromat allows you to use the custom function INTEGROMAT in Google Sheets similarly to 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.
You can simply use the function like built-in functions in Google Sheet.
Create a new scenario with the following modules:
Webhook URL |
Establish a connection to the spreadsheet using the Integromat add-on. |
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 into the desired field using Integromat. |
1. Create a 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 the user changes the amount, the INTEGROMAT function re-calculates the Total - USD according to the current exchange rate:
This module watches for changes in all the cells of a spreadsheet. It means that when you update numerous cells in one row, one-by-one, Integromat will then receive multiple updated events.
Webhook | Establish a connection to the spreadsheet using the Integromat add-on. |
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 into 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 remain 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 What happens when the Insert rows option is selected (the Add a Row module is executed 3 times): Overwrite The new data overwrites the existing data in the areas where 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 What happens when the Overwrite option is selected (the Add a Row module is executed 3 times): |
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 . |
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 . |
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 remain 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. |
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 . |
This module is to be used together with the Perform a Function module.
Response type | Select whether you insert text or a number into the sheet. |
Value | Map the value from the previous module you want to insert into the 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 |
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 . |
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: |
||||||||||||||||
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.
|
||||||||||||||||
Sheets | Add sheets to the new spreadsheet. |
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 into 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 in 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 remain 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. |
Deletes a specified sheet from a spreadsheet.
Connection | Establish a connection to the spreadsheet using your Google account. |
Spreadsheet | Select or map the Google spreadsheet that contains the sheet you want to delete. |
Sheet | Select or map the sheet you want to delete. |
Allows you to perform a custom API call.
Connection | Establish a connection to the spreadsheet using your Google account. |
URL |
Enter a path relative to For the list of available endpoints, refer to the Google Sheets API Documentation.
|
Method |
Select the HTTP method you want to use: GET POST PUT PATCH DELETE |
Headers | Enter the desired request headers. You don't have to add authorization headers; we already did that for you. |
Query String | Enter the request query string. |
Body | Enter the body content for your API call. |
The following API call returns specified spreadsheet details.
URL:/spreadsheets/{{spreadsheetID}}
Method:GET
The result can be found in the module's Output under Bundle > Body:
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 variables in the output are then called by the headers. If the No option is selected, the module also retrieves the first table row, and variables in the output are then called simply 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 in the reply will be calculated and formatted according to the cell's formatting. Formatting is based on the spreadsheet's locale, not the requesting user's locale. For example, if Unformatted value The values will be calculated, but not formatted in the reply. For example, if Formula The values will not be calculated. The reply will include the formulas. For example, if |
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 (to the left of the decimal) counts the days since December 30th 1899. The fractional portion (to the 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 3 pm 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). |
Retrieves 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.
|
Value render option |
Formatted value The values in the reply will be calculated and formatted according to the cell's formatting. Formatting is based on the spreadsheet's locale, not the requesting user's locale. For example, if Unformatted value The values will be calculated, but not formatted in the reply. For example, if Formula The values will not be calculated. The reply will include the formulas. For example, if |
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 (to the left of the decimal) counts the days since December 30th 1899. The fractional portion (to the 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 3 pm 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). |
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. |
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, the service is intended for data visualization where the row numbers aren't needed. You can find more information about the query language in the documentation. |
Retrieves a list of all sheets in a spreadsheet.
Connection | Establish a connection to the spreadsheet using your Google account. |
Spreadsheet | Select or map the Google spreadsheet you want to retrieve sheets from. |
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.
Use the Search Rows (Advanced) module & use this formula to get the columns that 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
runScenario
and click "OK":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");
}
If you store a Date value in a spreadsheet without any formatting,
it will appear as text in ISO 8601 format in the spreadsheet. 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:
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.
When getting an image from Google Sheets, first make sure you enter the image as a formula. For example:=IMAGE("https://i.ytimg.com/vi/MPV2METPeJU/maxresdefault.jpg")
making use of the =IMAGE(...)
After you have done so, open the Google Sheets module (e.g. Watch Rows, Search Rows, Get a Cell) and select the Show advanced settings. Then select the Formula option in the Value render option field.
The output will be as shown below:
Then you can extract the URL using the replace function.
The output will be just the URL.
To be able to post an image, make sure to enter the =IMAGE(...)
formula that will be used in the cell. Then enter the Image URL.