@molecule/api-formula-engine

Spreadsheet-style formula engine for molecule.dev.

Pure-function parser + evaluator + dependency tracker for A1-notation formulas (=SUM(A1:B5), =IF(A1>0, "y", "n"), etc.). Designed for the spreadsheet flagship app and for ad-hoc line-item math (e.g. invoice/billing computed columns).

Capabilities:

• Arithmetic: + - * / % ^ (with Excel-compatible coercions).
• Comparison: = <> < <= > >= (numbers, strings, dates, booleans).
• String: & and CONCAT() / CONCATENATE().
• References: A1, $A$1, BC42, ranges A1:B5.
• Aggregations: SUM, AVERAGE/AVG, MIN, MAX, COUNT, COUNTA, COUNTIF, SUMIF.
• Conditionals: IF, AND, OR, NOT, IFS, SWITCH, IFERROR, ISERROR, ISNUMBER, ISBLANK.
• Math: ROUND, ABS, MOD, POWER, SQRT, INT, CEILING, FLOOR.
• Dates: DATE, TODAY, NOW, YEAR, MONTH, DAY, DATEDIFF/DATEDIF (units: D/M/Y/H/MIN/S).
• Text: LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, SUBSTITUTE.
• Errors: #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A, plus engine-emitted #CIRC! for circular references.

The Sheet class wraps the lower-level helpers and provides incremental recompute (only the transitive dependents of a changed cell are re-evaluated). Use parseFormula + evaluate directly for stateless one-shot math.

Pure functions, zero I/O, zero DB access, zero hardcoded UI text.

Auto-generated, AI-first package reference for the molecule.dev ecosystem. It is written to be read by coding agents as much as by people, and is generated from this package's source — edit src/index.ts JSDoc, not this file.

Quick Start

import { Sheet, parseFormula, evaluate } from '@molecule/api-formula-engine'

// Stateful: a small in-memory spreadsheet.
const sheet = new Sheet()
sheet.setValue('A1', 10)
sheet.setValue('A2', 20)
sheet.setFormula('A3', '=SUM(A1:A2)')
sheet.getValue('A3') // → 30

sheet.setValue('A1', 100)
sheet.getValue('A3') // → 120 (auto-recomputed)

// Stateless: ad-hoc evaluation against a lookup function.
const ast = parseFormula('=A1 * 1.0875')
const lineTotal = evaluate(ast, (coord) => (coord.col === 0 && coord.row === 0 ? 49.99 : null))

Type

utility

Installation

npm install @molecule/api-formula-engine

API

Interfaces

Token

Lexical token. value is the raw source text; op carries the normalized operator string for kind: 'op' tokens.

interface Token {
  readonly kind: TokenKind
  readonly value: string
  readonly position: number
  readonly errorCode?: FormulaErrorCode
}

FormulaError

Spreadsheet error value. Returned (never thrown) by evaluate/getValue to allow downstream cells to propagate it like Excel.

interface FormulaError {
  readonly type: 'error'
  readonly code: FormulaErrorCode
  readonly message?: string
}

CellCoord

Cell coordinate as a 0-indexed { col, row }. Column 0 = "A", row 0 = 1 (i.e. A1 is { col: 0, row: 0 }).

interface CellCoord {
  readonly col: number
  readonly row: number
}

CellRange

A rectangular range of cells, half-inclusive of both corners.

interface CellRange {
  readonly start: CellCoord
  readonly end: CellCoord
}

NumberLiteralNode

AST: numeric literal.

interface NumberLiteralNode {
  readonly kind: 'number'
  readonly value: number
}

StringLiteralNode

AST: string literal (double-quoted).

interface StringLiteralNode {
  readonly kind: 'string'
  readonly value: string
}

BooleanLiteralNode

AST: boolean literal (TRUE / FALSE).

interface BooleanLiteralNode {
  readonly kind: 'boolean'
  readonly value: boolean
}

ErrorLiteralNode

