#SPILL error should recalculate on changes to result area (before: "Setting a null value in the ARRAYFORMULA expansion area does not update the NoSpaceForArrayResult error[Bug]")

[IMSupperkaka]

Description

The following code does not run as expected

const hf = HyperFormula.buildEmpty({
  licenseKey: "gpl-v3"
});

// Add a new sheet and get its id.
const table1 = hf.addSheet("table1");

hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: hf.getSheetId(table1)
  },
  [[1,2],['=ARRAYFORMULA(A1:B1 > 1)', 1]]
);

// When set null at B2 does not recompute formula cell
hf.setCellContents(
  {
    row: 1,
    col: 1,
    sheet: hf.getSheetId(table1)
  },
  [[null]]
);

Expected behavior is that A2 recaculate when set null at B2

Video or screenshots

No response

Demo

https://codesandbox.io/s/nostalgic-wozniak-imse6d?file=/src/index.js

HyperFormula version

2.4.0

Your framework

No response

Your environment

chrome 113

[adrianszymanski89]

Hi @IMSupperkaka

After the initial investigation, it doesn't exactly look like a bug in the HyperFormula itself, as when I set the null value in the initial data, the formula calculates correctly.

However, I will need to check it with our developer, but he's currently on vacation and will be back by the end of the month. I'll update you once he is back.

[sequba]

@IMSupperkaka A formula cell recomputes only on updates to its dependencies. In your example, the cell A2 recomputes only when A1 or B1 changes. Unfortunately, #SPILL errors have no custom behavior in situations like that.

If you want to trigger recomputation manually you can use rebuildAndRecalculate method or simply setCellContents:

hf.rebuildAndRecalculate();
// OR
hf.setCellContents(addr, [[hf.getCellSerialized(addr)]]);

Does it solve your issue?

[IMSupperkaka]

rebuildAndRecalculate may have a relatively large performance overhead, setCellContents should solve my problem, but this seems to be a bug, is there any plan to fix it?

Another problem is that the expression of the array formula is not quite the same as that of Excel. For example, in Excel, the Filter function will only report an error if there is something else in the filtered array result, but we will check the size of the original array for additional values.

[sequba]

@IMSupperkaka

this seems to be a bug, is there any plan to fix it?

Yes, I agree it is a bug. I'm adding it to our backlog.

Another problem is that the expression of the array formula is not quite the same as that of Excel. For example, in Excel, the Filter function will only report an error if there is something else in the filtered array result, but we will check the size of the original array for additional values.

It seems like a separate matter unrelated to this one, so please open a new issue and provide a demo.