Closed BrianHung closed 9 months ago
Hi @BrianHung, thanks for reporting this issue. I can confirm it is a bug, so we'll work on it in one of the upcoming releases.
Apparently, it occurs only when sheet table1
is empty, so a workaround for that would be to initialize sheet1
with non-empty array:
hf.setCellContents(
{
row: 0,
col: 0,
sheet: hf.getSheetId(table1)
},
[['']]
);
Note to self: make sure other array functions work correctly in a similar scenario.
Other array functions like VLOOKUP
also break, as you noted.
@sequba COUNTIF
also break
Hi @sequba , I was doing some implementation and testing, and the issue also appears and breaks when referencing a sheet in which whose content is not initialized just yet 🤔 .
const hfInstance = HyperFormula.buildEmpty({ licenseKey: "gpl-v3" });
const sheetOne = [""];
const sheetTwo = ["=COUNTIF(sheetThree!A:A, test)"];
const sheetThree = [""];
hfInstance.addSheet("sheetOne");
hfInstance.addSheet("sheetTwo");
hfInstance.addSheet("sheetThree");
hfInstance.setSheetContent(0, [sheetOne]);
hfInstance.setSheetContent(1, [sheetTwo]);
hfInstance.setSheetContent(2, [sheetThree]);
see https://codesandbox.io/s/mystifying-ardinghelli-o136jr?file=/src/index.js:42-448
Any alternative option to make this work? because the workaround using ''
on the sheetThree it doesn't work 🤔
Thanks.
Suspending evaluation works as a workaround: https://codesandbox.io/s/unruffled-silence-3zs70q?file=/src/index.js And using buildFromSheets also works: https://codesandbox.io/s/wonderful-shannon-fsnxfc?file=/src/index.js
Although yeah, I think hfInstance.setSheetContent(1, [sheetTwo])
should first #REF out and then be valid after hfInstance.setSheetContent(2, [sheetThree])
.
Thanks @BrianHung , the issue with those workarounds is that I initialise the sheets dynamically as they load, and don't have all the content at the start. So can't suspend evaluation/buildFromSheets easily.
Just noticed that if I initialized the sheets before with an empty string, it also works as a workaround, instead of using suspend/resume evaluation.
There is any drawback on doing it like this?
import HyperFormula from "hyperformula";
const hfInstance = HyperFormula.buildEmpty({
licenseKey: "gpl-v3"
});
const sheetOne = [""];
const sheetTwo = ["=sheetOne!A1", '=COUNTIF(sheetThree!X:X, "test")'];
const sheetThree = [""];
hfInstance.addSheet("sheetOne");
hfInstance.addSheet("sheetTwo");
hfInstance.addSheet("sheetThree");
hfInstance.setSheetContent(0, [[""]]);
hfInstance.setSheetContent(1, [[""]]);
hfInstance.setSheetContent(2, [[""]]);
hfInstance.setSheetContent(0, [sheetOne]);
hfInstance.setSheetContent(1, [sheetTwo]);
hfInstance.setSheetContent(2, [sheetThree]);
console.log(hfInstance.getAllSheetsSerialized());
console.log(hfInstance.getAllSheetsValues());
https://codesandbox.io/s/keen-taussig-58hfqf?file=/src/index.js:337-453
@vmalvaro I also confirm it is working, and as it does not seem to do anything else than use the official API, it does not seem to be harmful in any way.
The workaround doesn't work perfectly @sequba.
When there are no rows to insert in the sheet, and we need to use the COUNTA
function on a column, it gives incorrect results if the column contains null
or empty string values. In such cases, if we intend to accurately count the empty cell values in the column, the workaround will yield incorrect results. It's important to note that similar issues may also arise with other aggregation functions when using the workaround.
=COUNTA("sheetOne!A:A")
will return 1 but it should return 0 logically as there are no rows.
Can we please prioritise and resolve this issue in the upcoming release?
Hi @gittysachin
In order to ensure a timely release, it is crucial to address the remaining two issues within the current release cycle, which is planned to conclude at the end of May. It is important to note that your reported issue will be thoroughly considered for inclusion in one of the upcoming releases. We understand the significance of adhering to the release schedule and are committed to delivering a product of the highest quality that meets your expectations. Your patience and understanding during this process are greatly appreciated.
Increasing impact to High
Thanks for the quick update, @sequba @AMBudnik. Eagerly looking forward to the resolution!
Another great news @BrianHung This issue is also fixed in v 2.6.1!
CC @gittysachin @alvarovillafane
Description
The following code errors with
Incorrect array size
Expected behavior is that
table2!A1
evaluates to#N/A
since no match occurs. For some reason,table1!A:A
is parsed as aCELL_RANGE
instead of aCOL_RANGE
, like in=SUM(table1!A:A)
.Steps to reproduce
https://codesandbox.io/s/spring-cherry-jx071t?file=/src/hyperformulaConfig.js