Docs / Writing cells and formulas

Writing cells and formulas.

Four tools cover direct writes. Each one lands as a pending batch, returns a readback of what it wrote, and never touches the file.

Conventions

• Formulas, not computed numbers. A derived value is written as a formula starting with =, so the model stays live when inputs change.
• Raw numbers, not formatted strings. Write 1250, not "$1,250". Formatting is set_format's job.
• A1 references point at final positions. Formulas inside a block reference cells where they will end up, not offsets within the payload.
• Sheet names are case-sensitive. Writing to a sheet that does not exist creates it.

set_cell: one cell

Pass value for a literal or formula for a formula, not both.

{ "path": "~/models/forecast.xlsx", "sheet": "Model", "cell": "F42", "formula": "=SUM(F5:F41)" }

{ "path": "~/models/forecast.xlsx", "sheet": "Assumptions", "cell": "B7", "value": 0.21 }

null as the value clears the cell's content and keeps its formatting.

set_range: a block

A 2D array starting at top_left: outer array is rows, inner is cells. Strings beginning with = are formulas. null or "" leaves the existing cell as it is, so a sparse payload only overwrites what it names.

{
  "path": "~/models/forecast.xlsx",
  "sheet": "Model",
  "top_left": "A44",
  "values": [
    ["Contingency", "=B42*0.05", "=C42*0.05", "=D42*0.05"],
    ["Total with contingency", "=B42+B44", "=C42+C44", "=D42+D44"]
  ]
}

copy_range: Excel copy and paste

Copies a rectangle to a destination with Excel semantics: relative references shift by the offset, $ anchors stay put, and formatting travels with the cells. Prefer it over re-typing shifted formulas; it cannot mis-shift.

{
  "path": "~/models/forecast.xlsx",
  "sheet": "Model",
  "source": "E5:E120",
  "dest": "F5",
  "mode": "all"
}

mode is all (default: formulas, values and formats), values (evaluated values only, to freeze a calculation as hardcodes) or formats (formatting only). dest_sheet copies to another sheet; sheet-qualified references keep pointing at their original sheets. The reply adds dest_range and copied_cells.

Extending a model by one period is the canonical use: copy the last column to the next one, then fix the header.

clear_range: delete content

Empties values and formulas in a range. Formatting is preserved. Use it to delete content rather than overwriting with blanks.

{ "path": "~/models/forecast.xlsx", "sheet": "Scratch", "range": "A1:K40" }

Reading the response

Every write returns the same shape:

{
  "ok": true,
  "batch_id": "b4",
  "write_check": "ok",
  "cells": [
    { "sheet": "Model", "cell": "B44", "value": 1562.5, "formula": "=B42*0.05", "text": "1,563" }
  ],
  "row_map": [ { "sheet": "Model", "row": 44, "label": "Contingency" } ],
  "pending_batches": 4,
  "review_url": "http://127.0.0.1:51234/review?path=…&token=…",
  "note": "Changes are pending in memory. …"
}

• write_check is ok when every cell the call meant to write now holds a value or formula, partial when some did not land, FAILED when none did. A FAILED write sets ok: false.
• cells is the evaluated readback, up to 200 cells, so the agent can see a formula's result immediately and catch a reference pointing at the wrong row.
• row_map lists the label found in the first columns of each touched row, which is how the agent confirms it wrote "Contingency" on the Contingency row.

When the edit is loop-shaped

"For every row where margin is negative, …", "repeat this formula across 40 columns", or anything that depends on reading cells first is a job for run_script: one call, one batch, addresses computed in code.

Found a mistake? Open an issue or email support@gridpath.dev.