loopable / web /src /sheet /a1.ts
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
a93637f verified
Raw History Blame Contribute Delete
16.6 kB
// 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) };
};