demo D128
Formula entry
A spreadsheet formula typed into a cell, and the evaluator reach limit
formulaFunctions
The configuration
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.13.0/lattice-grid.min.css">
<script src="https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.13.0/lattice-grid.min.js"></script>
<div id="grid" style="height: 540px"></div>
<script>
const grid = LatticeGrid.createGrid(document.getElementById('grid'), {
rowKey: 'id',
edit: true,
selection: 'multiple',
// Documented as extra functions a formula may call. In 1.4.0 the number
// type's parse is not handed it, so =VAT(...) is refused like any name that
// is not in the built-in library.
formulaFunctions: { VAT: (args) => Number(args[0]) * 1.2 },
columns: [
{ field: 'description', title: 'Description', layout: { flex: 1, min: 160, pin: 'start' } },
{ field: 'quantity', title: 'Qty', type: 'number', edit: true, layout: { width: 100 } },
{ field: 'unitPrice', title: 'Unit price', type: 'number', edit: true,
format: { style: 'currency', currency: 'GBP' }, layout: { width: 140 } },
{ field: 'discount', title: 'Discount', type: 'number', edit: true,
format: { style: 'percent', decimals: 0 }, layout: { width: 140 } },
{ id: 'lineNet', title: 'Line net', type: 'number',
format: { style: 'currency', currency: 'GBP' }, layout: { width: 130 },
value: {
deps: ['quantity', 'unitPrice', 'discount'],
compute: (d) => d.quantity * d.unitPrice * (1 - d.discount),
} },
{ field: 'total', title: 'Total', type: 'number',
format: { style: 'currency', currency: 'GBP' },
layout: { width: 140, pin: 'end' }, total: 'sum',
// The text editor on a number column, on purpose. The formula path lives
// in the number type's parse, and it only sees what the editor produces.
// The default number editor produces a number, so a formula never
// survives to reach the evaluator; the text editor hands text through
// untouched, so a column that accepts formulas asks for it here.
edit: { enabled: true, editor: 'text' } },
],
rows, // invoice lines: description, region, quantity, unitPrice, discount, total (empty)
});
// The number type's own parse is the function the commit path calls with
// whatever the editor produced, so calling it directly is the real evaluator
// on the real column. A formula is text a user typed, so its reach is the
// security boundary: there is no eval and no new Function behind it, only
// arithmetic, a closed list of functions and the row the cell stands in.
const column = grid.columns.all().find((c) => c.id === 'total');
const row = grid.rows.get(0);
const value = column.dataType.parse({
text: '=ROUND(quantity * unitPrice * (1 - discount), 2)',
value: null, data: row.data, row, column, grid,
});
</script>Typing a spreadsheet formula into a cell
Formula entry lets a cell hold =SUM(B2:B8) or =A3*1.2 instead of a static value, and the grid parses, evaluates, and re-evaluates it as the referenced cells change. A developer reaches for it wherever a grid stands in for a spreadsheet a user already expects to type into: budget lines, pricing sheets, a reconciliation view where a total needs to track its inputs rather than be pasted in by hand. Lattice Grid registers the supported operators and functions through formulaFunctions, so a column can expose only SUM, AVERAGE, and a handful of arithmetic operators, or a wider set, without the evaluator accepting an unbounded expression language. Entry works like inline editing: a formula is typed directly into the cell, committed with Enter, and re-parsed each time a dependency’s value is written. Lattice Grid tracks a bounded evaluator reach, so a formula referencing thousands of cells fails predictably at the configured limit rather than recursing until the tab stalls, which matters for a JavaScript data grid rendering tens of thousands of rows where a runaway calculation would otherwise block the main thread. A screen reader announces the evaluated result, not the formula text, so a non-sighted user hears the number a sighted user sees, with the formula itself available on request when the cell is focused for editing.
How do I add spreadsheet formulas to a grid cell?
Configure the column’s formulaFunctions with the operators and functions a formula is allowed to call, then enter a formula in a cell starting with =. Lattice Grid parses it, evaluates it against the referenced cells, and re-evaluates it whenever those cells change. An evaluator reach limit caps how many dependencies a single formula can walk, so a malformed or excessive reference fails at that boundary rather than degrading performance across the grid.