Write Tools

Warning: All write tools run safety checks before executing. Writes that would destroy formula cells, overlap Protected Ranges, or cross structured note guards are blocked by default. Formula overwrite blocks include the existing formula, proposed value, and a small diff preview. Pass overrides only when intentionally bypassing the specific guard, and confirm formula previews before passing allowFormulaOverwrite: true.

write_range

Writes a 2D array of values to a specific range, overwriting existing content.

valueInputOption

Pre-write snapshot

Before writing, write_range fetches the current range values and saves them to write_history. This snapshot can be restored via restore_snapshot. Snapshot is skipped silently if the range exceeds 1,000 rows or 2 MB.

Note guard preflight

Before committing, write tools fetch notes for the target columns' header row and, for small target ranges, exact target-cell notes. Structured notes with [WARN], [FORMULA], [LOCK], [DO_NOT_WRITE], or [NO_WRITE] block the write unless allowNoteGuardOverride: true is passed. [INFO] remains read-visible metadata and does not block.

Example blocking error:

Write blocked — target range intersects cell notes marked as write guards:
Sheet1!B1 header note [FORMULA] Do not write static values here.
Pass allowNoteGuardOverride: true only after confirming the note is safe to override.

Response

{ "updatedCells": 12 }

// With protection warnings (non-blocking):
{ "updatedCells": 12, "_warnings": ["⚠ Target range has a user-set protection warning."] }

// With explicit note guard override:
{ "updatedCells": 12, "_warnings": ["⚠ Overriding note guard at Sheet1!B1 header note [FORMULA]: Do not write static values here"] }

Formula overwrite blocks

If write_range targets existing formula cells, the write is blocked before any mutation. The error includes the affected addresses plus a preview for each formula cell:

write_range blocked because the target range contains formula cells (1): Sheet1!J1.
Formula overwrite preview: Sheet1!J1 existing formula "=ARRAYFORMULA(...UPPER(...))" -> proposed formula "=ARRAYFORMULA(...LOWER(...))" (replace "UPP" with "LOW").
Pass allowFormulaOverwrite: true only after confirming the existing formula vs proposed value diff is intentional.

Use the preview to confirm formula maintenance edits. Do not pass allowFormulaOverwrite just to silence the guard.

ARRAYFORMULA column blocks

If the target range overlaps a column where the schema scan found an ARRAYFORMULA anchor in rows 1-25, the write is blocked before mutation. The error names the generated columns, the anchor cells, and the safest range change:

Write blocked — target range Data!D2:N100 overlaps ARRAYFORMULA-owned column(s): E (anchor Data!E1), F (anchor Data!F1), G (anchor Data!G1), J (anchor Data!J1).
Guard reason: writing static values into generated column(s) E, F, G, J can replace spill output and break downstream formulas.
Safest minimal write: split or narrow the write range to exclude column(s) E, F, G, J.
Pass allowFormulaOverwrite: true only after confirming this formula/spill overwrite is intentional.

Cache invalidation

After a successful write, the entire sheet_snapshots cache for the spreadsheet is cleared so subsequent read_range and analyze_range calls see fresh data.

Mid-operation protection conflict

If a 403 or protection-related error occurs during (not before) the write — meaning the sheet's protection changed between the guard check and the actual API call — the error returned is:

Write failed — a protected range conflict was detected mid-operation.
The sheet's protection settings may have changed.
Call read_range to re-inspect the current schema before retrying.

append_rows

Appends rows to the bottom of an existing data table without overwriting.

Tip:

Always use the default INSERT_ROWS. OVERWRITE replaces cells at the detected table bottom rather than inserting new rows — which can destroy existing data below the table.

Note

append_rows also has allowFormulaOverwrite because the append target might land in an ARRAYFORMULA spill zone. The guard is the same as write_range.

clear_range

Clears all values in a range while preserving formatting, data validation, and conditional formatting rules.

Note

clear_range only removes cell values. To also remove formatting, use batch_update_sheet with a repeatCell request and fields: "userEnteredFormat".

format_cells

Applies visual formatting to a cell range using zero-based grid indexes (not A1 notation).

Index mapping

Row 1 (header) = startRowIndex: 0, endRowIndex: 1. Column A = startColumnIndex: 0, Column B = startColumnIndex: 1.

Color format

Colors are RGB decimals 0–1, not hex:

{ "red": 0.35, "green": 0.62, "blue": 0.56 }   // #5A9E8F

numberFormat types

TEXT, NUMBER, PERCENT, CURRENCY, DATE, TIME, DATE_TIME, SCIENTIFIC

// Currency with 2 decimal places
"numberFormat": { "type": "CURRENCY", "pattern": "$#,##0.00" }

// Date
"numberFormat": { "type": "DATE", "pattern": "yyyy-MM-dd" }

Full example — bold green header row

{
  "spreadsheetId": "...",
  "sheetId": 0,
  "startRowIndex": 0,
  "endRowIndex": 1,
  "startColumnIndex": 0,
  "endColumnIndex": 4,
  "bold": true,
  "backgroundColor": { "red": 0.35, "green": 0.62, "blue": 0.56 },
  "textColor": { "red": 1, "green": 1, "blue": 1 }
}