Support for partial infinite ranges (mixed-type ranges) e.g. A2:A

[oodavid]

Description

• A:A - works
• A2:A99 - works
• A2:A - causes #!ERROR, I think this is unexpected (it works in excel / google sheets)

The demo shows this behaviour

Video or screenshots

No response

Demo

https://stackblitz.com/edit/sgkxpfpa?file=index.ts,index.html

HyperFormula version

3.2.0

Your framework

No response

Your environment

Chrome latest

[AMBudnik]

Thanks for flagging this, @oodavid. I can see why this felt unexpected, especially since the same reference works in Excel and Google Sheets.

This behavior is a current HyperFormula limitation. The documentation explains that HyperFormula does not treat single cells as ranges in the same way as Excel or Google Sheets, so a reference like A2:A is not supported.

Here is the relevant documentation:
https://hyperformula.handsontable.com/guide/cell-references.html#range-references

If helpful, I would be glad to suggest a workaround that fits your use case.

[oodavid]

Thanks @AMBudnik - I hadn't spotted that in the docs.

Our workaround is to detect endless / open-ended ranges with regex and append 9999. For our use-case we're only interested in open-ended columns.

Logically it might be best to use the sheet size rather than 9999 - I'm not sure if there's any performance downside to how we currently handle this

[AMBudnik]

I asked @sequba to share his feedback on the performance. He's out for a short vacation but will be available around the end of this week/beginning of next week.

[sequba]

Logically it might be best to use the sheet size rather than 9999 - I'm not sure if there's any performance downside to how we currently handle this

There is a small memory overhead for creating such big range references. For each empty cell in a range, HyperFormula creates an EmptyCellVertex internally. I checked that an empty 10k-cell range uses ~3 MB of heap memory.

To answer your question, the optimal solution would be to use the actual size of the non-empty part of the sheet, but if 3 MB overhead per each such range is acceptable for you, then A2:A9999 does the job ;)

@oodavid, we'll consider supporting mixed-type ranges