Spreadsheet engine with cell references and live formulas
Implement a tiny spreadsheet engine as a class named Sheet. Cells hold either a number or a formula that can reference other cells, and reading a cell always returns its up-to-date value.
const sheet = new Sheet();
sheet.set(cellId, input); // store a raw input string
sheet.get(cellId); // => number, the computed value
sheet.raw(cellId); // => string, the input last stored ('' if none)
A cell id is one or more letters followed by one or more digits, for example A1, c7, or AB12. Ids are case-insensitive everywhere: a1 and A1 are the same cell, in method calls and inside formulas.
'' (empty string) clears the cell.= is a number literal such as '42', '-7.5', or '0.25'. Its value is Number(input).= is a formula.raw(cellId) returns the exact string passed to the last set for that cell, or '' if it was never set or was cleared.
=, a formula is a list of operands joined by the binary operators +, -, *, and /.3, 2.5) or a cell id.=2+3*4 is (2 + 3) * 4 = 20.=B2 or =7.0.Infinity (or NaN for 0 / 0).eval or new Function.get(cellId) returns 0 for an empty cell.set('A1', '5'), every formula that depends on A1, directly or through other cells, uses 5.set accepts any formula, even one that creates a cycle. If computing a cell needs a cell that (directly or indirectly) needs itself, get throws an Error whose message contains the word circular. Cells that do not touch the cycle keep working, and fixing an input in the cycle makes the cells readable again.D1 = B1 + C1 where both use A1) is not a cycle.const sheet = new Sheet();
sheet.set('A1', '4');
sheet.set('B1', '=A1 * 2 + 1'); // (4 * 2) + 1
sheet.get('B1'); // => 9
sheet.set('C1', '=2 + B1 * 10'); // (2 + 9) * 10
sheet.get('c1'); // => 110
sheet.set('A1', '1');
sheet.get('C1'); // => 50 ((1 * 2 + 1) + 2) * 10
sheet.get('Z9'); // => 0
sheet.raw('b1'); // => '=A1 * 2 + 1'
sheet.set('X1', '=Y1 + 1');
sheet.set('Y1', '=X1');
sheet.get('X1'); // throws Error('Circular reference ...')
sheet.set('Y1', '10');
sheet.get('X1'); // => 11