Google Sheets: Read range

Reads a block of cells in A1 notation and returns it as a list of rows, each a list of cells.

Kind: google-sheets.read_range
Palette: Integrations, under Google Sheets
Credential: a credential of kind bearer, named in credential
Other Google Sheets actions: Read rows as objects, Append rows, Update or append a row by key, Update range, Clear range, Get spreadsheet details, Create spreadsheet, Add sheet

When to use

A fixed block: a summary area, a lookup table, a single cell holding a setting.
When the grid itself matters more than a header: read by COLUMNS to get one list per column.

For a table with a header row, read_rows returns records keyed by column name instead. A sheet name with spaces is quoted in the range: 'Q3 sales'!A:D.

Connection (`with`)

Field	Required	Description
`spreadsheet_id`	yes	The id in the spreadsheet’s address: docs.google.com/spreadsheets/d/<id>/edit. For example: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms.

Input (`input`)

Field	Type	Required	Description
`range`	string	yes	A1 notation, e.g. Sheet1!A1:D50. A sheet name with spaces goes in single quotes: ‘Q3 sales’!A:D.
`value_render`	string	no	Cells as shown, as raw numbers, or as their formulas. One of `"FORMATTED_VALUE"`, `"UNFORMATTED_VALUE"`, `"FORMULA"`. Default `"FORMATTED_VALUE"`.
`major_dimension`	string	no	One list per row, or one per column. One of `"ROWS"`, `"COLUMNS"`. Default `"ROWS"`.

Settings

Beside with and input in the node’s config.

Field	Type	Required	Description
`credential`	string	yes	Name of the project credential the action authenticates with.
`fail_on_error_status`	boolean	no	Treat any 4xx/5xx as a failure. On by default: an action either did its job or did not. Default `true`.
`optional_when_untrusted`	boolean	no	Run without the credential when the run is untrusted (a fork pull request), instead of failing. The credential is still withheld.
`retry_on`	array	no	Status codes that fail the node instead of returning, so its `retry:` policy applies (e.g. [429, 503]).

Output

The HTTP response. The reply is under body: nodes.<id>.output.body.<field>.

Field	Type	Description
`status`	integer	The HTTP status code.
`headers`	object	The response headers.
`body`	object	The response body, parsed as JSON when it is JSON.
`body.majorDimension`	string
`body.range`	string	The range read, in A1 notation.
`body.values`	array of array	The cells, a list per row (or column). Trailing empty cells and rows are left out.

How it runs

On an agent with the connector capability. The managed environments have one, so this needs no agent of your own.
One GET request to sheets.googleapis.com.
Any 4xx or 5xx fails the step, unless fail_on_error_status is false. retry: asks again after a 5xx, a 401, 408, 409, 423, 425 or 429, or a 4xx with Retry-After, and not after any other 4xx, which would get the same answer.
429 waited out and asked again with backoff, with no retry: on the step.

Example

version: hyperspool-graph/v1
nodes:
  - id: limits
    type: google-sheets.read_range
    config:
      credential: sheets
      with: { spreadsheet_id: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms }
      input:
        range: Settings!B2
        value_render: UNFORMATTED_VALUE
  - id: check
    type: shell
    config:
      run: { $template: "./check-budget.sh {{ nodes.limits.output.body.values[0][0] }}" }
edges:
  - { from: limits, to: check }

The cells are at nodes.<id>.output.body.values. Trailing empty cells and rows are left out, so a range with nothing in it has no values at all.

See Connectors for credentials and versions, and the node reference for every other node.