handsontable / handsontable/hyperformula
Named Expression returns #CYCLE! in changes
Nobody has claimed this yet.
- Dominant language
- TypeScript
- Stars
- 2.8k
- Forks
- 171
- Avg merge
- 1d 22h
- Merged PRs (30d)
- 7
Description
Description
When Named Expression is added the changes are calculated. With an absolute address, the changes will be calculated for real values that are part of the workbook. But when named expression contains relative address, it will be calculated relatively to sheet -1 where the named expressions are stored.
Named Ranges in XL and GS are limited to absolute addresses. We could validate expression if it contains only the absolute addresses. Which is fine, but limited to ranges. Named Expressions are much more.
Only Libre Calc has full Named Expressions support. And this works as expected, named expression is relative to the sheet where it was used.
This complicates the changes returned when a relative named expression is added and as a result, we get CYCLE error.
Credits: @budnix
Steps to reproduce
it('basic usage with global named expression', () => {
const engine = HyperFormula.buildFromArray([
['42'],
])
const changes = engine.addNamedExpression('myName', '=Sheet1!A1+10', undefined)
// [ ExportedNamedExpressionChange { name: 'myName', newValue: 52 } ]
// OK! Calculation is based on Sheet1
expect(engine.getNamedExpressionValue('myName')).toEqual(52)
})
it('basic usage with global named expression', () => {
const engine = HyperFormula.buildFromArray([
['42'],
])
const changes = engine.addNamedExpression('myName', '=A1+10', undefined)
// [ ExportedNamedExpressionChange { name: 'myName', newValue: DetailedCellError { error: [CellError], value: '#CYCLE!' } } ]
// NOT OK! We get an error because address is relative
expect(engine.getNamedExpressionValue('myName')).toEqual(52)
})
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start at the addNamedExpression and getNamedExpressionValue entry points, then reproduce the two inline TypeScript cases from the issue. Done means the relative A1 expression is evaluated against the sheet where it is used, returns 52, and no longer produces #CYCLE! in the changes.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- typescript
- Domain
- backend
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100