Is there a way to determine if a cell reference exists?

[alvarovillafane]

In my app, I have the use case when the formula input and the calculation are done in different places.

One of the validations that I'd like to do after entering the formula is to check that all the references within the formula are valid.

For example:

    const hf = HyperFormula.buildEmpty({ licenseKey: 'gpl-v3' });
    hf.addSheet('Sheet1');
    hf.addSheet('Sheet2');
    hf.setSheetContent(0, [['=1+2', '=A1+B1']]);
    hf.setSheetContent(1, [['=TODAY()', '=1+2']]);

And then do something like:

hf.canIAddFormula({sheet: 0, col:1, row:0}, "=A1+9") => valid as A1 exists on Sheet1

hf.canIAddFormula({sheet: 0, col:1, row:0}, "=C4+9") => invalid as C4 does not exists on Sheet1

hf.canIAddFormula({sheet: 0, col:1, row:0}, "=Sheet2!B1") =>  valid as Sheet2!B1 exists

hf.canIAddFormula({sheet: 0, col:1, row:0}, "=Sheet2!B6") =>  invalid as "Sheet2!B6" does not exists

hf.canIAddFormula({sheet: 0, col:1, row:0}, "=SUM(Sheet2!A1:B1)") =>  valid as "Sheet2!A1:B1" range exists

hf.canIAddFormula({sheet: 0, col:1, row:0}, "=SUM(Sheet2!A1:B9)") =>  invalid as "Sheet2!A1:B9" range does not exists

etc...

Would you mind giving me any suggestions on how we could achieve a similar functionality?

Thanks!

[wojciechczerniak]

In spreadsheet ranges always exist, they are always valid. You have maxRows and maxColumns in the config, but those limitations are mostly for performance reasons. The assumption is that those ranges exist but are EMPTY_CELL. Depending on the software vendor those may be evaluated as 0 (zero).

Maybe you need something as ISBLANK?