Spreadsheet Lens

A spreadsheet as a lens [Foster et al. 2007] of the containing notebook. The cell at B3 is a variable literally named B3; so the view is a pure function of the module. Delete the sheet() call and the underling dataflow keeps working. Edit cells in either the sheet or the enclosing notebook.

Getting started

import {sheet} from '@tomlarkworthy/spreadsheet'

widget = sheet(runtime, {
  invalidation,     // required for teardown
  module,           // REQUIRED — the module whose scope the sheet projects
  format: {},       // { B3: "0.00" } — presentation state, owned by this cell
  size: {},         // { cols: { B: 140 }, rows: { "3": 40 } } — explicit sizes; unnamed = auto
  persist: true     // rewrite own source when format or size changes
})

Keys. Arrows move; Shift+arrows select a range. Enter or F2 edits in place; typing starts a new value. While editing, = then an arrow picks a reference, Shift+arrow widens it to a range, and a click on the grid does the same. Tab commits to the right, Enter to the row below. Delete clears the selection; Cmd+C / X / V copy, cut and paste with relative addressing; Cmd+Z and Shift+Cmd+Z undo and redo. Drag the handle at the selection corner to fill. Drag a column or row divider to size it; double-click it to return to auto.

How it works

Observable cell syntax already is formula-bar syntax: what a spreadsheet user types after = is the cell body, and the name comes from where they clicked. Recalculation is the reactive runtime, a circular reference is RuntimeError: circular definition, and a reference to a blank cell is an implicit variable ("A5 is not defined") that is taken over the moment A5 is defined — so formulas can be typed in any order.

Names as well as positions. Split definition from placement:

taxRate = 0.2        // definition — the semantic name
B1 = taxRate         // placement — "this lives at B1"

The sheet detects a placement cell (a positional name whose body is a bare reference to a non-positional name), renders the target's value at B1 and labels it. = B1 * 12 is then relative and shifts on copy; = taxRate * 12 is absolute and never shifts. Placement also puts any cell on the grid: D5 = revenuePlot.

Pinning a coordinate. $ is legal in a JS identifier, so $A$1, $A1 and A$1 are names too. The sheet defines each one it sees as an alias to the plain address, and paste leaves a pinned component alone — Excel's semantics, still with nothing stored outside the names.

Ranges. A4:C4 is not JavaScript, so the bar expands it before parsing into [A4, B4, C4] — an array of ordinary references, every one a real dependency — and reads a full-rectangle array literal back as a range for display. d3.sum(A4:C4) is stored as d3.sum([A4, B4, C4]). Written without spaces round the colon; x ? a1 : b2 stays a ternary.

Text. A bare name that resolves to nothing (Total), or words that do not parse (Total revenue), is stored as a string literal; the bar shows the quotes. A broken formula stays broken.

Overflow. Content spills into empty neighbours and clips at the first occupied cell — Excel's rule generalised from text to DOM nodes, done in CSS with nothing stored. A column is auto (the widest text value, capped) unless size names it; DOM values spill rather than widen.

Sparse. Only defined cells are variables and only defined cells have DOM. An empty sheet is zero of both; the grid is painted, not built.