<!-- BusyBot node reference — https://busybot.net/tools/google-sheets/ -->

> Node: Google Sheets (`google_sheets`) · Action · v1
> Category: Data & Storage · Credentials: Google Sheets OAuth2 (`googleSheetsOAuth2Api`), Google Service Account (`googleApi`)
> Updated: 2026-08-16

# Google Sheets

> Read, write, append, update, and manage data in Google Sheets spreadsheets.

## Overview

Read, write, append, update, and manage data in Google Sheets spreadsheets. The node works at two levels: the **Sheet** resource operates on a single tab inside a spreadsheet — appending rows, upserting, updating matched rows, reading rows with filters, clearing ranges, deleting rows or columns, and creating or deleting tabs — while the **Spreadsheet** resource creates and deletes whole spreadsheets.

Rows are exchanged as key-value objects keyed on the sheet's header row, so incoming item fields can be mapped onto columns automatically. Authentication is available via OAuth2 or a service account.

**Category:** Data & Storage  
**Tool Name:** `google_sheets`  
**Version:** 1

**Appearance:** Icon: `lucide-Sheet` | Color: `#0F9D58`

## Node Type

**Action** — processes input items and produces output

## Input / Output

| Direction | Port(s) |
|-----------|--------|
| Input | `main` |
| Output | `main`, `error` |

## Credentials

