Doubt about getCellDependents

[nathipg]

Hello!

I'm using hyperformula version 1.3.1 and I'm having a strange behavior from getCellDependents.

I made this sample to show it: https://jsfiddle.net/r1q2nbc9/1/

Based on the sample, cell E12 has this formula:

=SUMIFS(E2:E9,A2:A9,A12,B2:B9,B12,C2:C9,C12)

If I run the following command to get the dependents of E2:

formulaPlugin.engine.getCellDependents({ sheet: 0, col: 4, row: 1, });

I have the this return:

Which is a range from E2 to E9, why is this the function return?

To me, the return should be an array with the address to E12 cell.

What am I doing wrong?

[sequba]

Hi @nathipg!

It may seem illogical but this is the intended behaviour of getCellDependents. Most ranges are kept in the dependency graph as separate nodes for performance reasons. Read more about ranges in the dependency graph

In your case, you can run getCellDependents twice to get the address of E12 and E13 like this:

const level1Dependents = formulaPlugin.engine.getCellDependents({
  sheet: 0,
  col: 4,
  row: 1,
});

const level2Dependents = formulaPlugin.engine.getCellDependents(level1Dependents[0]);

console.log('Level 1 dependents:', level1Dependents);
console.log('Level 2 dependents:', level2Dependents);

Prints:

"Level 1 dependents:", [{
  end: {
    col: 4,
    row: 8,
    sheet: 0
  },
  start: {
    col: 4,
    row: 1,
    sheet: 0
  }
}]
"Level 2 dependents:", [{
  col: 4,
  row: 11,
  sheet: 0
}, {
  col: 4,
  row: 12,
  sheet: 0
}]

Does it solve your issue?

[nathipg]

Thank you! That solved my problem.
One question, is it possible to have more than 2 level dependents?

[sequba]

Yes, it is. If you want to get all the dependants of a cell you can use the BFS-like algorithm as described here: #889 (comment)

[nathipg]

Thanks! I'm gonna see it

[sequba]

Related issues for the future reference:
#934
#930
#931

[sequba]

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