Google Sheets: Update or append a row by key

Finds the row whose key column holds a key and writes the given cells into it, or appends a new row when there is none.

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

When to use

• Keeping a sheet in step with another system: an order’s status, a customer’s last contact.
• An upsert that a retry can’t turn into a duplicate: a retried step finds the row its first attempt appended and writes the same cells again.

values is an object keyed by header ({ "status": "shipped" }). The row keeps the cells you don’t name; null keeps a cell and "" empties it. The key matches a cell as shown or as stored, so 1001 finds a cell formatted as 1,001. The step fails, writing nothing, when a named column isn’t in the header row, when the key is in more than one row, or when values would change the key itself. An appended row needs the table to start in column A.

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
sheet	string	yes	The sheet (tab) holding the table, by name, e.g. Orders.
key_column	string	yes	The header of the column that identifies a row, e.g. order_id.
key	string	yes	The value to look for in key_column. The row holding it is updated; with no such row, one is appended with it.
values	object	yes	The cells to write, by column header, e.g. {“status”: “shipped”, “total”: 12}. Columns left out keep their value; null keeps a cell as it is and “” empties it.
header_row	integer	no	The row holding the column names. The rows below it are searched for the key. Default 1.
value_input	string	no	Parse the values as if typed (formulas, dates), or store them as given. One of "USER_ENTERED", "RAW". Default "USER_ENTERED".

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

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

Field	Type	Description
key	string	The key, as looked for.
outcome	string	Whether the key’s row was updated or a new row appended. A retry that finds the row its first attempt appended says updated. One of "updated", "appended".
range	string	The cells written, in A1 notation.
row	integer	The row number written.

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: updateRowByKey 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

version: hyperspool-graph/v1
nodes:
  - id: on_shipped
    type: trigger
    config: { event: order.shipped }
  - id: mark
    type: google-sheets.update_row_by_key
    config:
      credential: sheets
      with: { spreadsheet_id: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms }
      input:
        sheet: Orders
        key_column: order_id
        key: { $expr: "input.payload.order_id" }
        values:
          status: shipped
          tracking: { $expr: "input.payload.tracking" }
edges:
  - { from: on_shipped, to: mark }

The output says in outcome whether the row was updated or appended, with its row number and the range written.

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