Formula Chain

Jspreadsheet keeps a dependency graph of the formulas: each cell knows the formulas that read it, so a change recalculates exactly the affected cells, in dependency order. Version 13 exposes that graph through two worksheet methods.

What's new with Version 13

getDependents(cell): the formulas that read a cell, resolved the way a recalculation resolves them: direct references, ranges, full columns and rows, defined names, INDIRECT and references from other worksheets

getPrecedents(cell): the references a formula reads, without absolute markers and with defined names resolved to their range

Sparse storage: the row records are created on demand and reached through getCellRowObject(y); the records[y][x] matrix of version 12 no longer exists

Overview

Consider these cells: A1 = 10, B1 = =A1, C1 = =B1 * 10, D1 = =SUM(A1:B1)

Cell	Precedents	Dependents
A1	none	B1, D1
B1	A1	C1, D1
C1	B1	none
D1	A1:B1	none

When A1 changes to 20: B1 recalculates to 20, D1 to 22 and C1 to 200.

Documentation

Methods

Method	Description
`getDependents`	The formulas that read a cell: its direct dependents as cell names. A dependent on another worksheet is qualified with the worksheet name, quoted when needed, in the form the core writes into references (`'MY SHEET'!A1`). `getDependents(cell: String) => String[]`
`getPrecedents`	The references a formula reads, as written in the expression with the absolute markers removed: cells, ranges, full columns and rows, worksheet qualified references and 3D spans. A defined name is resolved to its range. An empty list for a cell without a formula. `getPrecedents(cell: String) => String[]`

Both methods read the graph the engine maintains, so they follow every edit: a formula replaced by a value leaves the dependents of the cells it read, a new formula joins them, and undo restores the previous graph.

Examples

Precedents and dependents of a cell

const worksheet = jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [10, '=A1', '=B1*10', '=SUM(A1:B1)'],
        ],
    }]
})[0];

worksheet.getDependents('A1'); // ['B1', 'D1']
worksheet.getPrecedents('D1'); // ['A1:B1']
worksheet.getPrecedents('A1'); // []

Every cell affected by a change

getDependents returns the direct dependents. Follow them to reach every formula a change recalculates:

const getAllDependents = function(worksheet, cell) {
    const result = [];
    const visited = new Set([cell]);
    const stack = [cell];
    while (stack.length) {
        const dependents = worksheet.getDependents(stack.pop());
        for (const name of dependents) {
            if (! visited.has(name)) {
                visited.add(name);
                result.push(name);
                // Dependents of another worksheet carry its name: resolve
                // the worksheet before following them
                if (name.indexOf('!') === -1) {
                    stack.push(name);
                }
            }
        }
    }
    return result;
}

getAllDependents(worksheet, 'A1'); // ['B1', 'D1', 'C1']

The graph of a worksheet

The two methods together give the edges of the dependency graph, ready for a diagram library or a trace precedents feature:

const edges = [];
const data = worksheet.getData();
for (let y = 0; y < data.length; y++) {
    for (let x = 0; x < data[y].length; x++) {
        const cell = jspreadsheet.helpers.getCellNameFromCoords(x, y);
        for (const reference of worksheet.getPrecedents(cell)) {
            edges.push({ from: reference, to: cell });
        }
    }
}