Calculation Web Worker

@rowsncolumns/calculation-webworker provides a modern calculation pipeline that keeps dependency tracking in the UI thread while offloading expensive formula evaluation to a dedicated Web Worker. The package exposes three building blocks:

1. CalculationGraph - builds and maintains the dependency DAG using @rowsncolumns/dag and fast-formula-parser's dependency parser.
2. ExecutionPlan - describes the ordered list of dirty cells and their precedents.
3. CalculationWorkerClient / worker runtime - serialises plans, streams them to a worker, evaluates formulas, and streams the results back.

At a high level, the UI thread:

const graph = new CalculationGraph({
  getSheetName,
  getSheetId,
  getFormula,
  // `property` is defined when the user typed =A1.foo.bar
  getCellValue,
  iterativeCalculation: {
    enabled: true,
    maxIterations: 100,
    maxChange: 0.001,
  },
});

graph.applyOperations(operations);
const plan = graph.buildExecutionPlan();

const client = new CalculationWorkerClient({ worker });
await client.initialize({ sheets: sheetMetadata, locale: navigator.language });
const result = await client.evaluate(plan, (node) => graph.buildScope(node));

In the worker bundle you call registerCalculationWorker() to wire up message handlers. The runtime constructs a FormulaParser, loads the default Spreadsheet functions, and evaluates each task in isolation.

Performance note: graph.buildScope(node) (and the batched variant used internally by enqueueCalculationOperations) memoises range dependencies per plan, so dozens of formulas referencing the same A1:A1000 range only hydrate it once per execution cycle.

Iterative calculation (circular references)

Enable iterativeCalculation on the CalculationGraph to allow circular references to converge. When enabled, the worker runtime re-evaluates the iterative group until it stabilizes.

Behavior:

• Iterates until the max per-cell delta is <= maxChange
• Returns #NUM! when it does not converge after maxIterations
• If iterativeCalculation.enabled is false, circular references surface #REF!

Tips:

• Seed initial values in your value store for circular cells (used as the starting point).
• Keep iterative groups small to reduce re-evaluation cost.

Common use cases:

• Financing cost depends on ending balance (interest loop)
• Revolving credit utilization loops
• Inventory or cash balance roll-forward models

Integration steps

1. Create the worker bundle

   // worker.ts
   import { registerCalculationWorker } from "@rowsncolumns/calculation-webworker/worker-entry";
   
   registerCalculationWorker();

   Then point your bundler (Vite, Webpack) to this entry and pass the generated Worker instance to the CalculationWorkerClient.

2. Build the DAG on the UI thread

    const graph = new CalculationGraph({
      getSheetId: (sheetName) => workbook.getSheetId(sheetName),
      getSheetName: (sheetId) => workbook.getSheet(sheetId).name,
      getFormula: (position) => workbook.getFormula(position),
      getCellValue: (position, property) => workbook.getValue(position, property),
    });

   Note: CellCoordinate objects are 1-based (A1 = { sheetId, rowIndex: 1, columnIndex: 1 }). Reuse the same convention when enqueueing CalculationOperations or reading values from your state store.

3. Apply spreadsheet operations

   graph.applyOperations([
     { kind: "upsert", position: activeCell, formula: "=SUM(A1:A10)" },
   ]);
   const plan = graph.buildExecutionPlan();

4. Evaluate in the worker

   const workerClient = new CalculationWorkerClient({
     createWorker: () => new Worker(new URL("./worker.ts", import.meta.url), { type: "module" }),
   });
   await workerClient.initialize({
     sheets: workbook.getSheets().map((sheet) => ({ id: sheet.id, name: sheet.name })),
     tables: workbook.getTables().map((table) => ({
       title: table.title,
       sheetId: table.sheetId,
       range: table.range,
       headerRow: table.headerRow,
       totalRow: table.totalRow,
       columns: table.columns?.map((column) => ({ name: column.name })),
     })),
     namedRanges: workbook.getNamedRanges().map((range) => ({
       name: range.name,
       range: range.range,
       value: range.value,
     })),
   });
   
   const plan = graph.buildExecutionPlan();
   const results = await workerClient.evaluate(plan, (node) => graph.buildScope(node));

   The optional tables and namedRanges fields keep the worker's parser in sync with your structured references and named ranges. Re-run initialize() whenever those definitions change so the worker can resolve them without asking the UI thread.

