Update cell values on Sheet rename

[BrianHung]

Is there a utility function to get a list of cell addresses that depend on a sheet? For example, 'Sheet1' gives back [sheetId, row, col] if 'Sheet1!A1' exists as a value?

Use case is when 'renameSheet' is called, there doesn't seem to be a way to update all cells which used the previous sheet name instead of reffing out.

I would think this information is stored or calculated somewhere in the 'DependencyGraph'.

[sequba]

Hi @BrianHung!

renameSheet function updates the sheet's dependencies automatically. Here is a unit test that demonstrates it:

it('should update the sheet dependencies', () => {
  const engine = HyperFormula.buildFromSheets({'OldSheetName': [[42]], 'DependantSheet': [['=OldSheetName!A1']]})

  engine.renameSheet(0, 'NewSheetName')

  expect(engine.getCellFormula({ sheet: 1, row: 0, col: 0 })).toEqual('=NewSheetName!A1');
})

Is this sufficient for your use case?

[BrianHung]

Is there a way to get a list of all the cells that are updated on a sheet rename?

The way I'm passing in data into hyperformula is more "controlled", where I have a separate datastore / object outside of hyperformula which I pass in via buildFromSheets. The separate object is for rendering an editable table with react.

On sheet renaming, I would like to update that object so when engine.renameSheet is called, the table can be re-rendered with the new cell value =NewSheetName!A1.

[BrianHung]

Shorter question: is the valuesUpdated listener called when renameSheet is called?

The line that confuses me from the doc is

Occurs when values in a specified location are changed and cause recalculation.

since values are changed but don't necessarily cause recalculation (?).

[BrianHung]

@sequba

[sequba]

@BrianHung Sorry for the late response,

Explanation

Is there a way to get a list of all the cells that are updated on a sheet rename?

I would think this information is stored or calculated somewhere in the 'DependencyGraph'.

This information can be extracted from the dependency graph but currently there is no public method for that. You can file a feature request for that and we will consider adding it to the scope of the next release.

Shorter question: is the valuesUpdated listener called when renameSheet is called?

No, it is not. The formula representation that is used internally references sheets by their ID so renaming a sheet do not cause any updates to cell formulas.

Idea 1: getCellDependents

You can get a list of dependencies of a sheet by iterating over its cells and calling getCellDependents for each of them:

it('getCellDependents should return reversed dependencies across sheets', () => {
  const engine = HyperFormula.buildFromSheets(
    {
      'DataSheet': [[1, 2, 3, '=A1']],
      'DependentSheet': [['=DataSheet!A1', '=DataSheet!A1', '=DataSheet!A1+DataSheet!B1', '=A1']],
    }
  )

  const dataSheetId = engine.getSheetId('DataSheet')
  const dependentSheetId = engine.getSheetId('DependentSheet')

  expect(engine.getCellDependents(adr('A1', dataSheetId))).toEqual([
    adr('D1', dataSheetId),
    adr('A1', dependentSheetId),
    adr('B1', dependentSheetId),
    adr('C1', dependentSheetId)
  ])
  expect(engine.getCellDependents(adr('B1', dataSheetId))).toEqual([adr('C1', dependentSheetId)])
  expect(engine.getCellDependents(adr('C1', dataSheetId))).toEqual([])
  expect(engine.getCellDependents(adr('D1', dataSheetId))).toEqual([])
})

Idea 2: text lookup

I have a separate datastore / object outside of hyperformula which I pass in via buildFromSheets. The separate object is for rendering an editable table with react.

If your datastore allows for that you can iterate over all the cells and check for Sheet1! substring.

[BrianHung]

This information can be extracted from the dependency graph but currently there is no public method for that. You can file a feature request for that and we will consider adding it to the scope of the next release.

Will do!

[sequba]

For the future reference: #937