Sales Dashboard with Sparkline & Cross-Sheet Formulas

This demo showcases a two-sheet sales dashboard built with GridSheet.

The first sheet holds monthly metrics with sparkline charts rendered via a custom policy. The second sheet pulls KPIs from the first using cross-sheet formulas and uses ARRAYFORMULA with IF to spill status and gap values across multiple rows.

Implementation Guide

Rendering Sparklines with renderSheet

The renderSheet method on PolicyMixinType is called when a cell's resolved value is a Sheet object — that is, when a formula that returns a range (e.g. =C1:H1) has been evaluated.

By setting a range formula on a cell and implementing renderSheet on that cell's policy, you can visualize multiple columns of data within a single cell.

const SparklinePolicyMixin: PolicyMixinType = {
  renderSheet({ value }: RenderProps<UserSheet>) {
    const matrix = toValueMatrix(value);
    const values: number[] = matrix.flatMap(
      (row) => row.filter((v): v is number => typeof v === 'number')
    );
    return <Line data={...} options={...} />;
  },
};

toValueMatrix() converts the cells held by the UserSheet into a 2D array of plain values. Using a type guard in the filter safely extracts numbers only.

Pairing a Range Formula with a Policy

On the cell definition side, set a range formula such as =C1:H1 on column B cells and assign a custom policy to that column.

matrices: {
  A1: [
    ['Revenue', '=C1:H1', 42, 51, 48, 67, 73, 89, '=ROUND((H1-C1)/C1,3)'],
    ...
  ],
},
cells: {
  B: { policy: 'sparkline', style: { backgroundColor: '#fafbfc' } },
}

The resolved result of =C1:H1 is passed to renderSheet as a Sheet, letting you build a Chart.js data source directly from it.

Sharing the Same book Across Multiple GridSheet Instances

The book returned by useSpellbook can be passed to multiple GridSheet components. Sharing the same book enables cross-sheet formula references between them.

const book = useSpellbook({ policies: { sparkline: new Policy({ mixins: [...] }) } });
 
<GridSheet book={book} sheetName={sheetName1} initialCells={...} />
<GridSheet book={book} sheetName={sheetName2} initialCells={...} />

By giving each component a different sheetName, multiple sheets coexist inside the same book.

Cross-Sheet Formula References

To reference data from the metrics sheet inside the dashboard sheet, use the SheetName!Range syntax.

=SUM(metrics!C1:H1)
=ROUND(AVERAGE(metrics!C3:H3),1)

When sheetName changes, these references update automatically. Try editing the sheet name inputs in the demo to see this in action.

Spilling with ARRAYFORMULA + IF

The dashboard sheet uses ARRAYFORMULA to compute values that spill across multiple rows from a single formula cell.

=ARRAYFORMULA(IF(B1:B5>=C1:C5,"✓ On Track","✗ Behind"))
=ARRAYFORMULA(B1:B5-C1:C5)

The first formula compares each KPI value against its target and spills a status label into D1:D5. The second computes the gap between value and target, spilling into E1:E5. Only the origin cell (D1, E1) holds the formula — the remaining cells are filled automatically.

Bulk Data Entry with matrices in buildInitialCells

Use the matrices key to set data in matrix form. The key is the address of the top-left cell.

buildInitialCells({
  matrices: {
    A1: [
      ['Revenue', '=C1:H1', 42, 51, 48, 67, 73, 89, '=ROUND((H1-C1)/C1,3)'],
      ['New Users', '=C2:H2', 120, 185, 210, 340, 295, 410, '=ROUND((H2-C2)/C2,3)'],
    ],
  },
  cells: {
    defaultCol: { width: 55 },
    defaultRow: { height: 50 },
    'C:H': { style: numStyle, alignItems: 'center' },
  },
})

defaultCol / defaultRow set the default width and height for all columns and rows. A range key like 'C:H' applies styles to multiple columns at once.