5. Commit results back to the spreadsheet state

   Iterate over results and update effective values / formatting using your existing reducers.

Working with CalculationOperation

@rowsncolumns/spreadsheet-state already emits CalculationOperation objects whenever cells change. The helper enqueueCalculationOperations accepts that array directly, converts it into DAG operations, builds an execution plan, and dispatches the work to the worker:

import {
  CalculationGraph,
  CalculationWorkerClient,
  enqueueCalculationOperations,
} from "@rowsncolumns/calculation-worker";
import type { CalculationOperation } from "@rowsncolumns/spreadsheet-state";

const graph = new CalculationGraph(...);
const workerClient = new CalculationWorkerClient(...);

async function flushCalculationQueue(ops: CalculationOperation[]) {
  const results = await enqueueCalculationOperations({
    graph,
    workerClient,
    operations: ops,
    resolveFormula: (op) => {
      // fall back to latest user-entered value if the op does not carry the formula string
      return op.value as string | undefined ?? getFormulaFromState(op.position);
    },
    onResult: (result) => {
      // stream each calculation as soon as it finishes
      applyResult(result.position, result.value);
    },
    onNodeBegin: (node) => {
      // react immediately before the worker starts evaluating this cell
      markCellAsLoading(node.position);
    },
    normalizePropertyOverride: (value, property) =>
      getEffectiveStructuredValue(value, property),
  });

  applyResultsToSheet(results);
}

By default the helper asks the CalculationGraph to build its own evaluation scope, but you can supply a custom resolveScope function if you need bespoke data hydration for each node. The optional onNodeBegin callback fires immediately before the worker starts evaluating a node, which is useful for driving UI loading indicators without having to inspect the entire execution plan up front. If your formulas expose structured results that require special handling when referenced via property access (=A1.foo.bar), pass normalizePropertyOverride so any dependency that reuses cached results goes through the same resolver you already use for getCellValue. The helper also partitions the plan by dependency level via PlanScheduler, ensuring that parallel worker pools only execute nodes whose precedents already finished, and batches the entire level into a single worker dispatch for predictable messaging overhead.

Running the pipeline in Node.js

The orchestration layer is UI-framework agnostic and also works in pure Node.js environments. The only requirement is a compatible worker implementation. Browsers supply Worker natively, but on the server you can use worker_threads:

import { Worker } from "node:worker_threads";
import {
  CalculationGraph,
  CalculationWorkerClient,
  enqueueCalculationOperations,
} from "@rowsncolumns/calculation-worker";

const workerClient = new CalculationWorkerClient({
  createWorker: () =>
    new Worker(new URL("./worker.js", import.meta.url), {
      type: "module",
    }),
});

await workerClient.initialize({
  sheets: [{ id: 1, name: "Sheet1" }],
  tables: workbook.getTables(),
  namedRanges: workbook.getNamedRanges(),
});

const graph = new CalculationGraph({
  getSheetId: () => 1,
  getSheetName: () => "Sheet1",
  getFormula: () => "=SUM(A1:A3)",
  getCellValue: () => 0,
});

const results = await enqueueCalculationOperations({
  graph,
  workerClient,
  operations: [
    {
      type: "add",
      position: { sheetId: 1, rowIndex: 1, columnIndex: 1 },
      value: "=SUM(A1:A3)",
    },
  ],
});

The worker bundle referenced above can reuse the same worker-entry helper that the browser build uses. Because the entire graph lives on the UI / Node side, the worker remains stateless and you can spin up multiple instances to process different plans in parallel when running in Node.js.

Registering custom functions

@rowsncolumns/fast-formula-parser exposes a functionsNeedContext hook that lets you inject bespoke functions (think MYFUNC() or integrations with your own APIs). To wire them up with the web worker pipeline:

1. Implement your custom functions in a module that matches the parser’s contract (the first argument is always the parser context, which includes the cell position, locale, etc.):

   // custom-functions.ts
   export const customFunctions = {
     // Example: =DOUBLE(A1)
     DOUBLE(context, value) {
       if (typeof value === "number") {
         return value * 2;
       }
       return value;
     },
     // Access the calling cell via context.position
     ROW_PLUS_COL(context) {
       return context.position.row + context.position.col;
     },
   };

2. Pass them to the worker runtime when you register it. You can merge them with the stock spreadsheet functions or override existing names:

   import { registerCalculationWorker } from "@rowsncolumns/calculation-webworker/worker-entry";
   import { customFunctions } from "./custom-functions";
   
   registerCalculationWorker({
     functions: {
       ...customFunctions,
     },
   });

