Skip to content
hyperspool

Google Sheets: Read rows as objects

Reads the rows under a sheet’s header row as objects keyed by the header, a chunk at a time, however long the table is.

When to use

Section titled “When to use”
  • A sheet used as a small database: orders, sign-ups, a to-do list a person keeps up by hand.
  • Feeding a fan_out or a transform with records instead of lists of cells.

For a fixed block of cells, or when you want the grid as lists, use read_range. Empty rows are left out, and the read goes on to the bottom of the sheet, so rows below a gap are read too; a long empty stretch is read in larger requests, so it costs only a few. An empty header cell is named by its column letter, and a name used twice gets its letter added (id (B)).

Connection (with)

Section titled “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)

Section titled “Input (input)”
Field Type Required Description
sheet string yes The sheet (tab) to read, by name, e.g. Orders.
columns string no The columns the table spans, e.g. A:F. Default "A:Z".
header_row integer no The row holding the column names. The rows below it are read, one object each. Default 1.
value_render string no Cells as shown, as raw numbers, or as their formulas. One of "FORMATTED_VALUE", "UNFORMATTED_VALUE", "FORMULA". Default "FORMATTED_VALUE".
chunk_rows integer no Rows asked for per request. Lower it if a read is over the response size limit. Default 1000.

Settings

Section titled “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.
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.

Output

Section titled “Output”

What the action’s script returns, read as nodes.<id>.output.<field>.

Field Type Description
headers array of string The column names, from the header row. An empty header cell is named by its column letter.
count integer How many rows were read.
rows array of object One object per row under the header, keyed by column name. Empty rows are left out.

How it runs

Section titled “How it runs”
  • On an agent with the connector capability. The managed environments have one, so this needs no agent of your own.
  • A script: readRows from the connector’s sheets.js, which may call only sheets.googleapis.com. The credential goes on those requests as headers, and the script never sees it.
  • The script decides what counts as a failure, so retry_on and fail_on_error_status do not apply.

Example

Section titled “Example”
version: hyperspool-graph/v1
nodes:
  - id: orders
    type: google-sheets.read_rows
    config:
      credential: sheets
      with: { spreadsheet_id: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms }
      input:
        sheet: Orders
        columns: A:F
  - id: open
    type: transform
    config:
      mode: all
      expr: nodes.orders.output.rows.filter(r => r.status === "open")
edges:
  - { from: orders, to: open }

credential names a Google account a project member connected under Settings > Connections > Connected accounts; it counts as a bearer credential. The rows are nodes.orders.output.rows, each like { "order_id": "1001", "status": "open", … }.

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