Spaces:
Paused
Paused
Download web/src/customer-grid/formulaEngine.ts from aios-loopable/cfo-os: direct link, hf CLI and curl.
- Browser
- Download file 31.9 kB
-
https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/web/src/customer-grid/formulaEngine.ts
- Command line
-
hf download hf://spaces/aios-loopable/cfo-os/web/src/customer-grid/formulaEngine.ts
-
curl -L -o formulaEngine.ts https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/web/src/customer-grid/formulaEngine.ts
31.9 kB
| // --------------------------------------------------------------------------- | |
| // customer-grid / formulaEngine.ts | |
| // Wave-5 I-B, grown to EXCEL GRADE 2026-07-31 (owner item 2). A pure module: | |
| // no React, no DOM, no imports from the rest of the grid, so it is testable | |
| // under plain node and portable into the shell unchanged. | |
| // | |
| // GRAMMAR (v2 — the shared contract; v1 remains a strict subset): | |
| // numbers 1 0.5 .5 1200.75 (no separators, no exponents) | |
| // strings "text" with "" escaping a quote — Excel's own rule | |
| // booleans TRUE FALSE (bare constants) | |
| // field refs {revenue_ytd} (slug charset [A-Za-z0-9_]) | |
| // arithmetic + - * / ^ ( ) (usual precedence; ^ is | |
| // right-associative and UNARY MINUS BINDS TIGHTER, Excel's | |
| // famous quirk: -2^2 = 4) | |
| // text & (concatenation; blank -> "") | |
| // comparisons < <= > >= = != (== and <> accepted) — numbers with | |
| // numbers, text with text (case-insensitive, Excel's rule); | |
| // a mixed or blank comparison is BLANK, never a guess. | |
| // functions numeric ABS ROUND ROUNDUP ROUNDDOWN INT MOD SQRT POWER | |
| // EXP LN SUM AVERAGE COUNT COUNTA MIN MAX | |
| // logic IF AND OR NOT IFERROR ISBLANK | |
| // text CONCATENATE/CONCAT LEFT RIGHT MID LEN TRIM | |
| // UPPER LOWER PROPER VALUE TEXT | |
| // date TODAY YEAR MONTH DAY DAYS (ISO YYYY-MM-DD) | |
| // | |
| // THE ONE RULE, unchanged: **any error → BLANK, never a wrong number.** An | |
| // unknown ref, a divide-by-zero, a boolean fed to arithmetic, an unreadable | |
| // date, an unknown TEXT() format — every one yields null, and null propagates | |
| // through ARITHMETIC. Inventing 0 for a missing value is how a wrong number | |
| // gets a confident face; this engine refuses instead. | |
| // | |
| // Where v2 deliberately follows EXCEL rather than v1's stricter reading: | |
| // - THE AGGREGATES SKIP BLANKS. SUM/AVERAGE/COUNT/COUNTA/MIN/MAX ignore | |
| // blank arguments exactly as Excel ignores empty cells — that selectivity | |
| // is the entire reason the aggregate forms exist beside `+`. (v1's | |
| // MIN/MAX-propagate-blank rule is superseded; `+` still propagates.) | |
| // - TEXT CONTEXT COERCES BLANK TO "". `{a} & {b}` with b blank is a, as in | |
| // Excel. Text is display, not arithmetic — "" states nothing false. | |
| // - A BOOLEAN RESULT DISPLAYS AS TRUE/FALSE (v1 blanked it). A comparison | |
| // the user typed deserves its answer on screen. | |
| // - FORMULA-OVER-FORMULA IS ALLOWED. Evaluation is topological | |
| // (`orderFormulas`); a CYCLE refuses at authoring time and every member | |
| // of one evaluates to blank — never a stale or half-updated number. | |
| // - IF's condition must still be a real BOOLEAN (no numeric truthiness): | |
| // this is a financial surface, and 0-means-false is a silent wrong branch. | |
| // - ROUND stays HALF-AWAY-FROM-ZERO (Excel's convention too). | |
| // --------------------------------------------------------------------------- | |
| /** Source-length cap. The host's structural validation mirrors it; both sides | |
| * refuse rather than truncate (a truncated formula is a different formula). */ | |
| export const MAX_FORMULA_LENGTH = 500; | |
| /** Token cap — a bounded tree for something re-evaluated per row per render. */ | |
| export const MAX_FORMULA_TOKENS = 200; | |
| /** Parse-depth cap (nested parens/calls). */ | |
| export const MAX_FORMULA_DEPTH = 24; | |
| export type FormulaAst = | |
| | { t: "num"; v: number } | |
| | { t: "str"; v: string } | |
| | { t: "bool"; v: boolean } | |
| | { t: "ref"; k: string } | |
| | { t: "neg"; e: FormulaAst } | |
| | { t: "bin"; op: "+" | "-" | "*" | "/" | "^" | "&"; l: FormulaAst; r: FormulaAst } | |
| | { t: "cmp"; op: "<" | "<=" | ">" | ">=" | "=" | "!="; l: FormulaAst; r: FormulaAst } | |
| | { t: "call"; fn: FormulaFn; args: FormulaAst[] }; | |
| export type FormulaFn = | |
| | "ABS" | "ROUND" | "ROUNDUP" | "ROUNDDOWN" | "INT" | "MOD" | "SQRT" | "POWER" | |
| | "EXP" | "LN" | "SUM" | "AVERAGE" | "COUNT" | "COUNTA" | "MIN" | "MAX" | |
| | "IF" | "AND" | "OR" | "NOT" | "IFERROR" | "ISBLANK" | |
| | "CONCATENATE" | "LEFT" | "RIGHT" | "MID" | "LEN" | "TRIM" | |
| | "UPPER" | "LOWER" | "PROPER" | "VALUE" | "TEXT" | |
| | "TODAY" | "YEAR" | "MONTH" | "DAY" | "DAYS"; | |
| const MANY = 30; // variadic cap — bounded, far above any honest use | |
| const FN_ARITY: Record<FormulaFn, [number, number]> = { | |
| ABS: [1, 1], | |
| ROUND: [1, 2], | |
| ROUNDUP: [1, 2], | |
| ROUNDDOWN: [1, 2], | |
| INT: [1, 1], | |
| MOD: [2, 2], | |
| SQRT: [1, 1], | |
| POWER: [2, 2], | |
| EXP: [1, 1], | |
| LN: [1, 1], | |
| SUM: [1, MANY], | |
| AVERAGE: [1, MANY], | |
| COUNT: [1, MANY], | |
| COUNTA: [1, MANY], | |
| MIN: [1, MANY], | |
| MAX: [1, MANY], | |
| IF: [2, 3], | |
| AND: [1, MANY], | |
| OR: [1, MANY], | |
| NOT: [1, 1], | |
| IFERROR: [2, 2], | |
| ISBLANK: [1, 1], | |
| CONCATENATE: [1, MANY], | |
| LEFT: [1, 2], | |
| RIGHT: [1, 2], | |
| MID: [3, 3], | |
| LEN: [1, 1], | |
| TRIM: [1, 1], | |
| UPPER: [1, 1], | |
| LOWER: [1, 1], | |
| PROPER: [1, 1], | |
| VALUE: [1, 1], | |
| TEXT: [2, 2], | |
| TODAY: [0, 0], | |
| YEAR: [1, 1], | |
| MONTH: [1, 1], | |
| DAY: [1, 1], | |
| DAYS: [2, 2], | |
| }; | |
| /** Excel spells it CONCAT in modern versions; both names, one function. */ | |
| const FN_ALIASES: Record<string, FormulaFn> = { CONCAT: "CONCATENATE" }; | |
| export type ParseResult = | |
| | { ok: true; ast: FormulaAst; refs: string[] } | |
| | { ok: false; error: string }; | |
| type Token = | |
| | { t: "num"; v: number } | |
| | { t: "str"; v: string } | |
| | { t: "ref"; k: string } | |
| | { t: "ident"; v: string } | |
| | { t: "op"; v: string }; | |
| const CMP_OPS = new Set(["<", "<=", ">", ">=", "=", "!="]); | |
| function tokenize(src: string): Token[] | string { | |
| const out: Token[] = []; | |
| let i = 0; | |
| while (i < src.length) { | |
| const c = src[i]; | |
| if (c === " " || c === "\t" || c === "\n" || c === "\r") { | |
| i += 1; | |
| continue; | |
| } | |
| // {field_key} | |
| if (c === "{") { | |
| const end = src.indexOf("}", i + 1); | |
| if (end < 0) return "a field reference is missing its closing }"; | |
| const k = src.slice(i + 1, end).trim(); | |
| if (!/^[A-Za-z0-9_]+$/.test(k)) | |
| return k === "" | |
| ? "empty field reference {}" | |
| : `field reference {${k}} may only contain letters, digits and _`; | |
| out.push({ t: "ref", k }); | |
| i = end + 1; | |
| continue; | |
| } | |
| // "string" — Excel's own escape: "" inside a string is one quote | |
| if (c === '"') { | |
| let j = i + 1; | |
| let s = ""; | |
| for (;;) { | |
| if (j >= src.length) return "a text value is missing its closing quote"; | |
| if (src[j] === '"') { | |
| if (src[j + 1] === '"') { | |
| s += '"'; | |
| j += 2; | |
| continue; | |
| } | |
| break; | |
| } | |
| s += src[j]; | |
| j += 1; | |
| } | |
| out.push({ t: "str", v: s }); | |
| i = j + 1; | |
| continue; | |
| } | |
| // number: 1 1.5 .5 (a trailing dot — "5." — is refused) | |
| if (/[0-9]/.test(c) || (c === "." && /[0-9]/.test(src[i + 1] ?? ""))) { | |
| const m = /^(?:[0-9]+(?:\.[0-9]+)?|\.[0-9]+)/.exec(src.slice(i)); | |
| if (!m) return `unreadable number at "${src.slice(i, i + 8)}"`; | |
| if (/^[0-9.]/.test(src[i + m[0].length] ?? "")) | |
| return `unreadable number at "${src.slice(i, i + 8)}"`; | |
| out.push({ t: "num", v: Number(m[0]) }); | |
| i += m[0].length; | |
| continue; | |
| } | |
| // function names / TRUE / FALSE | |
| if (/[A-Za-z_]/.test(c)) { | |
| const m = /^[A-Za-z_][A-Za-z0-9_]*/.exec(src.slice(i))!; | |
| out.push({ t: "ident", v: m[0] }); | |
| i += m[0].length; | |
| continue; | |
| } | |
| // operators — two-char first. `<>` is Excel's not-equal; `==` tolerated as `=`. | |
| const two = src.slice(i, i + 2); | |
| if (two === "<=" || two === ">=" || two === "!=" || two === "==" || two === "<>") { | |
| out.push({ t: "op", v: two === "==" ? "=" : two === "<>" ? "!=" : two }); | |
| i += 2; | |
| continue; | |
| } | |
| if ("+-*/^&(),<>=".includes(c)) { | |
| out.push({ t: "op", v: c }); | |
| i += 1; | |
| continue; | |
| } | |
| return `unexpected character "${c}"`; | |
| } | |
| return out; | |
| } | |
| /** | |
| * Recursive-descent parser. Grammar, lowest binding first: | |
| * expr := concat ((< <= > >= = !=) concat)? -- ONE comparison, no chains | |
| * concat := additive (& additive)* -- Excel: & binds below + - | |
| * additive:= mult ((+|-) mult)* | |
| * mult := power ((*|/) power)* | |
| * power := unary (^ power)? -- right-assoc; unary minus | |
| * inside, so -2^2 = (-2)^2 | |
| * unary := "-" unary | primary | |
| * primary := number | string | TRUE | FALSE | ref | "(" expr ")" | FN "(" … ")" | |
| */ | |
| export function parseFormula(src: string): ParseResult { | |
| if (typeof src !== "string" || src.trim() === "") | |
| return { ok: false, error: "the formula is empty" }; | |
| if (src.length > MAX_FORMULA_LENGTH) | |
| return { ok: false, error: `formula longer than ${MAX_FORMULA_LENGTH} characters` }; | |
| const toks = tokenize(src); | |
| if (typeof toks === "string") return { ok: false, error: toks }; | |
| if (toks.length === 0) return { ok: false, error: "the formula is empty" }; | |
| if (toks.length > MAX_FORMULA_TOKENS) | |
| return { ok: false, error: `formula has more than ${MAX_FORMULA_TOKENS} tokens` }; | |
| let pos = 0; | |
| const peek = (): Token | undefined => toks[pos]; | |
| const isOp = (v: string): boolean => { | |
| const t = toks[pos]; | |
| return t?.t === "op" && t.v === v; | |
| }; | |
| let err: string | null = null; | |
| const fail = (message: string): null => { | |
| if (err === null) err = message; | |
| return null; | |
| }; | |
| function parseExpr(depth: number): FormulaAst | null { | |
| if (depth > MAX_FORMULA_DEPTH) return fail("formula is nested too deeply"); | |
| const left = parseConcat(depth); | |
| if (!left) return null; | |
| const t = peek(); | |
| if (t?.t === "op" && CMP_OPS.has(t.v)) { | |
| pos += 1; | |
| const right = parseConcat(depth); | |
| if (!right) return null; | |
| const again = peek(); | |
| if (again?.t === "op" && CMP_OPS.has(again.v)) | |
| return fail("comparisons cannot be chained (a < b < c)"); | |
| return { t: "cmp", op: t.v as "<", l: left, r: right }; | |
| } | |
| return left; | |
| } | |
| function parseConcat(depth: number): FormulaAst | null { | |
| let node = parseAdditive(depth); | |
| if (!node) return null; | |
| while (isOp("&")) { | |
| pos += 1; | |
| const r = parseAdditive(depth); | |
| if (!r) return null; | |
| node = { t: "bin", op: "&", l: node, r }; | |
| } | |
| return node; | |
| } | |
| function parseAdditive(depth: number): FormulaAst | null { | |
| let node = parseMult(depth); | |
| if (!node) return null; | |
| for (;;) { | |
| if (isOp("+") || isOp("-")) { | |
| const op = (peek() as { v: "+" | "-" }).v; | |
| pos += 1; | |
| const r = parseMult(depth); | |
| if (!r) return null; | |
| node = { t: "bin", op, l: node, r }; | |
| } else return node; | |
| } | |
| } | |
| function parseMult(depth: number): FormulaAst | null { | |
| let node = parsePower(depth); | |
| if (!node) return null; | |
| for (;;) { | |
| if (isOp("*") || isOp("/")) { | |
| const op = (peek() as { v: "*" | "/" }).v; | |
| pos += 1; | |
| const r = parsePower(depth); | |
| if (!r) return null; | |
| node = { t: "bin", op, l: node, r }; | |
| } else return node; | |
| } | |
| } | |
| function parsePower(depth: number): FormulaAst | null { | |
| if (depth > MAX_FORMULA_DEPTH) return fail("formula is nested too deeply"); | |
| const base = parseUnary(depth); | |
| if (!base) return null; | |
| if (isOp("^")) { | |
| pos += 1; | |
| const exp = parsePower(depth + 1); // right-associative: 2^3^2 = 2^(3^2) | |
| if (!exp) return null; | |
| return { t: "bin", op: "^", l: base, r: exp }; | |
| } | |
| return base; | |
| } | |
| function parseUnary(depth: number): FormulaAst | null { | |
| if (depth > MAX_FORMULA_DEPTH) return fail("formula is nested too deeply"); | |
| if (isOp("-")) { | |
| pos += 1; | |
| const e = parseUnary(depth + 1); | |
| return e ? { t: "neg", e } : null; | |
| } | |
| return parsePrimary(depth); | |
| } | |
| function parsePrimary(depth: number): FormulaAst | null { | |
| if (depth > MAX_FORMULA_DEPTH) return fail("formula is nested too deeply"); | |
| const t = peek(); | |
| if (!t) return fail("the formula ends unexpectedly"); | |
| if (t.t === "num") { | |
| pos += 1; | |
| return { t: "num", v: t.v }; | |
| } | |
| if (t.t === "str") { | |
| pos += 1; | |
| return { t: "str", v: t.v }; | |
| } | |
| if (t.t === "ref") { | |
| pos += 1; | |
| return { t: "ref", k: t.k }; | |
| } | |
| if (t.t === "op" && t.v === "(") { | |
| pos += 1; | |
| const inner = parseExpr(depth + 1); | |
| if (!inner) return null; | |
| if (!isOp(")")) return fail("missing closing parenthesis"); | |
| pos += 1; | |
| return inner; | |
| } | |
| if (t.t === "ident") { | |
| const upper = t.v.toUpperCase(); | |
| if (upper === "TRUE" || upper === "FALSE") { | |
| pos += 1; | |
| return { t: "bool", v: upper === "TRUE" }; | |
| } | |
| const fn = (FN_ALIASES[upper] ?? upper) as FormulaFn; | |
| if (!(fn in FN_ARITY)) | |
| return fail(`unknown function ${upper} — see the formula help for what is available`); | |
| pos += 1; | |
| if (!isOp("(")) return fail(`${fn} must be called with parentheses: ${fn}(…)`); | |
| pos += 1; | |
| const args: FormulaAst[] = []; | |
| if (!isOp(")")) { | |
| for (;;) { | |
| const a = parseExpr(depth + 1); | |
| if (!a) return null; | |
| args.push(a); | |
| if (isOp(",")) { | |
| pos += 1; | |
| continue; | |
| } | |
| break; | |
| } | |
| } | |
| if (!isOp(")")) return fail(`missing closing parenthesis on ${fn}(…)`); | |
| pos += 1; | |
| const [lo, hi] = FN_ARITY[fn]; | |
| if (args.length < lo || args.length > hi) | |
| return fail( | |
| lo === hi | |
| ? `${fn} takes exactly ${lo} argument${lo === 1 ? "" : "s"}` | |
| : `${fn} takes ${lo} to ${hi} arguments` | |
| ); | |
| return { t: "call", fn, args }; | |
| } | |
| return fail(`unexpected "${t.t === "op" ? t.v : String((t as { v: unknown }).v)}"`); | |
| } | |
| const ast = parseExpr(1); | |
| if (!ast) return { ok: false, error: err ?? "could not parse the formula" }; | |
| if (pos !== toks.length) { | |
| const t = toks[pos]; | |
| return { | |
| ok: false, | |
| error: `unexpected "${t.t === "op" || t.t === "ident" ? (t as { v: string }).v : t.t === "ref" ? `{${(t as { k: string }).k}}` : String((t as { v: unknown }).v)}" after the formula`, | |
| }; | |
| } | |
| return { ok: true, ast, refs: collectRefs(ast) }; | |
| } | |
| /** Every {ref} the tree names, deduped, in first-appearance order. */ | |
| export function collectRefs(ast: FormulaAst): string[] { | |
| const out: string[] = []; | |
| const walk = (n: FormulaAst): void => { | |
| switch (n.t) { | |
| case "ref": | |
| if (!out.includes(n.k)) out.push(n.k); | |
| return; | |
| case "neg": | |
| walk(n.e); | |
| return; | |
| case "bin": | |
| case "cmp": | |
| walk(n.l); | |
| walk(n.r); | |
| return; | |
| case "call": | |
| n.args.forEach(walk); | |
| return; | |
| default: | |
| return; | |
| } | |
| }; | |
| walk(ast); | |
| return out; | |
| } | |
| /** number | string | boolean | null inside the tree; the CELL result is | |
| * number | string | null (booleans display as "TRUE"/"FALSE"). */ | |
| type Value = number | string | boolean | null; | |
| /** What TODAY() and friends resolve against. `today` is the TENANT's day | |
| * (payload `today`), never the browser clock. */ | |
| export interface FormulaEnv { | |
| today?: string; | |
| } | |
| /** | |
| * A cell value coerced for arithmetic. Strict on purpose: | |
| * number (finite) -> itself 0 is a real value, not a blank | |
| * "" / null / undefined-> null blank in, blank out | |
| * numeric string -> its number (trimmed; Number(), never parseFloat) | |
| * anything else -> null text is not silently 0 | |
| */ | |
| export function toOperand(raw: unknown): number | null { | |
| if (typeof raw === "number") return Number.isFinite(raw) ? raw : null; | |
| if (raw == null) return null; | |
| if (typeof raw === "string") { | |
| const s = raw.trim(); | |
| if (s === "") return null; | |
| const n = Number(s); | |
| return Number.isFinite(n) ? n : null; | |
| } | |
| if (typeof raw === "boolean") return null; // a checkbox is not a number | |
| return null; | |
| } | |
| function asNumber(v: Value): number | null { | |
| if (typeof v === "number" && Number.isFinite(v)) return v; | |
| if (typeof v === "string") return toOperand(v); | |
| return null; | |
| } | |
| /** Text context: blank -> "" (Excel), numbers in General format, booleans as | |
| * their names. Text is display, not arithmetic — "" states nothing false. */ | |
| function asText(v: Value): string { | |
| if (v == null) return ""; | |
| if (typeof v === "string") return v; | |
| if (typeof v === "boolean") return v ? "TRUE" : "FALSE"; | |
| return numToText(v); | |
| } | |
| /** Excel's "General": no separators, no trailing zeros, up to ~10 dp. */ | |
| function numToText(n: number): string { | |
| if (Number.isInteger(n)) return String(n); | |
| return String(Number(n.toFixed(10))); | |
| } | |
| /** Airtable/Excel ROUND: half away from zero, precision may be negative (tens). */ | |
| function roundHalfAway(x: number, p: number): number | null { | |
| if (!Number.isInteger(p) || p < -10 || p > 10) return null; | |
| const m = Math.pow(10, p); | |
| const r = Math.sign(x) * Math.round(Math.abs(x) * m); | |
| const out = r / m; | |
| return Number.isFinite(out) ? out : null; | |
| } | |
| /** An ISO date's parts, or null. Accepts "YYYY-MM-DD" and anything that starts | |
| * with it ("YYYY-MM-DD HH:MM:SS") — the shapes the grid's date fields hold. */ | |
| function dateParts(v: Value): { y: number; m: number; d: number } | null { | |
| if (typeof v !== "string") return null; | |
| const m = /^(\d{4})-(\d{2})-(\d{2})/.exec(v.trim()); | |
| if (!m) return null; | |
| const y = Number(m[1]); | |
| const mo = Number(m[2]); | |
| const d = Number(m[3]); | |
| if (mo < 1 || mo > 12 || d < 1 || d > 31) return null; | |
| return { y, m: mo, d }; | |
| } | |
| function dateSerial(p: { y: number; m: number; d: number }): number { | |
| return Date.UTC(p.y, p.m - 1, p.d) / 86_400_000; | |
| } | |
| /** | |
| * TEXT(value, format) — the common Excel number formats, fail-closed: | |
| * "0" "0.00" fixed decimals | |
| * "#,##0" "#,##0.00" thousands separators | |
| * "$#,##0.00" … a literal currency prefix | |
| * "0%" "0.0%" percent of 1 (0.42 -> "42%") | |
| * An unrecognised format is BLANK, never a guess at what it meant. | |
| */ | |
| function textFormat(n: number, fmt: string): string | null { | |
| const m = /^(\$?)(#,##0|0)(?:\.(0+))?(%?)$/.exec(fmt.trim()); | |
| if (!m) return null; | |
| const [, cur, intPart, decimals, pct] = m; | |
| let x = n; | |
| if (pct === "%") x *= 100; | |
| const dp = decimals ? decimals.length : 0; | |
| const neg = x < 0 || Object.is(x, -0) ? "-" : ""; | |
| const fixed = Math.abs(x).toFixed(dp); | |
| let [ints, frac] = fixed.split("."); | |
| if (intPart === "#,##0") ints = ints.replace(/\B(?=(\d{3})+(?!\d))/g, ","); | |
| return neg + cur + ints + (dp ? "." + frac : "") + (pct === "%" ? "%" : ""); | |
| } | |
| /** The aggregate forms: Excel-style, blanks SKIPPED (that selectivity is why | |
| * they exist beside `+`). Returns the numeric args that resolved. */ | |
| function numericArgs(nodes: FormulaAst[], ev: (n: FormulaAst) => Value): number[] { | |
| const out: number[] = []; | |
| for (const a of nodes) { | |
| const v = ev(a); | |
| if (v == null) continue; | |
| const n = asNumber(v); | |
| if (n != null) out.push(n); | |
| } | |
| return out; | |
| } | |
| function evalNode(n: FormulaAst, get: (key: string) => unknown, env: FormulaEnv): Value { | |
| const ev = (x: FormulaAst): Value => evalNode(x, get, env); | |
| switch (n.t) { | |
| case "num": | |
| return n.v; | |
| case "str": | |
| return n.v; | |
| case "bool": | |
| return n.v; | |
| case "ref": { | |
| const raw = get(n.k); | |
| // Text stays text (LEFT/&/comparisons need it); numbers stay numbers. | |
| if (typeof raw === "string") return raw; | |
| if (typeof raw === "number") return Number.isFinite(raw) ? raw : null; | |
| if (typeof raw === "boolean") return null; // a checkbox is not a value here | |
| return null; | |
| } | |
| case "neg": { | |
| const v = asNumber(ev(n.e)); | |
| return v == null ? null : -v; | |
| } | |
| case "bin": { | |
| if (n.op === "&") { | |
| // Concatenation is TEXT context: blanks become "", nothing errors. | |
| return asText(ev(n.l)) + asText(ev(n.r)); | |
| } | |
| const l = asNumber(ev(n.l)); | |
| if (l == null) return null; | |
| const r = asNumber(ev(n.r)); | |
| if (r == null) return null; | |
| let out: number; | |
| switch (n.op) { | |
| case "+": out = l + r; break; | |
| case "-": out = l - r; break; | |
| case "*": out = l * r; break; | |
| case "/": | |
| if (r === 0) return null; | |
| out = l / r; | |
| break; | |
| case "^": | |
| out = Math.pow(l, r); | |
| break; | |
| } | |
| return Number.isFinite(out) ? out : null; | |
| } | |
| case "cmp": { | |
| const lv = ev(n.l); | |
| const rv = ev(n.r); | |
| const ln = typeof lv === "number" ? lv : null; | |
| const rn = typeof rv === "number" ? rv : null; | |
| // numbers with numbers (numeric strings count as numbers, Excel's coercion)… | |
| const lAsN = ln ?? (typeof lv === "string" ? toOperand(lv) : null); | |
| const rAsN = rn ?? (typeof rv === "string" ? toOperand(rv) : null); | |
| if (lAsN != null && rAsN != null) return cmpNums(n.op, lAsN, rAsN); | |
| // …text with text, case-insensitively (Excel's rule)… | |
| if (typeof lv === "string" && typeof rv === "string") { | |
| const a = lv.trim().toLowerCase(); | |
| const b = rv.trim().toLowerCase(); | |
| if (a === "" || b === "") return null; // a blank comparison is not an answer | |
| return cmpNums(n.op, a < b ? -1 : a > b ? 1 : 0, 0); | |
| } | |
| // …anything mixed or blank is BLANK, never a guess. | |
| return null; | |
| } | |
| case "call": { | |
| switch (n.fn) { | |
| case "ABS": { | |
| const v = asNumber(ev(n.args[0])); | |
| return v == null ? null : Math.abs(v); | |
| } | |
| case "ROUND": | |
| case "ROUNDUP": | |
| case "ROUNDDOWN": { | |
| const v = asNumber(ev(n.args[0])); | |
| if (v == null) return null; | |
| const p = n.args.length > 1 ? asNumber(ev(n.args[1])) : 0; | |
| if (p == null || !Number.isInteger(p) || p < -10 || p > 10) return null; | |
| if (n.fn === "ROUND") return roundHalfAway(v, p); | |
| const m = Math.pow(10, p); | |
| const scaled = v * m; | |
| const r = n.fn === "ROUNDUP" | |
| ? Math.sign(scaled) * Math.ceil(Math.abs(scaled)) | |
| : Math.sign(scaled) * Math.floor(Math.abs(scaled)); | |
| const out = r / m; | |
| return Number.isFinite(out) ? out : null; | |
| } | |
| case "INT": { | |
| const v = asNumber(ev(n.args[0])); | |
| return v == null ? null : Math.floor(v); // Excel INT floors toward -∞ | |
| } | |
| case "MOD": { | |
| const a = asNumber(ev(n.args[0])); | |
| const b = asNumber(ev(n.args[1])); | |
| if (a == null || b == null || b === 0) return null; | |
| return a - b * Math.floor(a / b); // Excel: sign follows the divisor | |
| } | |
| case "SQRT": { | |
| const v = asNumber(ev(n.args[0])); | |
| return v == null || v < 0 ? null : Math.sqrt(v); | |
| } | |
| case "POWER": { | |
| const a = asNumber(ev(n.args[0])); | |
| const b = asNumber(ev(n.args[1])); | |
| if (a == null || b == null) return null; | |
| const out = Math.pow(a, b); | |
| return Number.isFinite(out) ? out : null; | |
| } | |
| case "EXP": { | |
| const v = asNumber(ev(n.args[0])); | |
| if (v == null) return null; | |
| const out = Math.exp(v); | |
| return Number.isFinite(out) ? out : null; | |
| } | |
| case "LN": { | |
| const v = asNumber(ev(n.args[0])); | |
| return v == null || v <= 0 ? null : Math.log(v); | |
| } | |
| case "SUM": { | |
| const vals = numericArgs(n.args, ev); | |
| return vals.reduce((a, b) => a + b, 0); | |
| } | |
| case "AVERAGE": { | |
| const vals = numericArgs(n.args, ev); | |
| return vals.length === 0 ? null : vals.reduce((a, b) => a + b, 0) / vals.length; | |
| } | |
| case "COUNT": | |
| return numericArgs(n.args, ev).length; | |
| case "COUNTA": { | |
| let c = 0; | |
| for (const a of n.args) { | |
| const v = ev(a); | |
| if (v != null && v !== "") c += 1; | |
| } | |
| return c; | |
| } | |
| case "MIN": | |
| case "MAX": { | |
| const vals = numericArgs(n.args, ev); | |
| if (vals.length === 0) return null; | |
| return n.fn === "MIN" ? Math.min(...vals) : Math.max(...vals); | |
| } | |
| case "IF": { | |
| const c = ev(n.args[0]); | |
| if (typeof c !== "boolean") return null; // no truthiness | |
| // LAZY: only the taken branch runs — IF({x} = 0, 0, 1/{x}) is the | |
| // guard pattern this exists for. | |
| if (c) return ev(n.args[1]); | |
| return n.args.length > 2 ? ev(n.args[2]) : null; | |
| } | |
| case "AND": | |
| case "OR": { | |
| // Strict booleans, evaluated lazily left to right (Excel short-circuits too). | |
| for (const a of n.args) { | |
| const v = ev(a); | |
| if (typeof v !== "boolean") return null; | |
| if (n.fn === "AND" && !v) return false; | |
| if (n.fn === "OR" && v) return true; | |
| } | |
| return n.fn === "AND"; | |
| } | |
| case "NOT": { | |
| const v = ev(n.args[0]); | |
| return typeof v === "boolean" ? !v : null; | |
| } | |
| case "IFERROR": { | |
| // Our errors ARE blanks (the one rule), so IFERROR is the blank-coalesce. | |
| const v = ev(n.args[0]); | |
| return v == null ? ev(n.args[1]) : v; | |
| } | |
| case "ISBLANK": { | |
| const v = ev(n.args[0]); | |
| return v == null || v === ""; | |
| } | |
| case "CONCATENATE": | |
| return n.args.map((a) => asText(ev(a))).join(""); | |
| case "LEFT": | |
| case "RIGHT": { | |
| const s = asText(ev(n.args[0])); | |
| const k = n.args.length > 1 ? asNumber(ev(n.args[1])) : 1; | |
| if (k == null || !Number.isInteger(k) || k < 0) return null; | |
| return n.fn === "LEFT" ? s.slice(0, k) : k === 0 ? "" : s.slice(-k); | |
| } | |
| case "MID": { | |
| const s = asText(ev(n.args[0])); | |
| const start = asNumber(ev(n.args[1])); | |
| const len = asNumber(ev(n.args[2])); | |
| if (start == null || len == null) return null; | |
| if (!Number.isInteger(start) || !Number.isInteger(len) || start < 1 || len < 0) | |
| return null; // Excel: MID is 1-based and refuses a 0 start | |
| return s.slice(start - 1, start - 1 + len); | |
| } | |
| case "LEN": | |
| return asText(ev(n.args[0])).length; | |
| case "TRIM": | |
| return asText(ev(n.args[0])).replace(/\s+/g, " ").trim(); | |
| case "UPPER": | |
| return asText(ev(n.args[0])).toUpperCase(); | |
| case "LOWER": | |
| return asText(ev(n.args[0])).toLowerCase(); | |
| case "PROPER": | |
| return asText(ev(n.args[0])).replace( | |
| /[A-Za-z]+/g, | |
| (w) => w[0].toUpperCase() + w.slice(1).toLowerCase() | |
| ); | |
| case "VALUE": { | |
| const v = ev(n.args[0]); | |
| return asNumber(v); | |
| } | |
| case "TEXT": { | |
| const v = asNumber(ev(n.args[0])); | |
| const fmt = ev(n.args[1]); | |
| if (v == null || typeof fmt !== "string") return null; | |
| return textFormat(v, fmt); | |
| } | |
| case "TODAY": | |
| return typeof env.today === "string" && dateParts(env.today) ? env.today : null; | |
| case "YEAR": | |
| case "MONTH": | |
| case "DAY": { | |
| const p = dateParts(ev(n.args[0])); | |
| if (!p) return null; | |
| return n.fn === "YEAR" ? p.y : n.fn === "MONTH" ? p.m : p.d; | |
| } | |
| case "DAYS": { | |
| // Excel argument order: DAYS(end, start). | |
| const end = dateParts(ev(n.args[0])); | |
| const start = dateParts(ev(n.args[1])); | |
| if (!end || !start) return null; | |
| return Math.round(dateSerial(end) - dateSerial(start)); | |
| } | |
| } | |
| return null; | |
| } | |
| default: | |
| return null; | |
| } | |
| } | |
| function cmpNums(op: "<" | "<=" | ">" | ">=" | "=" | "!=", l: number, r: number): boolean { | |
| switch (op) { | |
| case "<": return l < r; | |
| case "<=": return l <= r; | |
| case ">": return l > r; | |
| case ">=": return l >= r; | |
| case "=": return l === r; | |
| case "!=": return l !== r; | |
| } | |
| } | |
| /** | |
| * Evaluate a parsed formula over one row's values. Returns a FINITE number, a | |
| * STRING (text results, and booleans as "TRUE"/"FALSE"), or null (blank). | |
| * NEVER throws, whatever the inputs: the belt around the whole engine, because | |
| * a cell renderer that throws takes the grid with it. | |
| */ | |
| export function evalFormula( | |
| ast: FormulaAst, | |
| get: (key: string) => unknown, | |
| env: FormulaEnv = {} | |
| ): number | string | null { | |
| try { | |
| const v = evalNode(ast, get, env); | |
| if (typeof v === "number") return Number.isFinite(v) ? v : null; | |
| if (typeof v === "string") return v; | |
| if (typeof v === "boolean") return v ? "TRUE" : "FALSE"; | |
| return null; | |
| } catch { | |
| return null; | |
| } | |
| } | |
| /** | |
| * Dependency order for FORMULA-OVER-FORMULA (2026-07-31). Given every formula | |
| * field's source by key, returns the keys in an evaluation order where a | |
| * formula runs AFTER the formulas it references, plus the set caught in a | |
| * CYCLE — those evaluate to blank (their members never run), never to a stale | |
| * or half-updated number. A formula depending on a cyclic one still runs; the | |
| * cyclic ref simply reads blank. | |
| */ | |
| export function orderFormulas(sources: ReadonlyMap<string, string>): { | |
| order: string[]; | |
| cyclic: Set<string>; | |
| } { | |
| const deps = new Map<string, string[]>(); | |
| for (const [key, src] of sources) { | |
| const p = parseFormula(src); | |
| deps.set(key, p.ok ? p.refs.filter((r) => sources.has(r) && r !== key) : []); | |
| } | |
| const order: string[] = []; | |
| const state = new Map<string, 0 | 1 | 2>(); // 0 visiting-guard absent, 1 = on stack, 2 = done | |
| const cyclic = new Set<string>(); | |
| const visit = (key: string, stack: string[]): boolean => { | |
| const s = state.get(key); | |
| if (s === 2) return !cyclic.has(key); | |
| if (s === 1) { | |
| // Everything from the first appearance of `key` on the stack is the cycle. | |
| for (let i = stack.lastIndexOf(key); i < stack.length; i += 1) cyclic.add(stack[i]); | |
| return false; | |
| } | |
| state.set(key, 1); | |
| stack.push(key); | |
| for (const d of deps.get(key) ?? []) visit(d, stack); | |
| stack.pop(); | |
| state.set(key, 2); | |
| if (!cyclic.has(key)) order.push(key); | |
| return !cyclic.has(key); | |
| }; | |
| for (const key of sources.keys()) visit(key, []); | |
| return { order, cyclic }; | |
| } | |
| /** | |
| * Create-form validation: parse + check every ref against the fields that | |
| * exist. Referencing ANOTHER formula is allowed (evaluation is topological); | |
| * what is refused is a CYCLE — including the self-reference, its smallest | |
| * case. `formulaSources` carries every formula field's source so the check | |
| * can walk the graph; `selfKey` names the field being edited (absent on | |
| * create, where no cycle is possible yet). | |
| */ | |
| export function validateFormula( | |
| src: string, | |
| knownKeys: ReadonlySet<string>, | |
| formulaSources: ReadonlyMap<string, string>, | |
| selfKey?: string | |
| ): { ok: boolean; error?: string; refs: string[] } { | |
| const parsed = parseFormula(src); | |
| if (!parsed.ok) return { ok: false, error: parsed.error, refs: [] }; | |
| for (const k of parsed.refs) { | |
| if (!knownKeys.has(k) && !formulaSources.has(k)) | |
| return { ok: false, error: `{${k}} is not a field of this table`, refs: parsed.refs }; | |
| if (selfKey && k === selfKey) | |
| return { | |
| ok: false, | |
| error: "a formula cannot reference itself", | |
| refs: parsed.refs, | |
| }; | |
| } | |
| if (selfKey) { | |
| const all = new Map(formulaSources); | |
| all.set(selfKey, src); | |
| const { cyclic } = orderFormulas(all); | |
| if (cyclic.has(selfKey)) | |
| return { | |
| ok: false, | |
| error: "this formula would create a loop between formulas", | |
| refs: parsed.refs, | |
| }; | |
| } | |
| return { ok: true, refs: parsed.refs }; | |
| } | |