{"id":15616,"library":"fast-formula-parser","title":"Fast Excel Formula Parser and Evaluator","description":"fast-formula-parser is a high-performance JavaScript library designed for parsing and evaluating Microsoft Excel formulas. It uses an LL(1) parser to offer fast and reliable formula processing, supporting over 280 standard Excel functions. The library is currently at version 1.0.19 and maintains an active release cadence, frequently adding new functions, fixing bugs, and improving existing features. It differentiates itself through its speed, extensive function coverage, and a grammar engineered to eliminate ambiguities, making it an robust choice for integrating Excel-like formula capabilities into web or Node.js applications. It also provides hooks for custom functions, including asynchronous ones, and supports formula dependency parsing for building complex calculation graphs.","status":"active","version":"1.0.19","language":"javascript","source_language":"en","source_url":"https://github.com/LesterLyu/fast-formula-parser","tags":["javascript","excel","formula","spreadsheet","js","parser","excel-formula"],"install":[{"cmd":"npm install fast-formula-parser","lang":"bash","label":"npm"},{"cmd":"yarn add fast-formula-parser","lang":"bash","label":"yarn"},{"cmd":"pnpm add fast-formula-parser","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Required for the `WEBSERVICE` function when running in Node.js environments, as it's not implemented by default. Users must provide their own implementation or a compatible `fetch` polyfill.","package":"node-fetch","optional":true}],"imports":[{"note":"While CommonJS `require` works, ESM `import` is the recommended modern approach. The library also supports named imports from the main FormulaParser object.","wrong":"const FormulaParser = require('fast-formula-parser');","symbol":"FormulaParser","correct":"import FormulaParser from 'fast-formula-parser';"},{"note":"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.","wrong":"import { FormulaParser, FormulaHelpers, Types } from 'fast-formula-parser';","symbol":"{ FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN }","correct":"import FormulaParser, { FormulaHelpers, Types, FormulaError, MAX_ROW, MAX_COLUMN } from 'fast-formula-parser';"},{"note":"The `DepParser` for formula dependency analysis is a named export. It's useful for building dependency graphs.","symbol":"DepParser","correct":"import { DepParser } from 'fast-formula-parser';"}],"quickstart":{"code":"import FormulaParser, { FormulaError } from 'fast-formula-parser';\n\nconst data = [\n  ['', 'A', 'B', 'C'],\n  ['1', 10, 20, 30],\n  ['2', 5, 15, '=A1+B1'],\n  ['3', '=B2*2', '=SUM(A1:C1)', '=IF(C2>50, \"High\", \"Low\")']\n];\n\nconst parser = new FormulaParser({\n  onVariable: (name, sheetName, position) => {\n    // Implement custom variable handling if needed\n    // 'position' is available since v1.0.19\n    console.log(`Accessing variable '${name}' at sheet '${sheetName}', position: ${JSON.stringify(position)}`);\n    if (name === 'MY_CONSTANT') return 100;\n    return FormulaError.NAME;\n  },\n  onCell: (ref, sheetName) => {\n    // ref: { sheet, row, col }\n    const row = ref.row - 1; // 0-indexed\n    const col = ref.col - 1; // 0-indexed\n    if (data[row] && data[row][col] !== undefined) {\n      return data[row][col];\n    }\n    return FormulaError.REF;\n  },\n  onRange: (ref, sheetName) => {\n    // ref: { sheet, from: { row, col }, to: { row, col } }\n    const startRow = ref.from.row - 1;\n    const startCol = ref.from.col - 1;\n    const endRow = ref.to.row - 1;\n    const endCol = ref.to.col - 1;\n    const rangeData = [];\n    for (let r = startRow; r <= endRow; r++) {\n      const rowData = [];\n      for (let c = startCol; c <= endCol; c++) {\n        if (data[r] && data[r][c] !== undefined) {\n          rowData.push(data[r][c]);\n        }\n      }\n      rangeData.push(rowData);\n    }\n    return rangeData;\n  },\n  functions: {\n    // Custom function example\n    MYCUSTOMADD: (arg1, arg2) => arg1 + arg2\n  }\n});\n\n// Example usage\nconst position = { row: 3, col: 2, sheet: 'Sheet1' }; // Position of the formula '=SUM(A1:C1)'\nconst formulaResult = parser.parse(data[position.row - 1][position.col - 1], position);\nconsole.log(`Result of '=SUM(A1:C1)' at B3: ${formulaResult}`);\n\nconst directResult = parser.parse('=MYCUSTOMADD(10, MY_CONSTANT)');\nconsole.log(`Result of '=MYCUSTOMADD(10, MY_CONSTANT)': ${directResult}`);\n\nconst ifFormulaResult = parser.parse(data[position.row][position.col], { row: 4, col: 3, sheet: 'Sheet1' });\nconsole.log(`Result of '=IF(C2>50, \"High\", \"Low\")' at C4: ${ifFormulaResult}`);\n\nconst depParser = new FormulaParser.DepParser({ onCell: parser.options.onCell, onRange: parser.options.onRange });\nconst dependencies = depParser.parse('=A1+B1', { row: 2, col: 3, sheet: 'Sheet1' });\nconsole.log(`Dependencies for '=A1+B1': ${JSON.stringify(dependencies)}`);","lang":"javascript","description":"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."},"warnings":[{"fix":"Update your `onVariable` callback function signature from `(name, sheetName)` to `(name, sheetName, position)` to avoid unexpected behavior or errors.","message":"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.","severity":"breaking","affected_versions":">=1.0.19"},{"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 ... */ } } })`.","message":"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.","severity":"gotcha","affected_versions":">=1.0.18"},{"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.","message":"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.","severity":"breaking","affected_versions":">=1.0.15"},{"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.","message":"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.","severity":"gotcha","affected_versions":"<1.0.16"}],"env_vars":null,"last_verified":"2026-04-21T00:00:00.000Z","next_check":"2026-07-20T00:00:00.000Z","problems":[{"fix":"Upgrade to `fast-formula-parser` version 1.0.8 or later, where this bug was fixed.","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`.","error":"parser.parse('A1', position, true) returns #VALUE! instead of the cell value."},{"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.","cause":"A bug in earlier versions caused `SUMIF` and `AVERAGEIF` to be omitted from the list returned by `supportedFunctions()`, despite being implemented and usable.","error":"parser.supportedFunctions() does not include SUMIF and AVERAGEIF."},{"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.","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.","error":"FormulaError: #ERROR! (or similar FormulaError)"}],"ecosystem":"npm"}