// A1 addressing on the client (wave 49, W49-T03, ruling R5). // // This file owns ONE job: what a formula's text becomes when it is dragged to another cell. The // EVALUATION of that formula is the server's (`aios-web/api/formula_runtime.py`, `AUTHORITY = // "server"`), and nothing here computes a value. // // ⭐ THE RULE, WHICH IS EXCEL'S AND IS NOT NEGOTIABLE: a `$` PINS the part it precedes. // `B4` both parts shift `$B4` column pinned, row shifts // `B$4` row pinned, column shifts `$B$4` neither shifts // A sheet prefix (`Sheet2!C3`) never shifts; it names a tab, not a position. /** `A` -> 1, `Z` -> 26, `AA` -> 27. Spreadsheet base-26, which has no zero digit. */ import { ERROR_REF, transformSheetFormula } from "./formulaEval"; export const colToIndex = (letters: string): number => { let n = 0; for (const ch of letters.toUpperCase()) n = n * 26 + (ch.charCodeAt(0) - 64); return n; }; /** The inverse of {@link colToIndex}. */ export const indexToCol = (index: number): string => { let n = Math.floor(index); if (n < 1) return ""; let out = ""; while (n > 0) { const rem = (n - 1) % 26; out = String.fromCharCode(65 + rem) + out; n = Math.floor((n - 1) / 26); } return out; }; /** W57-T01: a whole column from its 1-based index, e.g. 1 -> "A:A". */ export const columnRangeA1 = (col: number): string => { const column = indexToCol(col); return `${column}:${column}`; }; /** W57-T01: a whole row from its 1-based index, e.g. 1 -> "1:1". */ export const rowRangeA1 = (row: number): string => `${row}:${row}`; export interface CellRef { sheet: string; colAbs: boolean; col: string; rowAbs: boolean; row: number; } const REF_SOURCE = "(?:(?:'([^']{1,64})'|([A-Za-z_][A-Za-z0-9_]{0,63}))!)?(\\$?)([A-Za-z]{1,3})(\\$?)([1-9][0-9]{0,6})"; /** Parse one reference, or null. Exported so a caller can validate without shifting. */ export const parseRef = (text: string): CellRef | null => { const m = new RegExp(`^${REF_SOURCE}$`).exec(String(text || "").trim()); if (!m) return null; return { sheet: m[1] || m[2] || "", colAbs: m[3] === "$", col: m[4].toUpperCase(), rowAbs: m[5] === "$", row: Number(m[6]), }; }; /** * A bare sheet name the grammar accepts unquoted. Anything else has to wear quotes. * * ⛔ THIS WAS A BUG BEFORE W50-T20 AND IT ONLY SHOWED UP ON A SHEET WITH A SPACE IN ITS NAME. * `formatRef` printed the name raw, so a fill or a re-anchor turned `'Q1 plan'!A1` into * `Q1 plan!A1`, which `REF_SOURCE` cannot parse: the reference stopped being a reference and the * server answered `bad_formula` for a formula the person never touched. */ const BARE_SHEET = /^[A-Za-z_][A-Za-z0-9_]{0,63}$/; export const formatRef = (ref: CellRef | string): string => { if (typeof ref === "string") return ref; const body = `${ref.colAbs ? "$" : ""}${ref.col}${ref.rowAbs ? "$" : ""}${ref.row}`; if (!ref.sheet) return body; const name = BARE_SHEET.test(ref.sheet) ? ref.sheet : `'${ref.sheet.replace(/'/g, "")}'`; return `${name}!${body}`; }; /** * Shift ONE reference by a row and column delta, honouring `$`. * * ⚠ A shift that would leave the grid is CLAMPED to row 1 / column A rather than wrapping or * going negative. Excel reports an error there; clamping keeps the text a valid address so the * server answers with an ordinary `unknown_ref` instead of a parse failure, which is the better * of the two for a person dragging a fill handle by accident. */ export const shiftRef = (ref: CellRef, dRow: number, dCol: number): CellRef => ({ ...ref, col: ref.colAbs ? ref.col : indexToCol(Math.max(1, colToIndex(ref.col) + dCol)), row: ref.rowAbs ? ref.row : Math.max(1, ref.row + dRow), }); /** * ⭐⭐ THE ONE SCANNER (W50-T20). Walk a formula's TEXT and hand every REFERENCE to `rewrite`, * copying everything else through byte for byte. * * ⛔ QUOTED TEXT IS NEVER TOUCHED. `="B4 is the total"` must survive unchanged, so the scan tracks * string state exactly as the server's `_rewrite` does. A regex over the whole string would * quietly edit prose, and the damage would only surface as a wrong label on someone's report. * * ⛔⛔ IT IS EXTRACTED RATHER THAN COPIED, AND THAT IS THE POINT OF THIS FUNCTION EXISTING. Drag * fill and structural re-anchoring are two DIFFERENT rules over the SAME text, and a second * hand written scanner would be a second opinion about which of `LOG10(`, `'Q1 plan'!A1` and * `"B4 is the total"` is a reference. One scan, two callers. */ export const rewriteRefs = ( formula: string, rewrite: (ref: CellRef) => CellRef | string, rewriteRange?: (from: CellRef, to: CellRef, separator: string) => string, ): string => { const src = String(formula || ""); const scan = new RegExp(REF_SOURCE, "gy"); let out = ""; let i = 0; let quote: string | null = null; while (i < src.length) { const c = src[i]; if (quote) { out += c; if (c === quote) quote = null; i += 1; continue; } if (c === '"' || c === "'") { // A leading quote could open a STRING or a quoted sheet name. Only a sheet name is followed // by a closing quote and then `!`, so look ahead before deciding. const close = src.indexOf(c, i + 1); const isSheet = c === "'" && close > i && src[close + 1] === "!"; if (!isSheet) { quote = c; out += c; i += 1; continue; } } const prev = i ? src[i - 1] : ""; const boundary = !/[A-Za-z0-9_$]/.test(prev); if (boundary && /[A-Za-z$']/.test(c)) { scan.lastIndex = i; const m = scan.exec(src); if (m && m.index === i) { const after = src.slice(m.index + m[0].length); // The same collision the server guards: `LOG10(` has the shape of a reference. A `(` after // the token means it is a function name, so it is copied through untouched. if (!/^\s*\(/.test(after)) { const ref = parseRef(m[0]); if (ref) { // Only the optional range caller consumes a pair. Other callers keep their scan. const separator = rewriteRange && /^\s*:\s*/.exec(after)?.[0]; if (rewriteRange && separator) { scan.lastIndex = m.index + m[0].length + separator.length; const end = scan.exec(src); if (end && !/^\s*\(/.test(src.slice(end.index + end[0].length))) { const last = parseRef(end[0]); if (last) { out += rewriteRange(ref, last, separator); i = end.index + end[0].length; continue; } } } out += formatRef(rewrite(ref)); i = m.index + m[0].length; continue; } } } } out += c; i += 1; } return out; }; /** * Drag-fill: rewrite every reference in a formula's TEXT by the given delta. * * A fill copies the formula to another cell, so every unpinned part shifts by the same amount * wherever it points. That is what makes it different from a structural insert, which shifts by * POSITION. */ export const fillFormula = (formula: string, dRow: number, dCol: number): string => { const src = String(formula || ""); if (!dRow && !dCol) return src; return transformSheetFormula(src, dRow, dCol); }; export type RefMove = "copy" | "cut"; /** * Copy or cut a formula to another cell, using the same reference scan as drag-fill. * * A COPY walks every unpinned reference by the block delta. A CUT is a move, so its formula * arrives verbatim: shifting it would change which cells the moved formula reads. */ export const moveFormula = ( formula: string, dRow: number, dCol: number, mode: RefMove, ): string => mode === "cut" ? String(formula || "") : fillFormula(formula, dRow, dCol); // ═════════════════════════════════════════════════════════════════════════════════════════════ // ⭐⭐ W50-T20 — STRUCTURAL RE-ANCHORING, AND WHY IT IS NOT A FILL. // // ⛔⛔ THE TRAP, SAID FIRST, BECAUSE REUSING `shiftRef` HERE LOOKS OBVIOUSLY RIGHT AND IS WRONG. // A FILL shifts EVERY reference in the formula by the SAME delta, because the formula itself // moved. A structural INSERT moves nothing; it moves the CELLS a reference points at, and only // the ones at or below the insertion point. So the shift is decided PER REFERENCE, by comparing // that reference's own position against the insertion point: // // insert one row at row 4 `=B2` stays `=B2` (above the insert: nothing moved) // `=B4` becomes `=B5` (at or below: the value slid down) // // Handing this to `fillFormula(formula, 1, 0)` would turn `=B2` into `=B3` and point it at a cell // nobody touched. That is a wrong number with no error, on a sheet somebody trusts. // // SDLC 61 R3 (2026-09-15) REVERSED THE EARLIER DECISION TO HOLD `$` AGAINST STRUCTURE. // The owner's Sales dashboard broke when one inserted column left every `$` reference pointing // at the wrong column. `$` now shifts on a structural insert or delete, matching Excel: the // referenced cells moved, so every reference must follow them. `$` still pins against a FILL // and a COPY, which is the only thing it ever meant in Excel. This replaces the earlier // deliberate divergence so an anchored reference cannot silently read a different cell. // [[two-gates-can-assert-opposite-things]] // // ⭐ CROSS TAB IS THE SAME SWEEP, KEYED ON THE NAME. A reference with no sheet prefix means the // tab the formula lives on; one with a prefix means that tab wherever it lives. So the rewrite // runs over EVERY tab's formulas and each reference is matched against the tab that actually // changed. Comparison is case insensitive, as Excel's is. // ═════════════════════════════════════════════════════════════════════════════════════════════ /** One structural change: `delta` rows or columns inserted (positive) or deleted (negative). */ export interface StructuralShift { /** The NAME of the tab whose rows or columns moved. */ sheet: string; axis: "row" | "col"; /** The 1-based row number or column index the band starts at. */ at: number; /** How many were inserted (> 0) or deleted (< 0). */ delta: number; } const sameName = (a: string, b: string): boolean => String(a || "").trim().toLowerCase() === String(b || "").trim().toLowerCase(); /** * Where one 1-based index lands after `delta` were inserted or deleted at `at`. * * R49 retires the deleted-reference clamp: both grammars already parse `#REF!` as an error * token, so it cannot cause the feared `bad_formula`. A deleted index returns null; only a * range with surviving cells may clamp its deleted endpoint and shrink. */ const shiftIndex = (value: number, at: number, delta: number): number | null => { if (value < at) return value; if (delta > 0) return value + delta; const past = at - delta; // the first index BELOW the deleted band return value < past ? null : value + delta; }; /** * Re-anchor one formula for a structural insert or delete. * * @param ownSheet the NAME of the tab this formula lives on, so a bare reference knows what it * is bare ABOUT. Passing the wrong one silently re-anchors the wrong tab's references. */ export const shiftForStructure = ( formula: string, shift: StructuralShift, ownSheet: string, ): string => { if (!shift.delta) return String(formula || ""); const index = (ref: CellRef): number => shift.axis === "row" ? ref.row : colToIndex(ref.col); const atIndex = (ref: CellRef, value: number): CellRef => shift.axis === "row" ? { ...ref, row: value } : { ...ref, col: indexToCol(value) }; const rewrite = (ref: CellRef): CellRef | string => { if (!sameName(ref.sheet || ownSheet, shift.sheet)) return ref; const moved = shiftIndex(index(ref), shift.at, shift.delta); return moved === null ? ERROR_REF : atIndex(ref, moved); }; const rewriteRange = (from: CellRef, to: CellRef, separator: string): string => { const sheet = from.sheet || ownSheet; if (to.sheet && !sameName(sheet, to.sheet)) { return formatRef(rewrite(from)) + separator + formatRef(rewrite(to)); } // A bare right endpoint inherits the left endpoint's sheet, not the formula's tab. if (!sameName(sheet, shift.sheet)) return formatRef(from) + separator + formatRef(to); const first = index(from), last = index(to); const low = Math.min(first, last), high = Math.max(first, last); if (low >= shift.at && high < shift.at - shift.delta) return ERROR_REF; const move = (value: number): number => shiftIndex(value, shift.at, shift.delta) ?? (value === low ? shift.at : shift.at - 1); return formatRef(atIndex(from, move(first))) + separator + formatRef(atIndex(to, move(last))); }; // Insertions retain the existing per-reference behavior, as does every scanner caller // that supplies no range callback (including reference rewrites used for copying). return rewriteRefs(String(formula || ""), rewrite, shift.delta < 0 ? rewriteRange : undefined); }; /** * ⭐⭐ S67-T09 (QI5, D-3285) — A CUT CELL TAKES EVERY REFERENCE TO IT ALONG, AS EXCEL DOES. * * A move relocates the block `col0:col1 x row0:row1` (1-based, inclusive) on `sheet` by `dRow` / * `dCol`. A reference that points INTO the block follows it, `$` or not (Excel re-points absolute * references on a cut too); a range follows only when BOTH ends lie inside the block, so a range * the block merely overlaps is left as it was. Any other reference is untouched. */ export interface CellMove { /** The NAME of the tab the block moved on. */ sheet: string; col0: number; row0: number; col1: number; row1: number; dRow: number; dCol: number; } export const shiftForMove = (formula: string, move: CellMove, ownSheet: string): string => { const src = String(formula || ""); if (!move.dRow && !move.dCol) return src; const inside = (ref: CellRef, sheet: string): boolean => { if (!sameName(ref.sheet || sheet, move.sheet)) return false; const col = colToIndex(ref.col); return ref.row >= move.row0 && ref.row <= move.row1 && col >= move.col0 && col <= move.col1; }; const moved = (ref: CellRef): CellRef => ({ ...ref, row: ref.row + move.dRow, col: indexToCol(colToIndex(ref.col) + move.dCol), }); const rewrite = (ref: CellRef): CellRef => (inside(ref, ownSheet) ? moved(ref) : ref); const rewriteRange = (from: CellRef, to: CellRef, separator: string): string => { const sheet = from.sheet || ownSheet; // A bare right endpoint inherits the left endpoint's sheet, not the formula's tab. if (inside(from, ownSheet) && inside(to, to.sheet ? ownSheet : sheet)) { return formatRef(moved(from)) + separator + formatRef(moved(to)); } return formatRef(from) + separator + formatRef(to); }; return rewriteRefs(src, rewrite, rewriteRange); }; /** * R5's other half: a cross-tab reference FOLLOWS a rename. * * ⛔ IT IS A SECOND, SEPARATE SWEEP AND NOT A SIDE EFFECT OF THE FIRST. A rename changes WHICH * NAME a reference wears and nothing about where it points; a structural change is the reverse. * Folding them together would mean a rename could never happen without an insert, and the day * somebody needs one alone the other would fire too. * * ⚠ A BARE REFERENCE IS LEFT BARE. On the renamed tab itself a bare `B4` still means "this tab", * which is still true after the rename, so writing the new name in would only add noise. */ export const renameSheetRefs = (formula: string, from: string, to: string): string => { if (!from || !to || sameName(from, to)) return String(formula || ""); return rewriteRefs(String(formula || ""), (ref) => ref.sheet && sameName(ref.sheet, from) ? { ...ref, sheet: to } : ref); }; /** The delta between two addresses, for a fill driven by dragging from one cell to another. */ export const deltaBetween = (from: string, to: string): { dRow: number; dCol: number } => { const a = parseRef(from); const b = parseRef(to); if (!a || !b) return { dRow: 0, dCol: 0 }; return { dRow: b.row - a.row, dCol: colToIndex(b.col) - colToIndex(a.col) }; };