AST: error literal (#DIV/0!, etc.).

interface ErrorLiteralNode {
  readonly kind: 'errorLiteral'
  readonly code: FormulaErrorCode
}

ReferenceNode

AST: cell reference (e.g. A1, $A$1).

interface ReferenceNode {
  readonly kind: 'reference'
  readonly coord: CellCoord
  /** Original textual representation, preserved for diagnostics. */
  readonly text: string
}

RangeNode

AST: cell range (e.g. A1:B5).

interface RangeNode {
  readonly kind: 'range'
  readonly range: CellRange
  readonly text: string
}

UnaryOpNode

AST: unary operation (e.g. -A1, A1%).

interface UnaryOpNode {
  readonly kind: 'unary'
  readonly op: UnaryOperator
  readonly operand: AstNode
}

BinaryOpNode

AST: binary operation.

interface BinaryOpNode {
  readonly kind: 'binary'
  readonly op: BinaryOperator
  readonly left: AstNode
  readonly right: AstNode
}

FunctionCallNode

AST: function call (e.g. SUM(A1:A10), IF(A1>0, "y", "n")).

interface FunctionCallNode {
  readonly kind: 'call'
  readonly name: string
  readonly args: AstNode[]
}

FunctionContext

Context handed to function implementations. Currently exposes the now function so date/time helpers can be made deterministic for tests via evaluateOptions.now.

interface FunctionContext {
  /** Returns the current date-time. */
  readonly now: () => Date
}

EvaluateOptions

Options shared by evaluate and the Sheet.recompute family.

interface EvaluateOptions {
  /** Override the clock used by `TODAY()` / `NOW()`. Defaults to `Date.now()`. */
  readonly now?: () => Date
}

SheetConfig

Sheet-level configuration. numCols / numRows are advisory bounds used when validating references; out-of-bounds references produce #REF!.

interface SheetConfig {
  /** Maximum number of columns. Default: 16384 (Excel's XFD). */
  readonly numCols?: number
  /** Maximum number of rows. Default: 1048576 (Excel's row count). */
  readonly numRows?: number
}

Types

ResolveCell

Function used to resolve a cell coordinate to its value. Returns null for empty cells. Implementations may evaluate dependent formulas (in which case they should detect cycles and return a #CIRC! error to the caller).

type ResolveCell = (coord: CellCoord) => CellValue

TokenKind

Lexical token kinds.

type TokenKind =
  | 'number'
  | 'string'
  | 'boolean'
  | 'error'
  | 'identifier'
  | 'reference'
  | 'range'
  | 'op'
  | 'lparen'
  | 'rparen'
  | 'comma'

FormulaErrorCode

Spreadsheet error codes. Errors propagate through expressions: any arithmetic or function call involving a FormulaError returns the left-most error unchanged (Excel-compatible semantics).

type FormulaErrorCode = '#DIV/0!' | '#VALUE!' | '#REF!' | '#NAME?' | '#NUM!' | '#N/A' | '#CIRC!'

CellValue

Possible cell values. null represents an empty cell; numbers, strings, booleans, and Dates are all valid scalar values. FormulaError represents a propagating error.

type CellValue = number | string | boolean | Date | null | FormulaError

AstNode

AST node kinds emitted by the parser.

type AstNode =
  | NumberLiteralNode
  | StringLiteralNode
  | BooleanLiteralNode
  | ErrorLiteralNode
  | ReferenceNode
  | RangeNode
  | UnaryOpNode
  | BinaryOpNode
  | FunctionCallNode

UnaryOperator

Unary operators supported by the parser.

type UnaryOperator = '+' | '-' | '%'

BinaryOperator

Binary operators. ^ = power, & = string concat, =/<> = equality, </<=/>/>= = ordering.

type BinaryOperator = '+' | '-' | '*' | '/' | '^' | '&' | '=' | '<>' | '<' | '<=' | '>' | '>='

FormulaFunction

Function implementation signature. Functions receive already-evaluated argument expressions: ranges are expanded into flat CellValue[] arrays. Functions return a CellValue (including FormulaError for domain errors).

type FormulaFunction = (
  args: ReadonlyArray<CellValue | ReadonlyArray<CellValue>>,
  ctx: FunctionContext,
) => CellValue

Classes

Sheet

In-memory single-sheet engine. References use A1 notation (A1, $A$1, BC42).

class Sheet {
  private readonly cells = new Map<string, CellRecord>()
  /** dependent-of[key] = set of cells that reference `key`. */
  private readonly dependents = new Map<string, Set<string>>()
  private readonly config: Required<SheetConfig>

  /**
   * Create a new Sheet with optional dimension limits.
   *
   * @param config - Sheet configuration.
   */
  constructor(config: SheetConfig = {}) {
    this.config = { ...DEFAULT_CONFIG, ...config }
  }

  /**
   * Set a literal value at a cell.
   *
   * @param ref - A1-style cell reference (e.g. `A1`, `$B$3`).
   * @param value - Literal value (number, string, boolean, Date, or null).
   * @param options - Evaluation options forwarded to recompute.
   */
  setValue(ref: string, value: CellValue, options: EvaluateOptions = {}): void {
    const coord = this.refToCoord(ref)
    this.assignCell(coord, value, null, null, options)
  }

  /**
   * Set a formula at a cell. The formula text may include or omit the
   * leading `=`. The formula is parsed eagerly and `#NAME?` /
   * `#VALUE!` errors caused by parser failures are stored as the
   * cell's value.
   *
   * @param ref - A1-style cell reference.
   * @param formula - Formula text (e.g. `=SUM(A1:A10)` or `SUM(A1:A10)`).
   * @param options - Evaluation options forwarded to recompute.
   */
  setFormula(ref: string, formula: string, options: EvaluateOptions = {}): void {
    const coord = this.refToCoord(ref)
    let ast: AstNode | null = null
    let parseError: CellValue | null = null
    try {
      ast = parseFormula(formula)
    } catch (e) {
      parseError = makeError('#VALUE!', (e as Error).message)
    }
    if (parseError) {
      this.assignCell(coord, parseError, null, formula, options)
      return
    }
    this.assignCell(coord, null, ast, formula, options)
  }

  /**
   * Clear a cell (remove value, formula, and dependency edges).
   *
   * @param ref - A1-style cell reference.
   * @param options - Evaluation options forwarded to recompute.
   */
  clear(ref: string, options: EvaluateOptions = {}): void {
    const coord = this.refToCoord(ref)
    const key = coordKey(coord)
    const existing = this.cells.get(key)
    if (existing) {
      for (const dep of existing.precedents) {
        this.dependents.get(dep)?.delete(key)
      }
    }
    this.cells.delete(key)
    this.recomputeDownstream(key, options)
  }

  /**
   * Read the most recently computed value for a cell.
   *
   * @param ref - A1-style cell reference.
   * @returns Cell value (`null` if the cell is empty).
   */
  getValue(ref: string): CellValue {
    const coord = this.refToCoord(ref)
    const key = coordKey(coord)
    const cell = this.cells.get(key)
    return cell ? cell.computed : null
  }

  /**
   * Return the formula text for a cell, or `null` if the cell holds a
   * literal value or is empty.
   *
   * @param ref - A1-style cell reference.
   * @returns Formula text (with leading `=`) or `null`.
   */
  getFormula(ref: string): string | null {
    const coord = this.refToCoord(ref)
    const key = coordKey(coord)
    return this.cells.get(key)?.formula ?? null
  }

  /**
   * Recompute every formula cell. Useful after bulk edits or when
   * `now()` semantics change.
   *
   * @param options - Evaluation options.
   */
  recomputeAll(options: EvaluateOptions = {}): void {
    const keys = [...this.cells.keys()]
    const { order, cycle } = topologicalSort(this.dependents, keys)
    for (const k of order) this.evaluateKey(k, options, cycle)
    for (const k of cycle) {
      const cell = this.cells.get(k)
      if (!cell) continue
      cell.computed = makeError('#CIRC!')
    }
  }

  /**
   * Evaluate a free-standing formula string against this sheet's data.
   * Does NOT mutate the sheet. Useful for ad-hoc evaluations such as
   * invoice line-item totals or computed-column previews.
   *
   * @param formula - Formula text.
   * @param options - Evaluation options.
   * @returns Evaluated cell value.
   */
  evaluateFormula(formula: string, options: EvaluateOptions = {}): CellValue {
    let ast: AstNode
    try {
      ast = parseFormula(formula)
    } catch (e) {
      return makeError('#VALUE!', (e as Error).message)
    }
    return evaluate(ast, (coord) => this.cells.get(coordKey(coord))?.computed ?? null, options)
  }

  // --- Internals ---

  /**
   * Parse an A1-style reference string into a validated CellCoord.
   */
  private refToCoord(ref: string): CellCoord {
    const coord = parseCellReference(ref)
    if (!coord) throw new Error(`Invalid cell reference: ${ref}`)
    if (coord.col >= this.config.numCols || coord.row >= this.config.numRows) {
      throw new Error(`Reference out of bounds: ${ref}`)
    }
    return coord
  }

  /**
   * Write a cell record, rewire dependency edges, and trigger downstream recompute.
   */
  private assignCell(
    coord: CellCoord,
    value: CellValue,
    ast: AstNode | null,
    formula: string | null,
    options: EvaluateOptions,
  ): void {
    const key = coordKey(coord)
    const existing = this.cells.get(key)
    if (existing) {
      for (const dep of existing.precedents) {
        this.dependents.get(dep)?.delete(key)
      }
    }
    const precedents = new Set<string>()
    if (ast) {
      for (const c of collectReferences(ast)) {
        const k = coordKey(c)
        precedents.add(k)
        let set = this.dependents.get(k)
        if (!set) {
          set = new Set()
          this.dependents.set(k, set)
        }
        set.add(key)
      }
    }
    const record: CellRecord = {
      value,
      ast,
      formula,
      precedents,
      computed: value,
    }
    this.cells.set(key, record)
    if (ast) this.evaluateKey(key, options, new Set())
    this.recomputeDownstream(key, options)
  }

  /**
   * Recompute all formula cells that transitively depend on the given key.
   */
  private recomputeDownstream(rootKey: string, options: EvaluateOptions): void {
    // Find every transitive dependent of the changed cell.
    const affected = new Set<string>()
    const stack = [rootKey]
    while (stack.length > 0) {
      const k = stack.pop()!
      const outs = this.dependents.get(k)
      if (!outs) continue
      for (const t of outs) {
        if (!affected.has(t)) {
          affected.add(t)
          stack.push(t)
        }
      }
    }
    if (affected.size === 0) return
    const keys = [...affected]
    const { order, cycle } = topologicalSort(this.dependents, keys)
    for (const k of order) this.evaluateKey(k, options, cycle)
    for (const k of cycle) {
      const cell = this.cells.get(k)
      if (!cell) continue
      cell.computed = makeError('#CIRC!')
    }
  }

  /**
   * Evaluate (or propagate) the computed value for a single cell key.
   */
  private evaluateKey(key: string, options: EvaluateOptions, cycleSet: ReadonlySet<string>): void {
    const cell = this.cells.get(key)
    if (!cell) return
    if (cycleSet.has(key)) {
      cell.computed = makeError('#CIRC!')
      return
    }
    if (!cell.ast) {
      cell.computed = cell.value
      return
    }
    const value = evaluate(
      cell.ast,
      (coord) => {
        const k = coordKey(coord)
        if (cycleSet.has(k)) return makeError('#CIRC!')
        const target = this.cells.get(k)
        return target ? target.computed : null
      },
      options,
    )
    cell.computed = value
    if (isError(value)) {
      cell.value = value
    }
  }
}

Functions

collectReferences(node)

Walk an AST and collect every coordinate it references (single references and every cell inside ranges).

function collectReferences(node: AstNode): CellCoord[]

• node — Root AST node.

Returns: Flat list of referenced coordinates (may contain duplicates).

topologicalSort(dependents, keys)

Topologically sort a set of coordinate keys by their dependency graph such that, for any edge a → b (b depends on a), a precedes b in the result.

Uses Kahn's algorithm. If a cycle is detected, the cycle members are returned in cycle and excluded from order — callers typically mark cycle members with a #CIRC! error.

function topologicalSort(
  dependents: ReadonlyMap<string, ReadonlySet<string>>,
  keys: readonly string[],
): { order: string[]; cycle: Set<string> }

• dependents — Map from a key to the set of keys that depend on it. (i.e. forward edges in the recompute order.)
• keys — Keys to sort.

Returns: { order, cycle } — order is the topological order; cycle is the set of keys participating in (or downstream of) a cycle.

evaluate(node, resolveCell, options)

Evaluate an AST in the context of a cell-resolution function.

function evaluate(node: AstNode, resolveCell: ResolveCell, options?: EvaluateOptions): CellValue

• node — Root AST node.
• resolveCell — Function returning the value of a referenced cell.
• options — Evaluation options.

Returns: Evaluated cell value (errors are returned, not thrown).

parseFormula(input)

Parse a formula string into an AST.

function parseFormula(input: string): AstNode

• input — Formula text (with or without a leading =).

Returns: Root AST node.

columnLettersToIndex(letters)

Convert a column letter (A, Z, AA, XFD) to a 0-indexed column.

function columnLettersToIndex(letters: string): number

• letters — Column letters (case-insensitive).

Returns: 0-indexed column number.

columnIndexToLetters(index)

Convert a 0-indexed column to letters (0→A, 25→Z, 26→AA).

function columnIndexToLetters(index: number): string

• index — 0-indexed column.

Returns: Column letters (uppercase).

parseCellReference(ref)

Parse a single cell reference (e.g. A1, $A$1, BC42).

function parseCellReference(ref: string): CellCoord | null

• ref — Reference text.

Returns: Parsed cell coordinate, or null if invalid.

parseCellRange(ref)

Parse a cell range (e.g. A1:B5). Normalizes so start is top-left and end is bottom-right regardless of input ordering.

function parseCellRange(ref: string): CellRange | null

• ref — Range text.

Returns: Parsed range, or null if invalid.

formatCellReference(coord)

Format a cell coordinate as A1-style text ({col:0,row:0} → A1).

function formatCellReference(coord: CellCoord): string

• coord — Cell coordinate.

Returns: A1-style reference text.

iterateRange(range)

Iterate every coordinate in a range. Yields top-to-bottom, left-to-right.

function iterateRange(range: CellRange): Generator<CellCoord, any, any>

• range — Cell range.

coordKey(coord)

Encode a coordinate to a cache-friendly string key ("col,row").

function coordKey(coord: CellCoord): string

• coord — Cell coordinate.

Returns: Stable string key.

refOf(coord)

Reformat a cell coordinate as A1-style text. Re-exported for convenience.

function refOf(coord: CellCoord): string

• coord — Cell coordinate.

Returns: A1-style reference text.

tokenize(input)

Tokenize a formula string. The leading = (if present) is stripped by the caller — pass the body of the formula here.

function tokenize(input: string): Token[]

• input — Raw formula text (no leading =).

Returns: Flat token list.

makeError(code, message)

Construct a FormulaError value.

function makeError(code: FormulaErrorCode, message?: string): FormulaError

• code — Error code.
• message — Optional human-readable description.

Returns: The error value.

isError(value)

Type-guard for FormulaError.

function isError(value: unknown): boolean

• value — Cell value to inspect.

Returns: true if the value is a propagating error.

firstError(values)

Returns the first error encountered in a flat list, or null if none.

function firstError(values: readonly CellValue[]): FormulaError | null

• values — Cell values to scan.

Returns: The first error value, or null.

toNumber(value)

Coerce a cell value to a number (Excel-compatible).

• null → 0
• boolean → 0/1
• Date → days since 1899-12-30 (Excel's epoch)
• string → parsed as number, #VALUE! if not parseable
• error → propagated unchanged

function toNumber(value: CellValue): number | FormulaError

• value — Cell value.

Returns: Number, or FormulaError on failure.

toStringValue(value)

Coerce a cell value to a string for display / & concatenation.

function toStringValue(value: CellValue): string | FormulaError

• value — Cell value.

Returns: String, or FormulaError for propagating errors.

toBoolean(value)

Coerce a cell value to a boolean (Excel-compatible).

function toBoolean(value: CellValue): boolean | FormulaError

• value — Cell value.

Returns: Boolean, or FormulaError on failure.

dateToSerial(date)

Convert a Date to an Excel serial number (days since 1899-12-30).

function dateToSerial(date: Date): number

• date — Date instance.

Returns: Excel serial date.

serialToDate(serial)

Convert an Excel serial number to a Date (UTC).

function serialToDate(serial: number): Date

• serial — Excel serial date.

Returns: Date instance.

compareValues(left, right)

Compare two scalar cell values (Excel ordering: number < string, boolean compared as 0/1 within numbers).

function compareValues(left: CellValue, right: CellValue): number | FormulaError

• left — Left operand.
• right — Right operand.

Returns: -1, 0, 1, or FormulaError if uncomparable.

Constants

BUILTIN_FUNCTIONS

Map of function names (uppercase) to implementations. Aliases like AVG → AVERAGE and DATEDIF → DATEDIFF map to the same function reference.

const BUILTIN_FUNCTIONS: Readonly<Record<string, FormulaFunction>>