Google Sheets

Create spreadsheets, read, look up, append and update rows as a connected Google account.

The Google Sheets app reads and writes spreadsheets as a connected Google account.

Worked examples

Google Sheets — append a row for every row a Data Table query returned.

A Data Table step, Row · Get, with whatever filter you want. It answers {{ upstream.query.output.result.rows }} — an array of row objects keyed by your table's own columns.
A Loop over {{ upstream.query.output.result.rows }}.
Inside the loop, a Google Sheets step, Rows · Append rows:
- Spreadsheet id — the long id from the sheet's own URL.
- Range — Service calls, the name of the sheet to append to.
- Rows to write (Expression) — [["{{ loop.item.order_number }}", "{{ loop.item.customer }}", "{{ loop.item.status }}"]]

The extra brackets are not a typo: Sheets takes a grid, so [["a", "b"]] is one row of two cells and [["a"], ["b"]] is two rows of one. Appending the whole set in a single step is a matter of building that grid in an Edit Fields step first and passing it whole.

Looking a row up, then writing to it. Rows · Look up a row matches a column by name — the first row of the range you give it is the header. Each match comes back as { row, cells, values }: row is its 1-based position in the range, cells are the cells in order, and values is the same cells keyed by your header. So {{ upstream.lookup.output.result.0.values.Status }} reads the Status column of the first match, and a following Rows · Update a range pointed at Service calls!C{{ upstream.lookup.output.result.0.row }} writes back to the row it found.

A lookup matches exactly, as text, and returns every match rather than only the first. It reads the cells unformatted, so a cell displayed as 1,042 or €1.042,00 is matched as 1042 — the value in the cell, not the way the sheet shows it. A date is unformatted as Sheets' own serial number, so a date column is a poor thing to look up by. For that, and for anything looser — a partial match, a comparison, a date window — read the range with Read a range and sift it downstream: a Loop with an If / Else inside it.

The column name and the value both have to resolve to something. A lookup whose Column or Value to find came out empty is refused rather than answered, because an empty column name would otherwise match every row in the range — and the row it hands back is the one a following Update a range writes to.

The New row trigger keeps a row count: inserting a row in the middle does not fire, and deleting rows can make later additions look already read — append to the bottom. See What "new" means.

Operations

The App step's own settings — account, resource, operation, and { value, valueMode } per input — are described in the Apps overview. These are this app's operations.

Operation	What it does	Inputs (bold = required)	Permission
`spreadsheet.create`	Create a spreadsheet	`title`, `sheetTitle`	`spreadsheets`
`spreadsheet.sheets`	List sheets	`spreadsheetId`	`spreadsheets.readonly`
`values.read`	Read a range	`spreadsheetId`, `range`, `valueRenderOption`	`spreadsheets.readonly`
`values.lookup`	Look up a row	`spreadsheetId`, `range`, `column`, `value`	`spreadsheets.readonly`
`values.append`	Append rows	`spreadsheetId`, `range`, `values`, `valueInputOption`	`spreadsheets`
`values.update`	Update a range	`spreadsheetId`, `range`, `values`, `valueInputOption`	`spreadsheets`

Triggers

Use these on an App trigger node. It polls every pollMinutes and starts one run per new item.

Trigger	Fires for	Inputs (bold = required)
`row-added`	New row — Fires once for every row added below the ones already read.	`spreadsheetId`, `range`

Example

workflows/log-service-call.yaml

name: Log service call
description: Appends a service call to the log sheet.
graph:
  nodes:
    - id: start
      type: trigger_manual
      position: { x: 0, y: 0 }
      data: { description: 'Pass order and customer.' }
    - id: append
      type: app
      position: { x: 280, y: 0 }
      data:
        app: google-sheets
        version: 1.0.0
        resource: values
        operation: append
        connection: google-account
        inputs:
          spreadsheetId: { value: '1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789', valueMode: fixed }
          range: { value: 'Service calls', valueMode: fixed }
          values:
            { value: '[["{{ trigger.order }}", "{{ trigger.customer }}"]]', valueMode: expression }
          valueInputOption: { value: 'USER_ENTERED', valueMode: fixed }
  edges:
    - { id: start-to-append, source: start, target: append }

In a Package

The step travels whole; the account becomes a credential Slot the installer binds one of their own Google accounts to. See Apps in a Package.

elizon.yaml

slots:
  - id: google-account
    type: credential
    scheme: oauth2
    app: google
compliance:
  providers: [google]

Travels: app, version, resource, operation, inputs
Converted: connectionId → connection (the credential Slot)
Never carried: none