• Documentation >
• Apps >
• Productivity >
• Google Sheets

Google Sheets

Getting started with Google Sheets

Prerequisites:

• A Google account

• Ibexa Connect Google Sheets extension (optional - required for instant triggers)

In order to use Google Sheets with Ibexa Connect, you must have a Google account. If you do not have one, you can create one at accounts.google.com.

Connecting Modules Using the Google Account Sign In

To connect Google Sheets to Ibexa Connect, you must first connect your Google account.

1. Go to your Ibexa Connect scenarioand choose the Google Sheets module you want to use.

2. Next to Connection, click Add.

3. Name your connection, then click Save.

   

4. Ibexa Connect redirects you to the Google website where you are prompted to grant Ibexa Connect access to your account.

5. Choose the Google account you want to connect.

6. Confirm the dialog by clicking Allow.

   

You have successfully established the connection.

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

In order to use instant triggers, you must install the Ibexa Connect add-on in your spreadsheet and establish a connection between the Ibexa Connect module and Google Sheets.

Ibexa Connect add-on installation

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

2. Go to Extensions > Add-ons > Get add-ons

   

3. Search for the Ibexa Connect add-on.

4. Click on +Free to install the Ibexa Connect add-on.

5. Click Allow to grant access rights.

6. You have now installed the Ibexa Connect add-on.

   

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 Ibexa Connect 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 Save.

Did you know?

You can find over 100 predefined Google Sheets sample templates here.

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.

Tip

You can trigger a scenario in Ibexa Connect using a custom button in Google Sheets. See here for more information.

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 A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 will 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 will 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 will 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 (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 Ibexa Connect will work with during one execution cycle.

Caution

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

Watch Changes

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, Ibexa Connect will then receive multiple updated events.

Note

The module only watches for changes made in the Google Sheets app by the user. Script executions and API requests do not trigger this module. The module does not watch for newly added rows to the sheet.

Tip

You can trigger a scenario in Ibexa Connect using a custom button in Google Sheets. See here for more information.

Webhook	Establish a connection to the spreadsheet using the Ibexa Connect add-on.

Perform a Function

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

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 into the desired field using Ibexa Connect.

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

   

   1. Google Sheets > Perform a Function

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

      

   2. Currency > Convert an Amount between Currencies

      Converts the mapped EUR amount to USD.

      

   3. Google Sheets > Perform a Function - Responder

      Inserts the converted amount into the sheet cell.

      

2. Run the scenario

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

   

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

   

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 - the module receives the parameters passed to the function

• Perform a Function - Responder - the module returns the result of the function execution back to the sheet

Webhook URL

Establish a connection to the spreadsheet using the Ibexa Connect add-on.

Actions

Add a Row

Adds a row to a sheet.

Mode

Select whether you want to select the spreadsheet and sheet manually or by mapping.

Tip

Manual mapping is useful, for example, when a new spreadsheet is created in an Ibexa Connect 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):

Update a Row

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

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 Ibexa Connect 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.

Clear a Row

Deletes values from a specified row.

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.

Delete a Row

Deletes a specified row.

Note

To delete multiple rows based on filter criteria please see the Deleting Multiple Rows section.

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 or map the number from a preceding module, e.g. Search Rows.

Get a Cell

Retrieves a value from a selected cell.

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.

Update a Cell

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.

Clear a Cell

Deletes a value from a specified cell.

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.

Add a Sheet

Creates a new sheet in a selected spreadsheet.

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).

Create a Spreadsheet

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.

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 into the sheet.
Value	Map the value from the previous module you want to insert into the sheet.

Delete a Sheet

Deletes a specified sheet from a spreadsheet.

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.

Make an API Call

Allows you to perform a custom API call.

URL

Enter a path relative to https://sheets.googleapis.com/v4/. For example: /spreadsheets/{{spreadsheetID}}.

For the list of available endpoints, refer to the Google Sheets API Documentation.

Method

Select the HTTP method you want to use:

• GET - to retrieve information for an entry.

• POST - to create a new entry.

• PUT - to update/replace an existing entry.

• PATCH - to make a partial entry update.

• DELETE - to delete an entry.

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.

Example of Use - Get Spreadsheet

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

Search Rows

Searches rows using the filter options.

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.
Field Type	Select or map the field type to search the rows that match the specified type: Date Number String
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 A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 will 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 will 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 will 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 (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).

Search Rows (Advanced)

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.

Get Range Values

Retrieves range content.

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, Ibexa Connect will suppose that the header is in the first row of the specified range.
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 A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 will 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 will 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 will 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 (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).

List Sheets

Retrieves a list of all sheets in a spreadsheet.

Spreadsheet	Select or map the Google spreadsheet you want to retrieve sheets from.

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

Deleting Multiple Rows

To delete multiple rows based on filter criteria use the Search Rows module linked to the Delete a Row module as on the following example:

1. 1. Add the Search Rows module and Delete a Row module to the scenario.

2. Let's assume that you have a table where you need to delete all rows where column A equals to Y.

3. Open Search Rows module settings and set the fields as follows:

   Filter A Equal to Y Sort order Descending Order by Row number

   Caution

   Make sure that Sort order and Order by fields are set as above, otherwise, values will not be deleted correctly from the table!

4. Add the Delete a Row module to the scenario and connect it to the Search Row module.

5. Map the Row number item from the Search Rows module to the Delete a Row module's Row number field.

   

6. Run the scenario to delete values that match the filter criteria from the sheet.

   

How to Get Empty Cells from a Google Sheet

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

               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 Custom Button in a Sheet to Trigger a Scenario

1. In Ibexa Connect, insert the Webhook > Custom webhooks module/trigger into the scenario and configure it (see Webhooks).

2. Copy the webhook's URL.

3. Execute the scenario.

4. In Google Sheets, choose Insert > Drawing... from the main menu bar.

5. Click the Text box icon:

   

6. Design a button and click Save and Close in the top-right corner:

   

7. The button will be placed in your worksheet. Click the three vertical dots in the button's top-right corner:

   

8. Choose Assign script... from the menu.

9. Enter the name of your script (function). For example, runscenarioand click OK:

   

10. Choose Tools > Script editor from the main menu bar.

11. Insert the following code:

    • The name of the function must correspond to the name you specified in step 9.

    • Replace the https://hook.make.com/xxx...xxxURL with the webhook's URL you copied in step 2.

                                 function runScenario() {
        UrlFetchApp.fetch("https://hook.make.com/xxx...xxx");
      }
      

12. Press Ctrl+S to save the script file, enter a project name, and click OK.

13. Switch back to Google Sheets and click your new button.

14. Grant the required authorization to the script:

    

    

15. In Ibexa Connect, 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 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 theformatDate() 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 theformatDate() 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.

Posting and Getting Images from Google Sheets

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 and then enter the Image URL address.