Part of #269 — spec 0009 (specs/0009-unnest-to-let.md). Depends on #270 (parser).
Pure logic + unit tests: turn an AST into a leaf-first =LET(...) of named steps. No UI.
Scope:
- Step extraction — every function-call node and every binary-operator-expression node (other than the root) becomes a step. Unary expressions and postfix
%/# stay inline. Atomic leaves stay inline (they are /Refactor's job).
- Root as body — the outermost node becomes the LET body (not a named step), with its child references rewritten to step names.
- Leaf-first ordering — each step precedes every use of it (topological).
- No CSE in v1 — repeated identical sub-expressions each get their own step.
- Naming — function-call steps auto-name from the lowercased function name (
sumsq1, sqrt1, round1); operator steps calcN. Smallest numeric suffix that avoids collision with other steps, existing binding names, and workbook defined names; a base used once still gets 1.
- Include-toggle inlining — un-including a step substitutes its RHS back into the parent and re-parents its children to the grandparent.
- Emit via
FormulaFormatter.AppendLet; result must round-trip through LetParser.
Worked example (from spec)
=ROUND(SQRT(SUMSQ(XLOOKUP(H94, t[City], t[[X-Coordinates]:[Y-Coordinates]]) - $I$92:$J$92)) * 100, 0)
->
=LET(
xlookup1, XLOOKUP(H94, t[City], t[[X-Coordinates]:[Y-Coordinates]]),
calc1, xlookup1 - $I$92:$J$92,
sumsq1, SUMSQ(calc1),
sqrt1, SQRT(sumsq1),
calc2, sqrt1 * 100,
ROUND(calc2, 0))
Tests
Part of #269 — spec 0009 (
specs/0009-unnest-to-let.md). Depends on #270 (parser).Pure logic + unit tests: turn an AST into a leaf-first
=LET(...)of named steps. No UI.Scope:
%/#stay inline. Atomic leaves stay inline (they are/Refactor's job).sumsq1,sqrt1,round1); operator stepscalcN. Smallest numeric suffix that avoids collision with other steps, existing binding names, and workbook defined names; a base used once still gets1.FormulaFormatter.AppendLet; result must round-trip throughLetParser.Worked example (from spec)
=ROUND(SQRT(SUMSQ(XLOOKUP(H94, t[City], t[[X-Coordinates]:[Y-Coordinates]]) - $I$92:$J$92)) * 100, 0)->
Tests
=A1+B1-> body only, zero steps)calcNnaming with collision suffixingLetParser