Google Sheets — sheet/clear

When to Use

Reporting — weekly data refresh: A weekly reporting workflow clears all data rows in the "Weekly Sales" sheet (excluding the header row by using StartRow: 2), then immediately re-populates it with fresh data from the database. This pattern ensures the report always reflects current data without accumulating stale rows.
Operations — shift handover board: At the end of each shift, an automated workflow clears the "Active Tasks" sheet so the next shift starts with a clean board. The header row is preserved so the team's custom column structure is maintained.
Finance — budget template reset: At the start of each fiscal year, a workflow clears all value rows in the "Budget Template" sheet while preserving the column headers, formulas in the header area, and tab formatting — ready for the new year's data entry.
Marketing — campaign staging area: A staging sheet is used as an intermediate data buffer during campaign data enrichment. After the enrichment pipeline completes and results are written to the final sheet, the staging area is cleared to prevent stale data from affecting the next run.
Testing and QA: In automated QA workflows, a test sheet is cleared before each test run to ensure test data from previous runs does not interfere with assertions on the current run's output.

Data loss warning: Clearing a sheet erases cell values permanently via the Google Sheets API. There is no undo within a workflow. The values are only recoverable via Google Sheets' own revision history (available to the spreadsheet owner for up to 30 days). Always confirm the correct SpreadsheetId and SheetName before using this operation in automated workflows.

Configuration

Field	Required	Description
`CredentialId`	Required	Integer ID referencing a stored Google credential in the BizFirst Credentials Manager.
`SpreadsheetId`	Required	The Google Sheets spreadsheet ID from the URL.
`SheetName`	Required	Exact name of the tab to clear. Case-sensitive.
`ClearAll`	Optional	Boolean. Default `false`. When `true`, clears every cell in the entire sheet including the header row. When `false`, clears only the rows between `StartRow` and `EndRow` (inclusive). For most use cases, leave as `false` and specify a row range to preserve headers.
`StartRow`	Optional	Integer. The first row number (1-based) to include in the clear range. Required when `ClearAll = false`. Typically set to `2` to preserve the header row in row 1.
`EndRow`	Optional	Integer. The last row number (1-based) to include in the clear range. Required when `ClearAll = false`. If you don't know the last row, use a large number (e.g. `10000`) — the API will clear up to the last row with data.

Row range note: The clear operation affects all columns across the specified row range — there is no column-level restriction. To clear a specific column range, use sheet/update with blank values instead.

Validation Errors

Error Code	Cause
`VAL_MISSING_SPREADSHEET_ID`	`SpreadsheetId` is empty or whitespace.
`VAL_MISSING_SHEET_NAME`	`SheetName` is empty or whitespace.
`VAL_MISSING_ROW_RANGE`	`ClearAll = false` but `StartRow` is not provided.
`VAL_INVALID_ROW_RANGE`	`StartRow` is greater than `EndRow`, or either value is less than 1.
`SHEET_NOT_FOUND`	The specified `SheetName` does not exist in the spreadsheet.

Output Fields

Field	Type	Description
`clearedRange`	string	A1 notation of the cell range that was cleared, e.g. `"Weekly Sales!A2:Z1000"` or `"Weekly Sales"` (full sheet).
`spreadsheetId`	string	Echoed spreadsheet ID.
`sheetName`	string	Echoed sheet/tab name.
`status`	string	`"success"`.
`resource`	string	Always `"sheet"`.
`operation`	string	Always `"clear"`.

Sample Configuration

{
  "resource": "sheet",
  "operation": "clear",
  "CredentialId": 12,
  "SpreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
  "SheetName": "Weekly Sales",
  "ClearAll": false,
  "StartRow": 2,
  "EndRow": 5000
}

Sample Output

{
  "clearedRange": "Weekly Sales!A2:Z5000",
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
  "sheetName": "Weekly Sales",
  "status": "success",
  "resource": "sheet",
  "operation": "clear"
}

