developer guide
Spreadsheet Formulas in a JavaScript Data Grid
Typing an expression into a cell calculates it against that row, so someone can work out a figure where they are looking rather than in another window. The expression is evaluated by the grid rather than by the page, so a pasted formula cannot reach anything outside it.
Developer guide › Editing › Spreadsheet Formulas in a JavaScript Data Grid
Formulas
A leading = in a numeric cell is a formula. Type
=quantity * unitPrice and the grid stores 119.88.
What a user can type
=5 + 5
=quantity * unitPrice // by field name
=[Unit Price] * 1.2 // by title, when it has spaces
=ROUND(quantity * unitPrice, 2)
=IF(quantity > 10, "bulk", "single")
=SUM(readings) // an array property on the row
=MAX(readings) - MIN(readings)References name columns, not cells. A spreadsheet can say A1
because its rows do not move. A grid sorts, filters, groups, pages and virtualises, so the row
at position 1 is a different row a moment later and a formula written against it would silently
change meaning. quantity * price means the same thing wherever the row goes.
No eval, no new Function. This is text a
user typed into a cell. Handing it to the JavaScript engine would let anyone who can
edit a cell read your cookies, call your API with your credentials, or post the grid's contents
anywhere. It is a hand-written tokeniser and recursive-descent parser, and the only callable
things are the built-in functions: constructor, globalThis and
constructor.constructor("return 1")() all simply fail to resolve.
The result is stored, not the expression. A formula is a way of
entering a value, exactly like 1,200 or (50) or
12%. It commits as one undo step, fires one cell:changed, and passes
through the column's own validation.
The result is stored, not the formula. The expression is evaluated once, at the moment you commit it, and what lands in the cell is a value like any other, so it does not recalculate when a cell it referred to changes later. For a value that must stay in step with its inputs, use a computed column, which is re-evaluated whenever its dependencies move.
Adding your own functions
createGrid(el, {
formulaFunctions: {
MARGIN: ([revenue, cost]) => (revenue - cost) / revenue,
BAND: ([value]) => (value > 1000 ? 'A' : 'B'),
},
});
// Or evaluate one yourself, anywhere.
import { evaluateFormula } from '@toclocoinc/lattice-grid';
const r = evaluateFormula('=a * b', { data: { a: 6, b: 7 } });
r.ok ? r.value : r.error; // 42A formula that cannot be read rejects the edit and the cell keeps its old value, the same as any other unparseable text. Failures are returned rather than thrown: this runs on the commit path, where an exception would abandon the commit half-done.
Bare arithmetic is deliberately not a formula. 2-1 is a
plausible product code and 1/2 a plausible date. A reader that evaluated either on
a guess would have to guess wrong sometimes, and the wrong answer is not a visible error but a
plausible number: 2*3 stored as 23 looks like data. Arithmetic
without a leading = is refused outright, so the cell keeps what it had rather than
taking a number nobody typed.