Google Sheets

The Google Sheets node reads and writes data in Google Sheets using the Google Sheets v4 API. It lets a workflow pull rows from a spreadsheet, append or update records, look up matching rows, and clear or delete ranges — useful for syncing data to and from spreadsheets, maintaining lookup tables, and logging workflow output to a sheet.

Overview

The Google Sheets node is essential when you need to:

• Read rows from a sheet - Pull a range and parse it into keyed objects (or return raw cell values)
• Append data - Add new rows to the end of a sheet's data
• Update rows - Match existing rows by a key column and write changes (optionally inserting rows that aren't found)
• Look up rows - Find rows matching one or more column/value pairs
• Clear a range - Empty the cell values in a range without deleting the structure
• Delete rows or columns - Remove dimensions from a sheet

Configuration

Authentication

How the node authenticates to Google (default Service Account):

Option	Value	Credential used
Service Account (default)	serviceAccount	googleApi (service account email + private key)
OAuth2	oAuth2	googleSheetsOAuth2Api (OAuth2 client + token)

See Credential for the fields of each.

Action

The operation to perform (default Read):

Action	Value	Description
Append	append	Append data to a sheet
Clear	clear	Clear data from a sheet
Delete	delete	Delete columns and rows from a sheet
Lookup	lookup	Look up a specific column value and return the matching row
Read (default)	read	Read data from a sheet
Update	update	Update rows in a sheet

Sheet ID (required)

The ID of the Google Sheet, found in the sheet URL: https://docs.google.com/spreadsheets/d/{ID}/. Supports expressions.

Sheet Name (required; all actions except Delete)

The sheet (tab) name. Defaults to Sheet1. Supports expressions. The node combines Sheet Name with Range into <sheetName>!<range> (URL-encoding the sheet name) before calling the API.

Range (required; all actions except Delete)

The A1 table range to read from or write to, e.g. A:F (default). Per the Google documentation it may also include the sheet, e.g. MySheet!A:F.

Action-specific parameters

Which fields appear depends on the selected action:

Field	Shown for	Description
RAW Data	Read, Update	When on, data is treated as raw cell arrays instead of being parsed into keyed objects. Default off.
Data Property	Read (RAW on), Update (RAW on)	The property name into/from which raw data is read or written. Default data. Supports expressions.
Data Start Row	Read, Lookup, Update (hidden when RAW on)	Index of the first row that contains data, not keys. Default 1. Minimum 1. Supports expressions.
Key Row	Read, Lookup, Append, Update (hidden when RAW on)	Index of the row containing the column keys. Default 0. Minimum 0. Case-sensitive matching. Supports expressions.
Lookup Column(s)	Lookup	One or more Lookup Column / Lookup Value pairs to match (all must match a row). Both fields support expressions.
Key	Update (RAW off)	The column name used to identify which existing row to update. Default id. Supports expressions.
Data	Append, Update	The data to write, as JSON (an array of objects, or a single object). Supports expressions.
Add If Not Found	Update (RAW off)	When on, rows whose key value isn't found are appended instead of skipped (upsert). Default off.
To Delete	Delete	Columns and/or rows to delete (see below).
Options	Append, Lookup, Read, Update	Additional options (see below).

To Delete (Delete action)

A repeatable collection with two groups, Columns and Rows. Each entry has:

Field	Type	Default	Description
Sheet	options	—	The numeric sheet (tab) ID to delete from. Required.
Start Index	number	0	The 0-based, inclusive start index of the column/row to delete.
Amount	number	1	Number of columns/rows to delete.

Each entry becomes a deleteDimension request, with endIndex = startIndex + amount.

Options (collection)

Option	Type	Default	Shown for	Description
Continue If Empty	boolean	false	Lookup, Read	By default the workflow stops if the lookup/read returns no values; enable to return an empty result instead.
Return All Matches	boolean	false	Lookup	By default only the first match is returned; enable to return all matches.
Value Input Mode	options	RAW	Append, Update	How written data is interpreted: RAW (stored as-is) or User Entered (USER_ENTERED, parsed as if typed in the UI).
Value Render Mode	options	UNFORMATTED_VALUE	Lookup, Read	How values are rendered: Unformatted Value (default), Formatted Value (FORMATTED_VALUE), or Formula (FORMULA).

Credential

The node uses one of two credentials depending on Authentication. In both cases the credential record is loaded from the Credential store using the workflow's tenant and the node's credential ID, and the operation fails if the record has no data.

Service Account (googleApi)

The service account fields are stored on the credential and read at runtime:

Field	Required	Description
email	Yes	The service account email, used as the JWT iss (and sub unless delegatedEmail is set).
privateKey	Yes	The service account private key used to sign the JWT (RS256).
delegatedEmail	No	When present, used as the JWT sub for domain-wide delegation.

The node signs a 1-hour RS256 JWT (aud = https://oauth2.googleapis.com/token) requesting the drive, drive.file, and spreadsheets scopes, then exchanges it for an access token via the jwt-bearer grant at https://oauth2.googleapis.com/token. The token is sent as Authorization: Bearer <token> on each Sheets API call.

OAuth2 (googleSheetsOAuth2Api)

A standard Google OAuth2 credential. Its metadata sets:

• Authorization URL: https://accounts.google.com/o/oauth2/v2/auth
• Access Token URL: https://oauth2.googleapis.com/token
• Auth URI Query Parameters: access_type=offline&prompt=consent
• Scope: https://www.googleapis.com/auth/drive.file and https://www.googleapis.com/auth/spreadsheets

At runtime the node reads the stored clientId, clientSecret, accessTokenUrl, and oauthTokenData (access/refresh tokens). It signs the request with the stored access token; on a 401 or 403 it refreshes the token using the refresh token, retries the request once, and persists the refreshed oauthTokenData back to the credential store.

Output

All requests target https://sheets.googleapis.com/v4/spreadsheets/.... Output depends on the action.

Read

• Parsed (RAW off, default): an array of objects, one per data row, keyed by the header row (Key Row), starting at Data Start Row. When no values are returned, the result is [] unless Continue If Empty is on, in which case it is [{}].
• RAW (RAW on): a single-element array wrapping the raw cell values under the configured Data Property, e.g. [{ "data": [["id","name"],["1","Acme"]] }].

Lookup

An array of the matching row objects (keyed by the header row). Returns only the first match unless Return All Matches is on. Returns [] when nothing matches (or [{}] when both Continue If Empty and Return All Matches are on).

Append

Returns the input data that was written (the Data array as supplied), after appending it to the sheet via the Sheets values:append call. The raw API response is not returned.

Update

Returns the input data that was written (the Data array as supplied), after applying the update. Behind the scenes:

• Parsed (RAW off): matches rows by the Key column and issues a values:batchUpdate; rows whose key isn't found are skipped, or appended when Add If Not Found is on.
• RAW (RAW on): issues a values:batchUpdate with the supplied values for the <sheetName>!<range> range.

The raw API response is not returned.

Clear

Returns the Sheets values:clear API response, e.g.:

{
  "spreadsheetId": "…",
  "clearedRange": "Sheet1!A1:Z100"
}

Delete

Returns the input data wrapped as nested output (an array containing the input object/array), after issuing the spreadsheet batchUpdate with the deleteDimension requests. The raw API response is not returned.

Example Usage & Common Use Cases

Read a Sheet into Objects

Google Sheets:
  Authentication: Service Account
  Action: Read
  Sheet ID: {{ $json.sheetId }}
  Sheet Name: Orders
  Range: A:F

Output: [ { id: "1", customer: "Acme", … }, … ]

Append New Rows

[Build rows] → [Google Sheets]

Google Sheets:
  Action: Append
  Sheet ID: 1AbC…
  Sheet Name: Log
  Range: A:C
  Data: {{ steps.build.output }}   // [ { ts, level, message }, … ]
  Options → Value Input Mode: User Entered

Output: the rows that were appended

Update Rows by Key (with Upsert)

Google Sheets:
  Action: Update
  Sheet ID: 1AbC…
  Sheet Name: Inventory
  Range: A:D
  Key: sku
  Data: {{ $json.items }}
  Add If Not Found: true

Output: the rows supplied for update

Look Up a Row

Google Sheets:
  Action: Lookup
  Sheet ID: 1AbC…
  Sheet Name: Customers
  Range: A:E
  Lookup Column(s):
    - Lookup Column: email   Lookup Value: {{ $json.email }}
  Options → Return All Matches: true

Output: [ matching row object(s) ]

Clear a Range

Google Sheets:
  Action: Clear
  Sheet ID: 1AbC…
  Sheet Name: Staging
  Range: A:Z

Output: { spreadsheetId: "…", clearedRange: "Staging!A1:Z…" }

Delete Rows

Google Sheets:
  Action: Delete
  Sheet ID: 1AbC…
  To Delete → Rows:
    - Sheet: 0   Start Index: 1   Amount: 5

How It Works

1. Load the credential - The node loads the credential record from the Credential store by tenant + credential ID; if the record has no data it throws Credential [<credentialId>] not found.
2. Authenticate - For Service Account, the node signs a JWT and exchanges it for an access token via the jwt-bearer grant. For OAuth2, it signs the request with the stored access token and auto-refreshes it on a 401/403, persisting the new token.
3. Build the range - Sheet Name and Range are combined into <sheetName>!<range> (Delete works on numeric sheet IDs instead).
4. Call the Sheets API - Reads use GET .../values/{range}; append uses ...:append; clear uses ...:clear; update uses values:batchUpdate; delete uses the spreadsheet :batchUpdate with deleteDimension requests.
5. Transform data - On read/lookup, rows are parsed into objects keyed by the header row. On write, each object is converted to a cell row in the header's column order — booleans become 'TRUE'/'FALSE', objects are JSON-stringified, numbers/strings pass through, and missing/empty values become ''.
6. Return - Reads/lookups return the parsed (or raw) rows; clear returns the API response; append/update/delete return the input data.

Best Practices

• Match Key Row and Data Start Row to your layout - The defaults assume the header is row 0 and data starts at row 1. Adjust them if your sheet has extra header rows; key matching is case-sensitive.
• Use a stable Key for updates - Update matches rows by the Key column; pick a column with unique, stable values, and enable Add If Not Found only when inserts are desired.
• Pick the right Value Input Mode - Use User Entered when you want Google to parse numbers/dates/formulas; use RAW to store values exactly as supplied.
• Handle empty results explicitly - Enable Continue If Empty on read/lookup so the node returns an empty result instead of erroring when there's no data.
• Don't rely on append/update output for API metadata - These actions return the data you sent, not the Sheets API response; use Read afterward if you need to confirm what landed.
• Grant the service account access - For service-account auth, share the spreadsheet with the service account email, or calls return 403.

Troubleshooting

Common Issues

• "Error: No parameters found" - The node was invoked without parameters.
• Credential [<credentialId>] not found - The referenced credential record has no data (missing or empty in the store).
• The range "<range>" is not valid. - The Range for an update couldn't be parsed into start/end cells; use valid A1 notation like A:D.
• Could not find column for key "<key>"! - The Key column for an update isn't present in the Key Row.
• Missing required lookup column: <column> - A Lookup Column doesn't exist in the header row.
• "The key row does not exist!" - For lookup, the configured Key Row is out of range for the returned data.
• "Could not retrieve the key row!" / "Could not retrieve the key column!" / "Could not retrieve the column data!" - The sheet returned no values for the header/key range during an update or append; check the sheet, range, and that the header row has data.
• 403 from the Sheets API - The service account or OAuth2 user doesn't have access to the spreadsheet; share it with the account.

Debugging Tips

• Verify access first with a Read - A simple Read confirms the credential and Sheet ID before attempting writes.
• Check the resolved range - The node uses <sheetName>!<range>; confirm the Sheet Name (tab) exists and the Range is valid A1 notation.
• Confirm the service account is shared in - For 403s, share the spreadsheet with the service account email.
• Re-authorize OAuth2 on persistent 401s - If token refresh fails, the stored refresh token may be revoked or expired; reconnect the credential.

Related

• Spreadsheet - Parse or build spreadsheet/CSV content within a workflow
• HTTP Request - For Google Sheets endpoints not covered by this node
• Code - Build or reshape the JSON rows before an Append / Update call
• Set - Shape values before an Append / Update call