Formula sheet that recomputes only affected cells, in order
A pricing dashboard is built like a spreadsheet: some cells hold inputs (exchange rates, quantities), and other cells are formulas computed from them. Some formulas are expensive (they run a pricing model), so the engine must do the least work possible: when an input changes, only the formulas that are actually affected run, each one at most once, and only after all of its own inputs are up to date.
Implement a class ComputeSheet. Formulas are JavaScript functions, so there is nothing to parse.
const sheet = new ComputeSheet();
sheet.setValue(id, value); // store a constant
sheet.setValues({ [id]: value, ... }); // store several constants as one edit
sheet.setFormula(id, deps, compute); // store a formula
sheet.get(id); // => the cell's current value
undefined.setValue(id, value) stores a constant (any value). If the cell held a formula, the formula is replaced and no longer runs.setValues(entries) stores every own key of entries as a constant, as one edit.setFormula(id, deps, compute) stores a formula. deps is an array of cell ids. The formula’s value is compute(...deps.map(get)): the current values of its dependencies, in the order listed. A dependency can be listed more than once; it is passed once per listing.setFormula computes the new formula right away (exactly one compute call), then updates everything that depends on the cell.get(id) only reads a stored value. It never calls compute.Every call to setValue, setValues or setFormula is one edit. After an edit, every cell must hold its correct value, and the engine must follow these rules:
Object.is. Setting a cell to the value it already has is not a change.D uses B and C, and both use A, changing A runs B and C once each and then D once.setFormula would make a cell depend on itself, directly (deps contains id) or through other formulas, it throws an Error whose message contains the word cycle.compute is not called, and the cell keeps its previous constant or formula, which keeps working.const sheet = new ComputeSheet();
sheet.setValues({ qty: 3, price: 20, fee: 5 });
sheet.setFormula('net', ['qty', 'price'], (q, p) => q * p); // 60
sheet.setFormula('gross', ['net', 'fee'], (n, f) => n + f); // 65
sheet.setFormula('band', ['gross'], (g) => (g > 100 ? 'high' : 'low'));
sheet.get('band'); // => 'low'
sheet.setValue('fee', 6); // runs gross (66) and band, which stays 'low'
sheet.setValue('fee', 6); // same value: no formula runs
sheet.setValue('qty', 10); // runs net, gross, band once each
sheet.get('band'); // => 'high'
sheet.setFormula('qty', ['band'], () => 1); // throws Error('... cycle ...')
sheet.get('qty'); // => 10 (unchanged)
Sheets in the tests have up to a few thousand cells, including long chains where each formula uses the previous one.