3. In the UI thread nothing changes—CalculationWorkerClient keeps sending plans/scopes and the worker now understands the custom functions. If you need different bundles (e.g., tenant-specific functions), generate separate worker builds and swap them via the createWorker factory you pass to CalculationWorkerClient.

Tip: Custom functions often need additional context (user session, server data, localization). Use the scope object you already pass to enqueueCalculationOperations to send whatever metadata the worker needs, or extend the worker runtime to fetch remote data (just remember that long-running calls may block other formulas unless you spawn multiple workers).

Realtime / long-running functions

When you need functions that reach out to external services (e.g. CRYPTOPRICE, WEBSOCKETDATA, etc.) follow these guidelines:

1. Use context.position.signal for cleanup. The worker now supplies an AbortSignal on every formula invocation. Attach listeners so you can stop timers, cancel fetch() requests, or tear down sockets as soon as the host aborts the evaluation:

   export const customFunctions = {
     CRYPTOPRICE(context, symbol) {
       return new Promise(async (resolve, reject) => {
         const controller = new AbortController();
         const abort = () => {
           controller.abort();
           reject(new FormulaError("#ERROR!", "Cancelled"));
         };
         context.position.signal?.addEventListener("abort", abort, { once: true });
   
         try {
           const response = await fetch(`/api/prices/${symbol}`, {
             signal: controller.signal,
           });
           const data = await response.json();
           resolve(data.price);
         } catch (error) {
           reject(
             error instanceof FormulaError
               ? error
               : new FormulaError("#ERROR!", (error as Error).message)
           );
         } finally {
           context.position.signal?.removeEventListener("abort", abort);
         }
       });
     },
   };

2. Push realtime updates from the host, not the worker. A formula returns a single value per evaluation, so you need a small host-side service that re-dirties the cell whenever new data arrives:

   const priceSubscriptions = new Map<string, () => void>();
   
   export function subscribeToPrice(symbol: string, position: CellCoordinate) {
     if (priceSubscriptions.has(positionKey(position))) return;
     const unsubscribe = priceFeed.on(symbol, () => {
       enqueueCalculation({
         type: "dirty",
         position,
       });
     });
     priceSubscriptions.set(positionKey(position), unsubscribe);
   }

   Your custom function can call into that registry (through helpers you provide in the scope object) to register/unregister listeners while context.position.signal ensures cleanup when the evaluation is cancelled.

3. Throttle expensive operations. Because the worker may re-run formulas frequently (if the user edits related cells, or if collaboration events arrive), keep your custom functions idempotent and cache intermediate data on the host. For example, store the latest WebSocket payload and have the function simply read from that cache, then rely on subscriptions to trigger new recalculations.

This approach mirrors how Excel and Sheets implement realtime functions: computations still happen inside the calculation engine, but the orchestration (subscriptions, dirtying cells, cleanup) lives outside so the worker stays stateless and responsive.

Static references (data validations, conditional formats, etc.)

Static subscribers (named ranges, validation rules, conditional formats) can be attached to the DAG so they are notified whenever a watched range changes. Just enqueue static:add / static:remove operations alongside normal cell edits:

await enqueueCalculationOperations({
  graph,
  workerClient,
  operations: [
    {
      type: "static:add",
      reference: { id: "validator#1" },
      range: {
        sheetId: sheet.id,
        startRowIndex: 1,
        endRowIndex: 10,
        startColumnIndex: 1,
        endColumnIndex: 1,
      },
    },
    { type: "dirty", position: { sheetId: sheet.id, rowIndex: 1, columnIndex: 1 } },
  ],
  onResult: (result) => {
    if (result.staticReferenceId) {
      invalidateValidationRule(result.staticReferenceId);
    }
  },
});

Static results are streamed immediately (no worker round-trip) so you can react to them just like regular cell results.

Testing

Iterative calculation is covered in the calculation worker tests:

yarn workspace @rowsncolumns/calculation-worker test

For end-to-end spreadsheet hook coverage (including single-threaded iterative calculation):

yarn workspace @rowsncolumns/spreadsheet-state test -- use-calculation.spec.ts

This package intentionally focuses on orchestration and communication primitives so you can iterate on the spreadsheet experience without rewriting the dependency graph or formula runtime for every platform.