How to get all cellPrecedents & cellDependents from one address?

[MartinDawson]

Hyperformula has these 2 functions:

getCellPrecedents & getCellDependents.

However they only get the immediate children/parents.

I need to get all the children/parents from an address in an efficient way.

The current hyperformula functions such as GraphBuilder.ts processDependencies expect all dependencies to be given up front, not just one node.

[izulin]

Hyperformula has these 2 functions:

getCellPrecedents & getCellDependents.

However they only get the immediate children/parents.

I need to get all the children/parents from an address in an efficient way.

The current hyperformula functions such as GraphBuilder.ts processDependencies expect all dependencies to be given up front, not just one node.

processDependencies adds dependencies to the dependency graph, and it is ok to just add immediate dependencies there. Algorithms behind handling dependencies take care of dependencies of dependencies etc.

[MartinDawson]

Yes but what I am asking is how do we get all of the cell precedents starting from a single vertex/address?

Here's an example:

  it('all precedents should be gotten from one vertex', () => {
    const [engine] = HyperFormula.buildFromArray({ cells: [
      [{ cellValue: 1 }, { cellValue: 2 }, { cellValue: 3 }],
      [{ cellValue: '=SUM(A1:B1)' }, { cellValue: '=SUMSQ(A1:B1)' }],
      [{ cellValue: '=A2+B2' }],
    ]})

    expect(engine.getAllCellPrecedents(adr('A3'))).toEqual([adr('A2'), adr('B2'), adr('A1'), adr('B1')])
  })

Currently, something like this is not possible in HF as far as I am aware?

I'm wondering how I can add this feature if it doesn't exist.

[bardek8]

That's right, currently HF supports only single-hop precedents/dependents.

In order to get them all, you can apply BFS-like approach:

 1      AllCellPrecedents={start}
 2      let Q be an empty queue
 4      Q.enqueue(start)
 5      while Q is not empty do
 6          cell := Q.dequeue()
 7          S := getCellPrecedents(cell)
 9          for all cells c in S do:
10              if c is not in AllCellPrecedents then:
11                  insert w to AllCellPrecedents
12                  Q.enqueue(c)

[MartinDawson]

Thanks, that works well.

I see that getCellPrecedents returns simpleCellAddress and a simpleCellRange

For example this test returns true with the above BFS code:

    const [engine] = HyperFormula.buildFromArray({ cells: [
      [{ cellValue: 1 }, { cellValue: 2 }],
      [{ cellValue: '=SUM(A1:B1)' }],
    ]})
    
   expect(engine.getAllCellPrecedents(adr('A2'))).toEqual([simpleCellRange(adr('A1'), adr('B1')), adr('A1'), adr('B1')])

Why does A2 have both a simpleCellRange precedent AND the equivalent in simpleCellAddress precedents?

I would have thought that it should do this instead:

    const [engine] = HyperFormula.buildFromArray({ cells: [
      [{ cellValue: 1 }, { cellValue: 2 }],
      [{ cellValue: '=SUM(A1:B1)' }],
    ]})
    
   expect(engine.getAllCellPrecedents(adr('A2'))).toEqual([simpleCellRange(adr('A1'), adr('B1'))])

Instead of duplicating addresses.

Do simpleCellRanges always have their equivelent in simpleCellAddresses in this case? If so then I could just ignore simpleCellRanges from the return.

If not then I'm thinking to exclude simpleCellRanges addresses when the equivelent in simpleCellAddresses is already their.

[sequba]

That's right, currently HF supports only single-hop precedents/dependents.

In order to get them all, you can apply BFS-like approach

I created an issue (feature request) for that: #931 but it's not on our current roadmap so the BFS-like approach will be described in the docs (#934)

Why does A2 have both a simpleCellRange precedent AND the equivalent in simpleCellAddress precedents?

This page describes the key concepts of keeping ranges in the dependency graph. I also created an issue to explain it more accurately in the documentation: #930

[sequba]

@MartinDawson A thorough explanation of these functions can be found in the Dependency Graph guide and API reference for getCellDependents and getCellPrecedents.