Google Sheets Block

Read or write Google Sheets spreadsheet data.

Access To Spreadsheet

Before using this block, you need to give Automa access to the spreadsheet first. There're two ways that you can do that:

1. Share the spreadsheet with the public.
2. Share the spreadsheet with the Automa service account.

When you share the spreadsheet with the public, Automa only has read access to it.
And if you have a plan to update the cell values of the spreadsheet, you need to share the spreadsheet with the Automa service account. To do that, open the spreadsheet you want to share, click the share button on the top right, and input service@automa2.iam.gserviceaccount.com.

Spreadsheet Id

The Spreadsheet Id contains letters, numbers, hyphens, or underscores. And you can find it in the Google Sheets URL.

https://docs.google.com/spreadsheets/d/10FGdgjWhkFeJ8m-M5WC0zDIMnSqRFuctuZ1CkOr38mY/edit

Range

The range of the values of the cells that you want to get or update, you can define the cells range by using the A1 Notation like Sheet1!A1:B2 or R1C1 notation like Sheet1!R1C1:R2C2.

Get Spreadsheet Values

Get the cells values of the spreadsheet.

• Reference key
  The key for identifying the google sheets data.

• Use the first row as keys
  Use the first row of the spreadsheet as the object key. For example, when you have a spreadsheet like this.

  name	age
  foo	22
  bar	23

// option disabled
[["name", "age"], ["foo", 22], ["bar", 23]]

// option enabled
[{ "name": "foo", "age": 22 }, { "name": "bar", "age": 23 }]

Accessing Sheets Data

To access the spreadsheet values from an input of a block, you can use expressions like {{ googleSheets.referenceKey.path }} syntax.

Read more: Expressions

Update Spreadsheet Values

Update the cells values of the spreadsheet.

• Value input option
  Determines how input data should be interpreted, default to RAW.

  Value	Description
  RAW	The values the user has entered will not be parsed and will be stored as-is
  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.

  Read more on the Google developer page

• Data from
  The source of the data for updating the spreadsheet, defaults to table. When using the custom option, the inputted data must be an array of an arrays data type with valid JSON syntax.

  Example

  [["name", "age"], ["foo", 22], ["bar", 23, "text"]]

• Use keys as the first row
  Use the columns as the first row on the spreadsheet.