Google Sheets integration block

The Google Sheets integration block is used to set up an integration with the Google Sheets service. This integration allows you to read the necessary data from spreadsheets or write it right from the script.

How to add

To add the block in the script, select Google Sheets integration in the block menu.

Creating an integration

If you create an integration for the first time, the following notification will appear.

Click Go to settings. You will be automatically redirected to the integration settings. Here, in the Connect section, click Google account.

Next, choose an account and agree to the requested access.

The integration is connected. Now go to the script to set block parameters.

caution

If you decide to remove the integration with a specific account in the settings, but then create it again, do not forget to reconfigure the block in the script.
When you create an integration, a unique ID is assigned to you. This ID will be reassigned if you create a new integration.

Parameters

Fill in the following fields:

• Account
  • If you connected several accounts, select the one you need from the list.
• Spreadsheet
  • Select a spreadsheet from the list or create a new one.
  • The current script name will be the name of the new spreadsheet.
• Sheet
• Action
  • Read data from cells
  • Write data to cells
  • Write data to line
  • Delete line/column
  • Clear cells
• List of parameters
  • Specify cell coordinates or values you want to pass in the Google spreadsheet.

Actions

caution

Google Sheets limits the number of API requests per minute. Learn more about limits

Read data from cells

The method allows reading data from cells in a Google spreadsheet.

Specify the cell number and a variable which will be used for storing data.

tip

This variable can be used in other blocks.

In the example below, we get the client’s first and last names from the database.

Write data to cells

The method is used to write data to cells in a Google spreadsheet. The cell coordinates and the value being passed can be specified either explicitly or using a variable.

You can pass an array of values in List of parameters. In this case, the data will be written in cells following the specified cell.

caution

If you save your data to a non-empty cell, its existing data will be overwritten.

Here is an example of using the method to update the client database.

Write data to line

The method allows you to add arbitrary values to the beginning of the first line where the required number of cells is empty.

tip

The method does not overwrite data already existing in the spreadsheet but appends new data instead.

In the example below, we add a new client to the spreadsheet.

Delete line or column

The method allows deleting lines and columns from the spreadsheet. You can also delete a range of columns or lines.

caution

When deleting a line from the spreadsheet, other lines move up. When deleting a column, other columns shift left.

In the Value or variable field, specify a range of lines or columns you want to delete.

• To delete a line, specify a range as n:n where n is a number of the line to be deleted. For example, if you want to delete the first line, write 1:1.
• To delete a column, specify a range as A:A where A is a letter for the column to be deleted.
• To delete multiple contiguous lines, specify a range as n:m where n is the first line and m is the last line to be deleted. For example, if you want to delete the lines from the first to the third one, write 1:3.
• To delete multiple contiguous columns, specify a range as A:C where A is the first column and C is the last column to be deleted.

The method also allows deleting multiple lines and columns that are not contiguous.

To do that, click Add value when editing the block and add the other range you want to delete.

In the video below, we delete the client from the spreadsheet.

Clear cells

The method allows clearing specific cells or ranges of cells from the spreadsheet.

caution

When clearing the cells, the lines and the columns are not deleted or moved.

To clear a specific cell, specify the cell number in the Value or variable field when editing the block, e.g., B7.

To delete a range of cells, specify it as:

upper-left cell:lower-right cell

For example, if you write A3:C6, you will delete the range where A3 is an upper-left cell and C6 is a lower-right one.

To clear one more cell or range, click Add value and specify its number in this field.

In the example below, we clear several cells in Google Sheets.