Expression Reference

Assuming this node is named clearSalesData in the workflow:

Expression	Returns	Description
`{{ $output.clearSalesData.clearedRange }}`	string	A1 notation of the cleared range — useful for logging.
`{{ $output.clearSalesData.spreadsheetId }}`	string	Spreadsheet ID for passing to the subsequent write node.
`{{ $output.clearSalesData.sheetName }}`	string	Sheet name for passing to the subsequent write node.
`{{ $output.clearSalesData.status }}`	string	"success" when cleared without error.

Node Policies & GuardRails

Irreversibility: Cell values cleared via the API cannot be recovered programmatically. Recovery requires the spreadsheet owner to use Google Sheets revision history (File → Version history). Always gate sheet/clear with a confirmation step or a config-driven flag (ENABLE_CLEAR=true) when used in automated scheduled workflows.
Preserve headers with StartRow: Set ClearAll = false and StartRow = 2 in almost all cases. Only use ClearAll = true when you intend to erase the header row as well — for example, before re-writing a new structure entirely.
Formula cells: sheet/clear removes both values and formulas. If your sheet has formula cells in the data area (e.g. computed columns), they will be deleted and must be rewritten after the clear. Consider using sheet/deleteRowsOrColumns followed by a fresh sheet/append instead, which lets you control exactly which rows are removed.
Formatting preserved: Cell formatting (font, colors, borders, number formats) is not affected by sheet/clear — only values are erased. This is by design: the sheet's visual structure is preserved for the next write cycle.
Quota: Each clear operation is one write request toward the 100 writes/minute per project quota.
Clear then append pattern: The most common and robust pattern for refreshing sheet data is: (1) sheet/clear with StartRow = 2 to remove old data rows, (2) sheet/append to write fresh data. Chain these two nodes sequentially — never in parallel.

Workflow Examples

Example 1 — Weekly Sales Report Refresh

// Trigger: Scheduled every Monday at 7 AM
// Node 1 — sheet/clear (clearOldData)
{
  "resource": "sheet", "operation": "clear",
  "CredentialId": 12,
  "SpreadsheetId": "{{ $env.SALES_SHEET_ID }}",
  "SheetName": "Weekly Sales",
  "ClearAll": false,
  "StartRow": 2,
  "EndRow": 10000
}

// Node 2 — HttpRequest: GET /api/sales/weekly for current week data
// Node 3 — sheet/append: write fresh rows to Weekly Sales
//   Uses SpreadsheetId: {{ $output.clearOldData.spreadsheetId }}
//   Uses SheetName: {{ $output.clearOldData.sheetName }}

Example 2 — Staging Area Reset After Pipeline

// At the end of a data enrichment pipeline, clear the staging sheet
// Node (final) — sheet/clear (resetStaging)
{
  "resource": "sheet", "operation": "clear",
  "CredentialId": 15,
  "SpreadsheetId": "{{ $env.PIPELINE_SHEET_ID }}",
  "SheetName": "Staging",
  "ClearAll": true
}
// ClearAll: true because staging sheet is rebuilt from scratch each run,
// including the header row, by the next pipeline execution.

Example 3 — QA Test Sheet Reset

// Before each automated test run, reset the test fixture sheet
{
  "resource": "sheet", "operation": "clear",
  "CredentialId": 20,
  "SpreadsheetId": "{{ $env.QA_SHEET_ID }}",
  "SheetName": "Test Data",
  "ClearAll": false,
  "StartRow": 2,
  "EndRow": 500
}
// After clear: append known test fixture rows
// Run assertions on downstream nodes

sheet/clear FormID SH03

When to Use

Configuration

Validation Errors

Output Fields

Sample Configuration

Sample Output

Expression Reference

Node Policies & GuardRails

Workflow Examples

Example 1 — Weekly Sales Report Refresh

Example 2 — Staging Area Reset After Pipeline

Example 3 — QA Test Sheet Reset