# Sheet API
The sheet app (`deft/sheet`) is a reactive spreadsheet: a workbook is a grid
of cells, every `=` cell is a deft expression evaluated in the workbook's
**calculation sandbox**, and cells reference each other as plain deft
variables (`$a1`). The app is split into a headless model (`model.dft`) and
a Lotus 1-2-3-style panel (`sheet.dft`); the model also runs alone as the
headless daemon (`src/run.dft`).
The `[sheets/…]` API is the **user-level** programmatic workbook surface —
one vocabulary shared by `prog.dft` defs, cell formulas, scripts and
external agents. `calc-open` refers these functions into the calculation
sandbox when a workbook opens, so they are available without sourcing
anything (the bundled `sheet-fns` formula library is a separate concern).
They are ordinary model functions — the panel, the RPC methods and the test
suite call the same code. The `[sheet/…]` spellings remain as legacy
aliases of the same functions.
For driving a *running* workbook from another runtime (a chart app, an
agent, a script), use the `@rpc` methods in the same namespace — see
[Driving from outside](#driving-from-outside).
## Function Reference
### Cells
| Function | Args | Returns | Purpose |
|---|---|---|---|
| `sheets/set-cell` | `cell` `src` | status string / `%Err` | Set one cell from typed input |
| `sheets/set-cells` | `%{addr src …}` | count / `%Err` | Bulk set, one undo step |
| `sheets/fill` | `cell` `rows` | range / `%Err` | Spill a block of values from an anchor |
| `sheets/append-row` | `values` `?col` | address / `%Err` | Append below the column's last used cell |
| `sheets/cell-value` | `cell` | value / nil / `%Err` | Computed value (nil when empty) |
| `sheets/cell-meta` | `cell` | map / `%Err` | Full cell metadata |
| `sheets/cells` | `?spec` | list of addresses | Stored cells, row-major |
| `sheets/clear-cell` | `cell` | bool / `%Err` | Remove one cell |
| `sheets/clear-range` | `spec` | count / `%Err` | Remove every stored cell in a range |
### Formatting
| Function | Args | Returns | Purpose |
|---|---|---|---|
| `sheets/set-format` | `spec` `patch` | count / `%Err` | Number format / alignment on stored cells |
| `sheets/set-column-format` | `col` `patch` | true / `%Err` | Whole-column overlay (covers empty cells) |
| `sheets/set-row-format` | `row` `patch` | true / `%Err` | Whole-row overlay (covers empty cells) |
| `sheets/set-column-width` | `col` `w` | width / `%Err` | Column display width (clamped 3..40) |
### Cell settings
| Function | Args | Returns | Purpose |
|---|---|---|---|
| `sheets/set-every` | `cell` `secs` | bool / `%Err` | `# every N` re-evaluation interval (0 clears) |
| `sheets/set-watch` | `cell` `src` | bool / `%Err` | Watch expression (nil/`""` clears) |
| `sheets/set-publish` | `cell` `on` `?name` | bool / `%Err` | Include in the published cells feed; optional `name` overrides the feed key |
### Tables
| Function | Args | Returns | Purpose |
|---|---|---|---|
| `sheets/table` | `name` | Table / `%Err` | The live `%Table` behind a spilled table |
| `sheets/table-rows` / `sheets/table-cols` | `name` | rows / names | Current rows / header names |
| `sheets/table-transpose` / `sheets/table-hide-cols` / `sheets/table-filter` | `name` … | rows / `%Err` | Pure derives; filter spec = fn / `%{:col :op :value}` triples / infix string `"$age > 30"` |
| `sheets/table-view` | `name` `%{:filter :hide :transpose}` | rows / `%Err` | Spec derive, fixed pipeline filter → hide → transpose |
| `sheets/table-spill` | `name` `cell` `src` | status / `%Err` | Spill a derived table as a LIVE expression (re-derives on source refresh) |
| `sheets/table-refresh` / `sheets/table-refresh-all` | `?name` | bool / count | Re-run table anchors (async cells re-query) |
| `sheets/table-owner` | `name` | anchor id | The move-proof anchor behind a table's auto-name |
### Named ranges, workbook and program file
| Function | Args | Returns | Purpose |
|---|---|---|---|
| `sheets/get-range` | `spec` | rows / `%Err` | Values of a named or A1 range |
| `sheets/named-ranges` | | list of maps | Defined names with their `:range` and `:topic` |
| `sheets/define-range` | `name` `range` | address / `%Err` | Define (or redefine) a named range |
| `sheets/remove-range` | `name` | bool | Remove a named range |
| `sheets/save` | | true | Insert a workbook snapshot (like Ctrl+S) |
| `sheets/workbook-name` | | string | The open workbook (`"main"`, …) |
| `sheets/prog-file` / `sheets/prog-source` | | path / text | The workbook's program file |
| `sheets/write-prog` / `sheets/reload-prog` | `src` | status | Replace / re-evaluate `prog.dft` live |
## Typed Input
`sheets/set-cell` takes the same source syntax as typing into the grid:
| Source | Meaning |
|---|---|
| `"=($a1 * 2)"` | Formula — evaluated in the calc sandbox |
| `"42"` | Number (stored as a formula cell) |
| `"hello"` | Text literal |
| `"==literal"` | Force the string form |
Addresses are A1 strings (`"B2"`) or keywords (`:b2`), any case. `spec`
arguments accept a single cell, an A1 range (`"B2:D4"`), or a named range.
Non-string `src` values are coerced the same way — `[sheets/set-cell "a1" 10]`
stores the number 10.
When the call itself is deft source (`prog.dft`, a script, another app),
escape a literal `$` inside the source string — `"=(\$a1 * 2)"` — or use
bare A1 refs (`"=(A1 * 2)"`), which need no escape. The examples below use
bare refs; named ranges must keep the `\$NAME` spelling.
`sheets/set-cells` and `sheets/fill` differ in how they treat values:
- `set-cells` — a map of address → source. **String values are typed
input** (so `"=($a1*2)"` is a formula); numbers, booleans and maps become
literals; nil leaves the cell empty. The whole batch is one undo step.
- `fill` — **values, not sources**. A list of rows spills as a matrix, a
flat list as one row, a scalar as one cell. Strings stay strings and a
leading `=` is escaped, so data can never accidentally evaluate.
[sheets/set-cells %{ "A1" "Item" "B1" "Cost" }]
[sheets/fill "A2" @{ @{"rent" 1200} @{"food" 400} }]
[sheets/append-row @{"total" [sheets/cell-value "B4"]}] # log rows
## Reading and Metadata
`sheets/cell-value` returns the computed value, nil for an empty cell, and
`%Err{:message}` when the cell is in error — pair it with the `iferror`
helper from `sheet-fns` or check `[err? …]`.
`sheets/cell-meta` is the introspection entry point:
| Field | Meaning |
|---|---|
| `:cell` | Canonical address (`"B2"`) |
| `:src` | Stored source, `=` included |
| `:kind` | `:expr` or `:lit` |
| `:value` / `:err` | Current value / error message |
| `:fmt` | Effective `%{:align :num}` (cell → row → column) |
| `:every` / `:watch` / `:pub` / `:pub-name` | Cell settings |
| `:table` / `:tname` | Table anchor flag / auto-registered name |
| `:refs` | Direct cell references, as addresses |
| `:names` | Named ranges the formula references |
Empty cells return a minimal map carrying the effective `:fmt` (from the
column/row overlays), so format inspection works before a cell exists.
set m [sheets/cell-meta "B4"] echo "B4 is $m~src -> $m~value (format $m~fmt~num)" [sheets/cells "B2:B10"] # => @{ "B2" "B4" "B7" }
## Formatting
Patch maps take two keys:
- `:num` — a printf-style spec run through deft's `format` at draw time:
`%.2f`, `%05d`, `%+.1f`. Stored values stay raw — recalc, `get-range` and
publishing all see the unformatted number.
- `:align` — `:left`, `:center` or `:right`.
`:default` clears a key. Resolution is per property: **cell → row overlay →
column overlay → type default**, so a cell that sets only alignment still
inherits the column's number format.
`sheets/set-format` patches the **existing** cells in a range; empty cells
carry no metadata. `sheets/set-column-format` / `sheets/set-row-format`
write the overlays, which cover empty and future cells. Rows are 1-based
spreadsheet numbering; columns are letters.
[sheets/set-format "B2:B4" %{ :num "%.2f" :align :right }] [sheets/set-column-format "C" %{ :num "%.0f" }] # covers C's empty cells [sheets/set-row-format 1 %{ :align :center }] [sheets/set-column-width "B" 12]
## Cell Settings
- `sheets/set-every` — re-evaluate the cell every N seconds (the same thing
as typing a `# every N` suffix; 0 clears). Sync deft only.
- `sheets/set-watch` — an expression that runs in the calc sandbox whenever
the cell's live value changes, with `$new` and `$old` bound. Errors
surface on the status line, never in the cell.
- `sheets/set-publish` — include the value in the workbook's
`sheet//cells` pub/sub feed. An optional trailing `name`
becomes the feed key (default: the cell address) so chart series read
`rss` instead of `H14`.
## Named Ranges
`sheets/define-range` registers a name over an A1 range; formulas reference
it as `$NAME`, and subscribers can follow
`sheet//range/` for change events. `sheets/named-ranges`
lists every defined name with its canonical `:range` and `:topic`.
[sheets/define-range "prices" "B2:B10"] [sheets/set-cell "C2" "=[avg \$prices]"] [sheets/get-range "prices"] # rows of values [sheets/remove-range "prices"]
## Deferred Writes
A write requested **while a recalculation pass is running** — a cell
formula or a watch calling `[sheets/set-cell …]` — is queued and applied
when the outermost pass ends. A mid-pass store would race the pass's
topological order, so this is the safe point to mutate.
Deferred calls return a status with a `(queued)` suffix, and reads in the
same formula still see the old value (the write has not happened yet).
Outside a pass every call is immediate. A write loop that keeps
re-scheduling itself is dropped after 1000 steps with a status message, so
a buggy watch cannot hang the app.
# A formula may seed another cell; the write lands after this pass. [sheets/set-cell "J1" "=[sheets/set-cell \"J2\" \"=7\"]"]
## Errors and Undo
Invalid addresses, format patches and intervals return
`%Err{:message}` instead of raising, so prog code can branch on them:
set res [sheets/set-format "B2" %{ :num "nope" }] if [err? $res] { [log/error $res~message] }
Every call is **one undo step** — bulk `set-cells`, `fill` and
`clear-range` included — so a scripted mutation reverts as a unit with
Ctrl+Z.
## Driving from Outside
The same namespace exposes `@rpc` methods, so another runtime can drive a
live workbook over the standard RPC path (see [RPC](../02-Platform/rpc)).
Method names are slash-free (AI/MCP tool names are `runtime__method` and
must match `[A-Za-z0-9_-]`), and args travel as source text — strings,
numbers, booleans, nil and keywords. Collection-shaped methods take
flat/CSV forms over RPC and the rich forms in-process:
| Method | Purpose |
|---|---|
| `sheet__set-cell` / `sheet__set-cells` | Set a cell / cells from typed input (pairs: `"A1" src "B1" src`) |
| `sheet__cell-value` / `sheet__cell-meta` / `sheet__cells` | Read a value / full metadata / stored addresses |
| `sheet__clear-cell-at` / `sheet__clear-range` | Clear a cell / a range |
| `sheet__fill` / `sheet__append-row` | Write literal value rows (`fill "A2" "rent" "food"`) |
| `sheet__set-format` / `sheet__set-column-format` / `sheet__set-row-format` / `sheet__set-column-width` | Formatting (flat `num`/`align` args) |
| `sheet__set-every` / `sheet__set-watch` / `sheet__set-publish` | Cell settings |
| `sheet__get-range` | Values of a named/A1 range |
| `sheet__define-range` / `sheet__remove-range` / `sheet__named-ranges` | Named ranges |
| `sheet__table` / `sheet__table-rows` / `sheet__table-cols` / `sheet__table-owner` | Live tables by name |
| `sheet__table-transpose` / `sheet__table-hide-cols` / `sheet__table-filter` / `sheet__table-view` | Table derives (`"$age > 30"` expr strings, `"a,b"` col lists) |
| `sheet__table-spill` / `sheet__table-refresh` / `sheet__table-refresh-all` | Spill / re-run tables |
| `sheet__workbook-name` / `sheet__save` | Workbook identity / snapshot |
| `sheet__prog-file` / `sheet__prog-source` / `sheet__write-prog` / `sheet__reload-prog` | Program file management |
await [rpc "sheet" "set-cell" "B2" "=(A1 * 2)"] set rows [await [rpc "sheet" "get-range" "prices"]] await [rpc "sheet" "set-format" "B2:B4" "%.2f" :right] set adults [await [rpc "sheet" "table-filter" "USERS" "$age > 30"]]
A headless workbook
(`deft packages/sheet/src/run.dft [workbook]`) runs the full model — async
cells, intervals, snapshots — and exposes the same RPC methods plus a
debug repl on `127.0.0.1:9312`.