handsontable / handsontable/hyperformula

Named Expression returns #CYCLE! in changes

Open
#362 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Named Expressions To Be Discussed Verified
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.