Google Sheets

Integromat Updated November 28, 2018 16:48

Getting started with Google Sheets

To use any of the above modules, you need to connect your Google account to Integromat. If you do not have a Google account yet, you can create one here

The Google Sheets file format

In order for Integromat to work with your Google Sheet file, the file must comply with several requirements.

The trigger "Watch a worksheet" retrieves only new rows that have not been filled in before. For example, if row 23 is newly filled in, the trigger will process it. Afterwards, the previous row will be deleted and overwritten with the new filled in row. Once this happens, the trigger will not return row 23 a second time.

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

The file must not contain a full blank row. If a blank row exists, it terminates the file and no rows after the blank row will be processed.

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:

By clicking the Allow button in the bottom right hand corner, Integromat will be given access. Afterwards, you will be redirected back to the Integromat administration page. Now, you can continue creating your scenario.

Setting up the Select rows action

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

In the settings of the Select rows action, you can specify the sort order of the returned results and the maximum number of returned rows. You may also 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")

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

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:

1. The first module gets the ID of the first row. Maximum number of returned rows is set to 1.
2. 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.
3. The last module deletes all the rows one by one by repeatedly deleting the first row. Row ID shall be set to the ID of the first row, which is provided by the first module: