Skip to content

Array formulas in the #SPILL! state don't re-spill when space becomes available #1785

Open

Description

@Tobiadefami

An array formula that returns #SPILL! keeps returning it after the cells around it change so that the result would fit. It only recovers when the formula is re-entered, updateConfig is called, or rebuildAndRecalculate() runs.

const hf = HyperFormula.buildFromArray([[null], ['=SEQUENCE(4)']], {licenseKey: 'gpl-v3', maxRows: 4})
// A2: #SPILL! (A2:A5 would pass maxRows)

hf.removeRows(0, [0, 1])
// the formula is now in A1, where A1:A4 fits, but A1 is still #SPILL!

hf.setCellContents({sheet: 0, col: 0, row: 0}, '=SEQUENCE(4)')
// A1:A4 = 1, 2, 3, 4

The same happens when the spill was blocked by a value and the blocking cell is cleared:

const hf = HyperFormula.buildFromArray([['=SEQUENCE(3)'], ['x']], {licenseKey: 'gpl-v3'})
// A1: #SPILL!

hf.setCellContents({sheet: 0, col: 0, row: 1}, null)
// A1 is still #SPILL!

It also stays #SPILL! after:

  • redo of the removeRows above
  • removeColumns (maxColumns: 4, =SEQUENCE(1,4) in B1, then remove column A)
  • moveRows of another row out from above the formula
  • a dependency change (=ARRAYFORMULA(SEQUENCE(4)*B2) in A2, then remove row 1 and change B1)

Undoing the write that blocked the spill does recover.

Cause: when isThereSpaceForArray returns false, setNoSpace() (FormulaVertex.ts) replaces the vertex's array with an ErroredArray of size ArraySize.error() (1×1, isRef), so the vertex no longer knows its real size. Evaluator.recomputeFormulaVertexValue then returns setNoSpace() whenever vertex.array.size.isRef is set, without calling isThereSpaceForArray, so the space check never runs again.

Expected: after a structural change, a cell write, or a dependency change, an array formula in the #SPILL! state should check for space again and spill if the result now fits. Excel Online does this in all three cases:

Setup Change Excel Online
=SEQUENCE(3) in A1048575 → #SPILL! (sheet edge) delete rows above spills 1, 2, 3
=SEQUENCE(1,3) in XFC1 → #SPILL! (sheet edge) delete column B moves to XFB1, spills 1, 2, 3 across XFB1:XFD1
=SEQUENCE(3) in D10, x in D11 → #SPILL! clear D11 spills 1, 2, 3 in D10:D12

Scope: the value-blocked case is pre-existing on develop. #1774 adds a second way into this state: a result that would extend past maxRows/maxColumns returns #SPILL!, so any array near the bottom or right edge of the sheet can get stuck. The paired tests for #1774 (hyperformula-tests#57) cover recovery only by editing the formula and by updateConfig. Their undo and dependency-change tests check that the error stays when the array still doesn't fit. None covers a change that makes room without re-entering the formula.

All cases reproduced on fix/HF-135 at ea7542586. Found by @marcin-kordas-hoc during the review of #1774.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions