Fast Excel Formula Parser and Evaluator

Common errors

• parser.parse('A1', position, true) returns #VALUE! instead of the cell value.

  cause A bug in earlier versions incorrectly returned a `#VALUE!` error when trying to parse a direct cell reference with the `allowReturnArray` flag set to `true`.
  fix Upgrade to `fast-formula-parser` version 1.0.8 or later, where this bug was fixed.

• parser.supportedFunctions() does not include SUMIF and AVERAGEIF.

  cause A bug in earlier versions caused `SUMIF` and `AVERAGEIF` to be omitted from the list returned by `supportedFunctions()`, despite being implemented and usable.
  fix Upgrade to `fast-formula-parser` version 1.0.16 or later. This version addressed the bug and correctly includes these functions in the `supportedFunctions()` output.

• FormulaError: #ERROR! (or similar FormulaError)

  cause Lexing or parsing errors within the formula string, or an `onCell`/`onRange`/`onVariable` handler returning a `FormulaError` explicitly. Before v1.0.15, detailed error information was less available.
  fix For parsing/lexing errors, inspect the `error.details` and `error.errorLocation` properties available on the `FormulaError` object (since v1.0.15) for more precise debugging information regarding the formula syntax. Ensure custom handlers (`onCell`, `onRange`, `onVariable`) return valid data types or explicit `FormulaError` objects when appropriate.

Warnings

• breaking The `onVariable()` hook signature changed in version 1.0.19. It now accepts a third parameter, `position`, which is an object `{sheet: string, row: number, col: number}`. Existing implementations need to be updated to account for this new parameter.
  Fix: Update your `onVariable` callback function signature from `(name, sheetName)` to `(name, sheetName, position)` to avoid unexpected behavior or errors.
• gotcha The `WEBSERVICE` Excel function is not implemented by default in Node.js environments due to its dependency on `fetch` or a similar HTTP client. An explicit override is required to use this function in Node.js.
  Fix: Provide a custom `WEBSERVICE` function in the parser options that uses a Node.js-compatible HTTP client (e.g., `node-fetch`). Example: `new FormulaParser({ functionsNeedContext: { WEBSERVICE: (context, url) => { const fetch = require('node-fetch'); /* ... implementation ... */ } } })`.
• breaking The behavior of `DepParser` when encountering errors changed in version 1.0.15. By default (`ignoreError: false`), the dependency parser will now throw a `FormulaError` on error instead of returning partial dependencies. Setting `ignoreError: true` will revert to the previous behavior of returning partial dependencies.
  Fix: Review `DepParser` usages and explicitly set `ignoreError: true` in the constructor options if partial dependency results are desired even with errors, or wrap calls in `try-catch` blocks to handle `FormulaError` exceptions.
• gotcha Inconsistent results between `parse` and `parseAsync` were observed in versions prior to 1.0.16, particularly when dealing with `ExcelRefFunction` and `ExcelConditionalRefFunction`. This could lead to different outcomes depending on whether an async or sync parsing method was used.
  Fix: Upgrade to version 1.0.16 or later. This release fixed the inconsistency by ensuring promises are resolved first before invoking these Excel reference functions, standardizing behavior across sync and async parsing.

Install

• npm install fast-formula-parser npm
• yarn add fast-formula-parser yarn
• pnpm add fast-formula-parser pnpm

Imports

• FormulaParser
  wrong:

  const FormulaParser = require('fast-formula-parser');

  correct:

  import FormulaParser from 'fast-formula-parser';

  While CommonJS `require` works, ESM `import` is the recommended modern approach. The library also supports named imports from the main FormulaParser object.
• { FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN }
  wrong:

  import { FormulaParser, FormulaHelpers, Types } from 'fast-formula-parser';

  correct:

  import FormulaParser, { FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN } from 'fast-formula-parser';

  The main `FormulaParser` class is a default export, while `FormulaHelpers`, `Types`, `FormulaError`, `MAX_ROW`, and `MAX_COLUMN` are named exports, available directly from the package or as properties of the default export.
• DepParser

  import { DepParser } from 'fast-formula-parser';

  The `DepParser` for formula dependency analysis is a named export. It's useful for building dependency graphs.

Quickstart

This quickstart initializes the formula parser with sample data and custom handlers for cell references, ranges, variables, and a custom function. It then demonstrates parsing and evaluating formulas, including one that uses a custom variable and another that uses a custom function, and shows how to get formula dependencies.

import FormulaParser, { FormulaError } from 'fast-formula-parser';

const data = [
  ['', 'A', 'B', 'C'],
  ['1', 10, 20, 30],
  ['2', 5, 15, '=A1+B1'],
  ['3', '=B2*2', '=SUM(A1:C1)', '=IF(C2>50, "High", "Low")']
];

const parser = new FormulaParser({
  onVariable: (name, sheetName, position) => {
    // Implement custom variable handling if needed
    // 'position' is available since v1.0.19
    console.log(`Accessing variable '${name}' at sheet '${sheetName}', position: ${JSON.stringify(position)}`);
    if (name === 'MY_CONSTANT') return 100;
    return FormulaError.NAME;
  },
  onCell: (ref, sheetName) => {
    // ref: { sheet, row, col }
    const row = ref.row - 1; // 0-indexed
    const col = ref.col - 1; // 0-indexed
    if (data[row] && data[row][col] !== undefined) {
      return data[row][col];
    }
    return FormulaError.REF;
  },
  onRange: (ref, sheetName) => {
    // ref: { sheet, from: { row, col }, to: { row, col } }
    const startRow = ref.from.row - 1;
    const startCol = ref.from.col - 1;
    const endRow = ref.to.row - 1;
    const endCol = ref.to.col - 1;
    const rangeData = [];
    for (let r = startRow; r <= endRow; r++) {
      const rowData = [];
      for (let c = startCol; c <= endCol; c++) {
        if (data[r] && data[r][c] !== undefined) {
          rowData.push(data[r][c]);
        }
      }
      rangeData.push(rowData);
    }
    return rangeData;
  },
  functions: {
    // Custom function example
    MYCUSTOMADD: (arg1, arg2) => arg1 + arg2
  }
});

// Example usage
const position = { row: 3, col: 2, sheet: 'Sheet1' }; // Position of the formula '=SUM(A1:C1)'
const formulaResult = parser.parse(data[position.row - 1][position.col - 1], position);
console.log(`Result of '=SUM(A1:C1)' at B3: ${formulaResult}`);

const directResult = parser.parse('=MYCUSTOMADD(10, MY_CONSTANT)');
console.log(`Result of '=MYCUSTOMADD(10, MY_CONSTANT)': ${directResult}`);

const ifFormulaResult = parser.parse(data[position.row][position.col], { row: 4, col: 3, sheet: 'Sheet1' });
console.log(`Result of '=IF(C2>50, "High", "Low")' at C4: ${ifFormulaResult}`);

const depParser = new FormulaParser.DepParser({ onCell: parser.options.onCell, onRange: parser.options.onRange });
const dependencies = depParser.parse('=A1+B1', { row: 2, col: 3, sheet: 'Sheet1' });
console.log(`Dependencies for '=A1+B1': ${JSON.stringify(dependencies)}`);