OFFSET is instantly converted to a reference

[AMBudnik]

Description

The OFFSET formula is instantly converted to a reference

Dataset [1, 45, "=OFFSET(A1, 0, 1)"]
getCellValue result 45 (referenced cell value)
getCellFormularesult =B1

Steps to reproduce

1. Go to the demo
2. Check the console

Demo

https://codesandbox.io/s/throbbing-lake-kqqjm

Your environment

• HyperFormula version: 0.4.0
• Browser Name and version: Chrome 91
• Operating System: Windows 10

[bardek8]

This is a known limitation of HF - we are not able to compute references dynamically and hence we handle OFFSET differently - we need to compute the reference during the parsing phase, to correctly build the graph of dependencies between the cells.

Hence the formula is not stored and processed as others, and the behavior you've mentioned.

This would be handled together when moving to the dynamic collection of dependencies, that would e.g. avoid the CYCLE error issue.

Though probably we should describe in known-limitations.md the OFFSET function in more detail, that we need to be able to compute the reference during the parsing phase - e.g. we cannot have =OFFSET(A1, C3, 0).

[AMBudnik]

Thanks for the insights, @bardek8

[AMBudnik]

@bardek8 OFFSET is also not listed under getRegisteredFunctionNames. Is that what you meant by

Hence the formula is not stored and processed as others

?

[bardek8]

By different processing I mean that this is the only function that we fully process during the parsing phase. All other functions are evaluated during the interpretation, can operate on the values of other cells, are listed under getRegisteredFunctionNames etc.

As for the OFFSET:

• we only allow static numbers in the arguments other than the first one, e.g. we can have =OFFSET(F16, 1, 0) but not =OFFSET(F16, 1, B5)
• we do not store AST of the formula with OFFSET - we simply keep the reference it corresponds to
• this is the only function explicitly handled in the parsing phase: rule and the heuristic

[AMBudnik]

Thank you. Now everything's clear.