Safety Systems

sheets-mcp runs two sequential safety layers before every write — a formula check and a schema guard. Both are fail-closed: they block the write and return a structured error unless explicitly overridden. SQL tools have a separate, non-bypassable Postgres sandbox.

Layer 1 — Formula overwrite guard (assertNoFormulaOverwrite)

Runs on every write_range and transform_range call. Steps in order:

1. If allowFormulaOverwrite: true → skip this guard entirely
2. Fetch the target range with valueRenderOption: FORMULA
3. Scan every cell for values starting with =
4. If any formula cells found → block the write with a detailed error listing every affected cell address

write_range blocked because the target range contains formula cells (3):
Sheet1!B2, Sheet1!B3, Sheet1!B4
Pass allowFormulaOverwrite: true only if you intend to replace formulas with static values.

This is distinct from the ARRAYFORMULA spill guard (see Layer 2). This guard catches any formula — =SUM(), =VLOOKUP(), =IF(), etc.

Layer 2 — Schema guard (runPreWriteSchemaGuards)

Runs on every write tool after Layer 1. Uses the schema cache (__schema__ key in sheet_snapshots, TTL = 300 seconds) to avoid repeated API calls.

The schema is built from two cheap API calls:

• spreadsheets.get — fetches frozen rows, protected ranges, conditional format count, merges, banded ranges
• spreadsheets.values.get on row 2 only, valueRenderOption: FORMULA — detects ARRAYFORMULA anchors

Protected Range check

Compares the write footprint (computed from the actual values[][] array dimensions) against every protectedRange in the schema.

ARRAYFORMULA column guard

Checks if any column in the write footprint matches an ARRAYFORMULA anchor column (detected from row 2 formulas in the schema cache). If a match is found and allowFormulaOverwrite is false:

⚠ A target column contains an ARRAYFORMULA. Writing static values will
permanently destroy this formula. Pass allowFormulaOverwrite: true to confirm
you intend to replace the formula with static data.

Warning: The ARRAYFORMULA guard checks column overlap only, not cell overlap. If any cell in the target range shares a column with an ARRAYFORMULA anchor in row 2, the write is blocked. This is intentionally conservative.

What _schema looks like (returned by read_range)

read_range always returns the schema alongside values so agents can reason about the sheet structure before writing:

{
  "values": [["Name","Revenue","Status"], ["Acme","12000","Active"]],
  "_schema": {
    "layout": { "frozenRows": 1, "frozenColumns": 0 },
    "semantics": ["Has 2 conditional format rules"],
    "constraints": [],
    "protectedRanges": [
      { "range": { "startRowIndex": 0, "endRowIndex": 1 }, "warningOnly": false, "description": "Header row" }
    ],
    "arrayFormulaColumns": ["C"],
    "warnings": ["⚠ Column C contains ARRAYFORMULA — do not write to C2:C"],
    "mergedTitleRows": []
  }
}

Agents should check _schema.arrayFormulaColumns and _schema.protectedRanges before calling any write tool.

Layer 3 — SQL sandbox (analyze_range + transform_range)

These tools execute user-supplied SQL inside a hardened Postgres transaction. Three enforcement levels:

Regex guard (application layer)

Applied before the query reaches Postgres:

Note

The strip is designed not to break column names that contain blocked words. E.g. data ->>'Dropoff Time' is safe — the UNION/DELETE stripping targets full keywords, not substrings inside string literals.

Transaction sandbox (Postgres layer)

Every SQL execution uses a dedicated client from the pool and runs inside:

BEGIN READ ONLY;
SET LOCAL ROLE sheet_analyzer;
-- user query runs here
ROLLBACK;

sheet_analyzer is a restricted Postgres role with:

• No login privileges
• No access to account, user, session, apikey, sheet_snapshots, or write_history tables
• GRANT USAGE ON SCHEMA public only — enough to use Postgres built-in functions and types

Even if the regex guard is bypassed and a Data-Modifying CTE is constructed, Postgres enforces READ ONLY at the transaction level and rejects it.

Context safety clamp

LIMIT 1000 is automatically appended to every analyze_range query before execution. This prevents runaway result sets from consuming the agent's context window.

transform_range has a pre-execution cell count guard: if rows × columns > 50,000, the entire call is rejected before any SQL runs.

Response size clamps (read_range)

read_range applies output clamps after fetching data:

When truncation occurs, a second content item is added to the MCP response:

⚠ read_range: Truncated to 1000 rows (original: 14823).
Use a narrower A1 range or analyze_range with SQL for large datasets.

AX implication: If an agent receives a truncation warning from read_range, it must switch to analyze_range with a SQL query. Reading in batches by adjusting the A1 range is also valid.

Schema cache invalidation

The __schema__ cache (TTL = 300s) and all range caches are fully cleared from sheet_snapshots after every successful write (write_range, transform_range, clear_range, manage_sheets). This ensures that safety guards on the next write always see the current sheet structure.