This tool requires **Google Sheets OAuth2**, **Google Service Account** credentials.
See the [Credentials Guide](https://busybot.net/credentials/) for setup instructions.

Configure the one that matches the Authentication parameter — OAuth2 or service account, not both.

### Resources

| Resource | Value |
|----------|-------|
| Sheet | `sheet` |
| Spreadsheet | `spreadsheet` |

### Operations

| Operation | Value | Description |
|-----------|-------|-------------|
| Append Row | `append` | Append a new row to a sheet |
| Append or Update Row | `appendOrUpdate` | Upsert: update matching rows or append if no match |
| Clear | `clear` | Clear rows, columns, or entire sheet data |
| Create | `create` | Create a new sheet tab or spreadsheet |
| Delete | `delete` | Delete a sheet tab or spreadsheet |
| Delete Rows or Columns | `deleteRowsOrColumns` | Delete specific rows or columns from a sheet |
| Get Rows | `getRows` | Read rows from a sheet, optionally filtered |
| Update Row | `update` | Update rows matching specified column criteria |

`Create` and `Delete` are offered for both resources; every other operation belongs to the **Sheet** resource.

### Parameters

#### Sheet: Append Row

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Spreadsheet ID | `string` | Yes | — | The ID of the Google Sheets spreadsheet (found in its URL). Supports expressions. |
| Sheet Name | `string` | Yes | `Sheet1` | The name of the sheet tab to operate on. |
| Data Mode | `options` | No | `autoMapInputData` | How to map incoming item data to the sheet columns. |
| | | | | Options: `autoMapInputData` (use item JSON fields; names must match column headers), `defineBelow` (manually specify column name/value pairs), `nothing` (do not append any row) |
| Fields to Send | `fixedCollection` | No | `{}` | Explicit column/value pairs to write. _(shown when Data Mode is `defineBelow`)_ |
| — Column Name | `string` | No | — | The sheet column header name. |
| — Field Value | `string` | No | — | The value to write into this column. Supports expressions. |
| Options (`appendOptions`) | `collection` | No | `{}` | Write behaviour. |
| — Cell Format | `options` | No | `USER_ENTERED` | How values are interpreted when written. |
| | | | | Options: `USER_ENTERED` (interpret values as if typed by the user — formulas evaluated, dates parsed), `RAW` (store values exactly as provided) |
| — Header Row | `number` | No | `1` | The 1-based row index that contains column headers. |
| — Use Google Sheets Append API | `boolean` | No | `false` | Use the native Google Sheets append endpoint instead of a targeted PUT. Ensures no data is overwritten. |

#### Sheet: Append or Update Row

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Spreadsheet ID | `string` | Yes | — | The ID of the Google Sheets spreadsheet (found in its URL). Supports expressions. |
| Sheet Name | `string` | Yes | `Sheet1` | The name of the sheet tab to operate on. |
| Matching Columns | `string` | Yes | — | Comma-separated column names used to identify matching rows (e.g. "email" or "id,region"). |
| Data Mode | `options` | No | `autoMapInputData` | How to map incoming item data to sheet columns for upsert. |
| | | | | Options: `autoMapInputData`, `defineBelow`, `nothing` |
| Fields to Send | `fixedCollection` | No | `{}` | Explicit column/value pairs to write. _(shown when Data Mode is `defineBelow`)_ |
| — Column Name | `string` | No | — | The sheet column header name. |
| — Field Value | `string` | No | — | The value to write into this column. Supports expressions. |
| Options (`appendOrUpdateOptions`) | `collection` | No | `{}` | Write behaviour and sheet layout. |
| — Cell Format | `options` | No | `USER_ENTERED` | How values are interpreted when written. |
| | | | | Options: `USER_ENTERED`, `RAW` |
| — Header Row | `number` | No | `1` | The 1-based row index that contains column headers. |
| — Data Start Row | `number` | No | `2` | First row that contains data (after the header). |
| — Use Google Sheets Append API | `boolean` | No | `false` | Use the native Google Sheets append endpoint instead of a targeted PUT. |

#### Sheet: Update Row

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Spreadsheet ID | `string` | Yes | — | The ID of the Google Sheets spreadsheet (found in its URL). Supports expressions. |
| Sheet Name | `string` | Yes | `Sheet1` | The name of the sheet tab to operate on. |
| Matching Columns | `string` | Yes | — | Comma-separated column names used to identify matching rows (e.g. "email" or "id,region"). |
| Data Mode | `options` | No | `autoMapInputData` | How to map incoming item data to sheet columns for update. |
| | | | | Options: `autoMapInputData`, `defineBelow`, `nothing` |
| Fields to Send | `fixedCollection` | No | `{}` | Explicit column/value pairs to write. _(shown when Data Mode is `defineBelow`)_ |
| — Column Name | `string` | No | — | The sheet column header name. |
| — Field Value | `string` | No | — | The value to write into this column. Supports expressions. |
| Options (`updateOptions`) | `collection` | No | `{}` | Write behaviour and sheet layout. |
| — Cell Format | `options` | No | `USER_ENTERED` | How values are interpreted when written. |
| | | | | Options: `USER_ENTERED`, `RAW` |
| — Header Row | `number` | No | `1` | The 1-based row index that contains column headers. |
| — Data Start Row | `number` | No | `2` | First row that contains data (after the header). |

#### Sheet: Get Rows

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Spreadsheet ID | `string` | Yes | — | The ID of the Google Sheets spreadsheet (found in its URL). Supports expressions. |
| Sheet Name | `string` | Yes | `Sheet1` | The name of the sheet tab to operate on. |
| Filters | `fixedCollection` | No | `{}` | Column/value conditions a row must satisfy to be returned. |
| — Column | `string` | No | — | The column header name to filter by. |
| — Value | `string` | No | — | The value the column must equal for a row to be included. Supports expressions. |
| Combine Filters | `options` | No | `AND` | How multiple filter conditions are combined. |
| | | | | Options: `AND` (all filter conditions must match), `OR` (at least one filter condition must match) |
| Return All | `boolean` | No | `false` | Whether to return all matching rows or only up to the limit. |
| Limit | `number` | No | `50` | Maximum number of rows to return. _(shown when Return All is `false`)_ |
| Options (`getRowsOptions`) | `collection` | No | `{}` | Value rendering and sheet layout. |
| — Output Formatting | `options` | No | `FORMATTED_VALUE` | How cell values are rendered in the output. |
| | | | | Options: `FORMATTED_VALUE` (values as displayed in the sheet), `UNFORMATTED_VALUE` (raw computed values), `FORMULA` (the formula string instead of the calculated value) |
| — Header Row | `number` | No | `1` | 1-based row index containing column headers. |
| — Data Start Row | `number` | No | `2` | 1-based index of the first data row (after headers). |
| — Range | `string` | No | — | Limit reading to this A1-notation range (optional). If omitted, reads the whole sheet. |

#### Sheet: Clear

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Spreadsheet ID | `string` | Yes | — | The ID of the Google Sheets spreadsheet (found in its URL). Supports expressions. |
| Sheet Name | `string` | Yes | `Sheet1` | The name of the sheet tab to operate on. |
| Clear | `options` | No | `wholeSheet` | What to clear in the sheet. |
| | | | | Options: `wholeSheet`, `specificRows`, `specificColumns`, `specificRange` |
| Keep First Row | `boolean` | No | `false` | Preserve the header row (row 1) when clearing the whole sheet. _(shown when Clear is `wholeSheet`)_ |
| Start Row Number | `number` | No | `1` | The 1-based row number to start clearing from. _(shown when Clear is `specificRows`)_ |
| Number of Rows to Clear | `number` | No | `1` | How many rows to clear. _(shown when Clear is `specificRows`)_ |
| Start Column | `string` | No | `A` | The column letter to start clearing from (e.g. A, B, C). _(shown when Clear is `specificColumns`)_ |
| Number of Columns to Clear | `number` | No | `1` | How many columns to clear. _(shown when Clear is `specificColumns`)_ |
| Range | `string` | Yes | `A:F` | A1 notation range to clear (e.g. "A1:D10" or "Sheet1!A1:D10"). _(shown when Clear is `specificRange`)_ |

#### Sheet: Delete Rows or Columns

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Spreadsheet ID | `string` | Yes | — | The ID of the Google Sheets spreadsheet (found in its URL). Supports expressions. |
| Sheet Name | `string` | Yes | `Sheet1` | The name of the sheet tab to operate on. |
| To Delete | `options` | No | `rows` | Whether to delete rows or columns. |
| | | | | Options: `rows`, `columns` |
| Start Row Number | `number` | No | `2` | The 1-based row number to start deleting from. _(shown when To Delete is `rows`)_ |
| Number of Rows to Delete | `number` | No | `1` | How many rows to delete. _(shown when To Delete is `rows`)_ |
| Start Column | `string` | No | `A` | The starting column letter to delete. _(shown when To Delete is `columns`)_ |
| Number of Columns to Delete | `number` | No | `1` | How many columns to delete. _(shown when To Delete is `columns`)_ |

#### Sheet: Create

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Spreadsheet ID | `string` | Yes | — | The ID of the Google Sheets spreadsheet (found in its URL). Supports expressions. |
| Title | `string` | Yes | `New Sheet` | The name of the new sheet tab. Supports expressions. |
| Sheet Name (within spreadsheet) | `string` | Yes | — | The spreadsheet ID in which to create the new sheet. Used as the spreadsheet ID for this operation. |
| Options (`sheetCreateOptions`) | `collection` | No | `{}` | Appearance and placement of the new tab. |
| — Hidden | `boolean` | No | `false` | Whether the sheet is hidden in the UI. |
| — Right to Left | `boolean` | No | `false` | Whether the sheet uses RTL text direction. |
| — Sheet ID | `number` | No | `0` | Numeric sheet ID. Cannot be changed once set. |
| — Sheet Index | `number` | No | `0` | Position of the sheet tab within the spreadsheet (0-based). |
| — Tab Color | `string` | No | — | Hex color for the sheet tab (e.g. #FF0000). |

#### Sheet: Delete

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Spreadsheet ID | `string` | Yes | — | The ID of the Google Sheets spreadsheet (found in its URL). Supports expressions. |
| Sheet Name | `string` | Yes | `Sheet1` | The name of the sheet tab to operate on. |

#### Spreadsheet: Create

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Title | `string` | No | — | The title of the new spreadsheet. Supports expressions. |
| Sheets | `fixedCollection` | No | `{}` | Tabs to create inside the new spreadsheet. |
| — Title | `string` | No | — | Name of the sheet tab. |
| — Hidden | `boolean` | No | `false` | Whether the sheet is hidden in the UI. |
| Options (`spreadsheetCreateOptions`) | `collection` | No | `{}` | Locale and recalculation settings. |
| — Locale | `string` | No | — | Spreadsheet locale in ISO format (e.g. en_US). |
| — Recalculation Interval | `options` | No | — | How often volatile functions are recalculated. |
| | | | | Options: empty (Google's default), `ON_CHANGE`, `MINUTE`, `HOUR` |

#### Spreadsheet: Delete

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Spreadsheet ID | `string` | Yes | — | The ID of the spreadsheet to delete. Supports expressions. |

#### All Operations

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Authentication | `options` | No | `oAuth2` | Authentication method to use. |
| | | | | Options: `oAuth2`, `serviceAccount` |
| Google Account | `credential` | No | — | Connect or select your Google account. _(shown when Authentication is `oAuth2`)_ |
| Service Account Email | `string` | Yes | — | The email address of the Google service account. _(shown when Authentication is `serviceAccount`)_ |
| Private Key | `string` | Yes | — | The private key from the service account JSON key file. _(shown when Authentication is `serviceAccount`)_ |
| Max Concurrency | `number` | No | `5` | Maximum number of items to process in parallel. |

## Output Data

The result **replaces** the item's JSON — the incoming fields do not pass through.

**Get Rows fans out: one output item per matching row.** Each row is an object keyed on the header row, plus `row_number` — the 1-based position of the row in the sheet, which you need if you want to address that row again later:

```json
{
  "Name": "Ada Lovelace",
  "Email": "ada@example.com",
  "Status": "Active",
  "row_number": 4
}
```

Every other operation produces exactly one item:

| Operation | Output |
|-----------|--------|
| `sheet` / `append` | `success: true` and `appended` — `1`, or `0` when Data Mode is `nothing`. |
| `sheet` / `appendOrUpdate` | `success: true`, `operation` (`update` or `append`, telling you which branch ran) and `rowsAffected`. |
| `sheet` / `update` | `success: true`, `updated` (how many rows matched) and `rows` (their row numbers). When nothing matched: `updated: 0` and a `message`. |
| `sheet` / `clear` | `success: true` and `cleared` — the A1 range that was cleared. |
| `sheet` / `create` | `success: true`, `spreadsheetId` and the new tab's properties. |
| `sheet` / `delete` | `success: true`, `spreadsheetId` and `deletedSheetId`. |
| `sheet` / `deleteRowsOrColumns` | `success: true`, `spreadsheetId`, `dimension` (rows or columns) and the `startIndex` / `endIndex` that were removed. |
| `spreadsheet` / `create` | The new spreadsheet resource as Google returned it, including its `spreadsheetId`, `spreadsheetUrl` and `sheets`. |
| `spreadsheet` / `delete` | `success: true` and `deletedSpreadsheetId`. |

## Usage Examples

- Use Google Sheets in a workflow to read, write, append, update, and manage data in Google Sheets spreadsheets

## Example Configuration

Append a row using the incoming item's fields:

```json
{
  "type": "google_sheets",
  "parameters": {
    "resource": "sheet",
    "operation": "append",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Sheet1",
    "dataMode": "autoMapInputData",
    "appendOptions": {
      "headerRow": 1,
      "cellFormat": "USER_ENTERED"
    }
  }
}
```

Append a row with explicit column mapping:

```json
{
  "type": "google_sheets",
  "parameters": {
    "resource": "sheet",
    "operation": "append",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Sheet1",
    "dataMode": "defineBelow",
    "fieldsUi": {
      "fieldValues": [
        { "fieldId": "Name", "fieldValue": "{{ $json.fullName }}" },
        { "fieldId": "Email", "fieldValue": "{{ $json.email }}" }
      ]
    }
  }
}
```

Update rows matched on a column:

```json
{
  "type": "google_sheets",
  "parameters": {
    "resource": "sheet",
    "operation": "update",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Sheet1",
    "matchingColumns": "email",
    "dataMode": "autoMapInputData",
    "updateOptions": {
      "headerRow": 1,
      "dataStartRow": 2
    }
  }
}
```

Upsert — update if a row matches, otherwise append:

```json
{
  "type": "google_sheets",
  "parameters": {
    "resource": "sheet",
    "operation": "appendOrUpdate",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Sheet1",
    "matchingColumns": "id",
    "dataMode": "autoMapInputData",
    "appendOrUpdateOptions": {
      "headerRow": 1,
      "dataStartRow": 2
    }
  }
}
```

Read filtered rows:

```json
{
  "type": "google_sheets",
  "parameters": {
    "resource": "sheet",
    "operation": "getRows",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Sheet1",
    "filtersUI": {
      "values": [
        { "lookupColumn": "Status", "lookupValue": "Active" }
      ]
    },
    "combineFilters": "AND",
    "returnAll": false,
    "limit": 100
  }
}
```

Clear a block of rows:

```json
{
  "type": "google_sheets",
  "parameters": {
    "resource": "sheet",
    "operation": "clear",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Sheet1",
    "clearMode": "specificRows",
    "startIndex": 5,
    "rowsToDelete": 10
  }
}
```

Delete rows permanently:

```json
{
  "type": "google_sheets",
  "parameters": {
    "resource": "sheet",
    "operation": "deleteRowsOrColumns",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Sheet1",
    "toDelete": "rows",
    "startIndex": 2,
    "numberToDelete": 5
  }
}
```

Create a new tab in an existing spreadsheet:

```json
{
  "type": "google_sheets",
  "parameters": {
    "resource": "sheet",
    "operation": "create",
    "sheetName": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "title": "New Data Sheet",
    "sheetCreateOptions": {
      "hidden": false,
      "index": 1,
      "tabColor": "#4285f4"
    }
  }
}
```

Create a whole new spreadsheet:

```json
{
  "type": "google_sheets",
  "parameters": {
    "resource": "spreadsheet",
    "operation": "create",
    "title": "My New Spreadsheet",
    "sheetsUi": {
      "sheetValues": [
        { "title": "Data", "hidden": false },
        { "title": "Summary", "hidden": false }
      ]
    },
    "spreadsheetCreateOptions": {
      "locale": "en_US",
      "autoRecalc": "ON_CHANGE"
    }
  }
}
```

### Error Handling

| Mode | Behavior |
|------|----------|
| **stop** | Halts workflow on first error |
| **continue** | Skips failed items, passes successful ones through |
| **errorPort** | Routes failed items to Error output port |

## Tips

Read, write, append, update, and manage data in Google Sheets spreadsheets including sheet creation and row-level operations. Use when you need to interact with spreadsheet data for reporting, data collection, or syncing records. Returns row data as key-value objects or operation confirmations.

- **Sheet Name is the tab, not the spreadsheet title.** The one exception is Sheet → Create, where the Sheet Name (within spreadsheet) field holds the target **spreadsheet ID** and Title holds the new tab's name.
- **Auto-mapping matches on header text.** With Data Mode `autoMapInputData`, an item property is written to the column whose header has the same name; properties with no matching header are ignored. Use `defineBelow` when the names differ.
- **Header Row and Data Start Row describe your layout.** If your sheet has a title block above the headers, raise both numbers so the node reads the right row as headers and starts writing below them.
- **`USER_ENTERED` behaves like typing.** Formulas evaluate and dates parse; switch Cell Format to `RAW` when a value such as a leading-zero code must be stored verbatim.
- **Matching Columns can name more than one column.** All named columns must match for a row to be considered the same record.
- **Clear empties cells; Delete Rows or Columns removes them.** Clearing leaves the grid the same size, deleting shifts everything below or to the right.
- **Get Rows returns `row_number`.** Keep it if you plan to come back and change that exact row.
- **Get Rows fans out** — each matching row becomes its own item, so downstream nodes process rows individually.