Events & Data Export: Inventory

This example shows how to react to what users do in a sheet: an activity log driven by onChange and the structural row/column events, a live TSV export of the sheet’s values, and per-row delete buttons rendered into empty cells with renderNull.

Try editing a stock count or price, clicking a red × button, or inserting/removing rows and columns from the context menu, and watch the log and TSV export update.

A1

fx

Activity Log:

No activity logged yet. Try editing a cell, deleting a product, or inserting rows via the context menu.

TSV Export (toValueMatrix):

Implementation Guide

📄 View Source Code

onChange and getLastChangedAddresses

onChange fires after every cell mutation. The sheet object passed to the callback exposes getLastChangedAddresses(), which returns the addresses (string[]) of the cells written by the most recent operation.

onChange: ({ sheet }: { sheet: UserSheet }) => {
  const addresses = sheet.getLastChangedAddresses();
  if (addresses.length > 0) {
    addActivityLog(`✏️ Cells changed: ${addresses.join(', ')} (onChange)`);
  }
  setTsv(convertToTSV(sheet));
},

Structural Event Callbacks

Besides onChange, useSpellbook accepts dedicated callbacks for row/column insertions and removals. Each receives the positions affected; x2c turns a column index into its letter.

onRemoveRows: ({ ys }) => {
  addActivityLog(`🗑️ Removed row(s) ${ys.join(', ')} (onRemoveRows)`);
},
onInsertRows: ({ y, numRows }) => {
  addActivityLog(`➕ Inserted ${numRows} row(s) at row ${y} (onInsertRows)`);
},
onRemoveCols: ({ xs }) => {
  addActivityLog(`🗑️ Removed column(s) ${xs.map(x2c).join(', ')} (onRemoveCols)`);
},
onInsertCols: ({ x, numCols }) => {
  addActivityLog(`➕ Inserted ${numCols} column(s) at ${x2c(x)} (onInsertCols)`);
},
onInit: ({ sheet }) => {
  addActivityLog('📦 Sheet initialized (onInit)');
  setTsv(convertToTSV(sheet));
},

Use these to build audit logs or to sync removed rows to a backend.

Exporting Sheet Data as TSV

toValueMatrix(sheet, { resolution: 'RESOLVED' }) evaluates formulas and returns a 2D array of plain values, which is easy to serialize. Pass resolution: 'RAW' instead to export the formulas themselves.

const convertToTSV = (sheet: UserSheet, evaluates: boolean = true): string => {
  if (!sheet) {
    return '';
  }
  const matrix = toValueMatrix(sheet, { resolution: evaluates ? 'RESOLVED' : 'RAW' });
  if (!matrix || matrix.length === 0) {
    return '';
  }
  return matrix
    .map((row) =>
      row
        .map((cell) => {
          if (cell === null || cell === undefined) {
            return '';
          }
          const cellStr = String(cell);
          if (cellStr.includes('\t') || cellStr.includes('\n')) {
            return cellStr.replace(/\t/g, ' ').replace(/\n/g, ' ');
          }
          return cellStr;
        })
        .join('\t'),
    )
    .join('\n');
};

Calling it from both onInit and onChange keeps the export populated from the start and in sync with every edit, including recalculated formula results.

Delete Buttons with renderNull

renderNull is called for cells with no value (null or undefined), so the empty column A is a natural place for action buttons. The mixin is built by a small factory that takes the component’s delete handler, so the button calls it directly — no global events or extra state needed.

// Summary rows are marked sortFixed on their row header ('022', '023', ...).
// Deriving "is this a product row?" from the sheet keeps working after rows move.
const isProductRow = (sheet: UserSheet, y: number) => y >= 1 && !sheet.getCell({ y, x: 0 })?.sortFixed;

const makeDeleteButtonMixin = (onDelete: DeleteHandler): PolicyMixinType => ({
  renderNull({ point, apply, sheet }: RenderProps<null | undefined>) {
    if (!apply || !isProductRow(sheet, point.y)) {
      return null;
    }
    const productName = String(sheet.getCell({ y: point.y, x: 2 })?.value ?? '');
    return (
      <button
        onClick={(e) => {
          e.stopPropagation();
          onDelete({ sheet, y: point.y, productName, apply });
        }}
      >
        ×
      </button>
    );
  },
});

In the component, the handler logs the product name (still readable before removal) and commits the removal with apply(sheet.removeRows(...)). onRemoveRows then fires as for any other row removal.

const handleDelete: DeleteHandler = ({ sheet, y, productName, apply }) => {
  addActivityLog(`🗑️ Delete button: "${productName}" (row ${y})`);
  apply(sheet.removeRows({ y, numRows: 1 }));
};

const book = useSpellbook({
  policies: {
    delete: new Policy({ mixins: [makeDeleteButtonMixin(handleDelete)] }),
    // ...
  },
  onChange: ({ sheet }) => { ... },
  onRemoveRows: ({ ys }) => { ... },
});

Formulas That Follow Structural Changes

Each product row computes its total with a formula (=D1*E1), and the summary rows aggregate with SUM, COUNTA, and COUNTIF. When a product row is deleted, the referenced ranges shrink automatically, so the totals in the export stay correct.

matrices: {
  A1: [
    [null, 'Laptop Pro X1', 'Electronics', 15, 1299.99, '=D1*E1'],
    // ...
  ],
  B22: [['TOTAL', '', '=SUM(D1:D21)', '', '=SUM(F1:F21)']],
  B23: [['Items', '', '=COUNTA(B1:B21)']],
  B24: [['Low Stock', '', '=COUNTIF(D1:D21,"<=10")']],
},

Summary rows are pinned with sortFixed/filterFixed (which is also what isProductRow checks), and computed columns are made read-only with prevention: operations.Write:

F: { policy: 'currency', prevention: operations.Write },
'022': { sortFixed: true, filterFixed: true },

The stock, category, and currency renderers in this demo are ordinary policy mixins; see the Policies example for policy-based styling and validation.