You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Array formulas in the #SPILL! state don't re-spill when space becomes available #1785
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.
consthf=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:
consthf=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.
Activity
docs(HF-135): record array size differences and known spill limits