Backend Calculation and Persistence

Formula-derived values are not written back into source rows. Formula-aware mounted rendering, sorting, and filtering can consume derived values, while grid.source remains the authoritative raw data.

Choose what to persist

Strategy	Persist	Use when
Formulas only	Raw rows containing =...	Every consumer can recalculate and no queryable cache is needed.
Formulas + calculated cache	Raw formulas plus values/patches	APIs, reports, or searches need current values without losing expressions.
Resolved snapshot	A separate copy with formulas replaced by values	Exporting or handing data to a system that cannot evaluate formulas.

Recommended: calculate a workbook

calculateFormulaWorkbook is synchronous, does not mount a grid, and does not mutate supplied rows. Each sheet needs a case-insensitively unique id, unique column properties, a stable rowIdProp, and a valid id on every row.

import { calculateFormulaWorkbook } from '@revolist/revogrid-pro';

const orders = {
  id: 'Orders',
  rowIdProp: 'id',
  columns: [
    { prop: 'sku', name: 'SKU' },
    { prop: 'qty', name: 'Quantity' },
    { prop: 'price', name: 'Price' },
    { prop: 'total', name: 'Total' },
  ],
  rows: [{
    id: 'order-1',
    sku: 'B-200',
    qty: 2,
    price: '=VLOOKUP(A1,\'Product List\'!A1:B2,2,FALSE)',
    total: '=B1*C1',
  }],
};
const products = {
  id: 'Product List',
  rowIdProp: 'id',
  columns: [
    { prop: 'sku', name: 'SKU' },
    { prop: 'price', name: 'Price' },
  ],
  rows: [
    { id: 'product-1', sku: 'A-100', price: 10 },
    { id: 'product-2', sku: 'B-200', price: 25 },
  ],
};

const result = calculateFormulaWorkbook({
  sheets: [orders, products],
  previousValues,
});

if (result.errors.length) {
  reportFormulaErrors(result.errors);
}

await database.transaction(async transaction => {
  for (const patch of result.patches) {
    await persistCalculatedCell(transaction, patch);
  }
});

previousValues = result.values;

Expected result: values.Orders['order-1'] is { price: 25, total: 50 }. On the first run, patches identify Orders!C1 and Orders!D1, including sheet id, row id, property, formula, and calculated value. Passing the returned values unchanged as the next previousValues baseline produces no patches.

Isolate formula errors

Errors are cell-level. An unknown function or circular dependency yields an entry in errors and an error-bearing patch, while independent cells still calculate.

const result = calculateFormulaWorkbook({
  sheets: [{
    id: 'Orders',
    rowIdProp: 'id',
    columns: [
      { prop: 'broken', name: 'Broken' }, // A
      { prop: 'qty', name: 'Quantity' }, // B
      { prop: 'price', name: 'Price' },  // C
      { prop: 'total', name: 'Total' },  // D
    ],
    rows: [{
      id: 'order-1',
      broken: '=UNKNOWN(A1)',
      qty: 2,
      price: 25,
      total: '=B1*C1',
    }],
  }],
});

Expected result: errors contains A1 with #ERROR, while D1 still calculates to 50. Decide explicitly whether your transaction persists successful patches, rejects the whole workbook, or queues a correction.

Scoped browser snapshot

For a simple one-sheet browser snapshot, evaluate only formula cells against the exact flat row and column arrays you supply:

import {
  evaluateRawValuesFormula,
  isFormula,
} from '@revolist/revogrid-pro';

const source = await grid.getSource();
const columns = await grid.getColumns();
const snapshot = source.map(row => {
  const resolved = { ...row };
  for (const column of columns) {
    const raw = row[column.prop];
    if (isFormula(raw)) {
      resolved[column.prop] = evaluateRawValuesFormula(raw, source, columns);
    }
  }
  return resolved;
});

Expected result: snapshot contains calculated values and source remains unchanged. This utility does not automatically reproduce mounted pin partitions, external sheets, async functions, or the grid’s authored-identity runtime; use the workbook API for production multi-sheet recalculation.

The application owns scheduling, transactions, authorization, retries, conflict handling, and persistence. RevoGrid calculates the supplied snapshot but does not watch or write your database.

Next steps

Previous: Async Formula Functions · Next: Supported Functions · Deeper reference: Formula API