Search code examples
excelapioffice-jsadd-inexcel-addins

Get the ultimate non-formula precedents of a cell in Excel Addin Nodejs Api

The problem is we want to get the ultimate non-formula precedents of a cell. To be more clear precedents of precedents.

E.G.

A1      10
A2      =A1
A3      =A2

So for A3 the precedent is A2, but the ultimate non-formula precedent is A1 (there are no further precedents).

Right now we call getDirectPrecedent() and store a list. Then we call the same function for the list until we get non formula precedent cell.

Is there any optimised way to achieve this? Any native API function?

Solution

  • There is a Range.getPrecedents API currently (April 9th, 2021) in preview. This returns all the precedents for a Range, not just the immediate, direct precedents.

    While the API is not available in general availability, you can test it out in Script Lab. Here's a sample gist showing how to use the API with your sample data: https://gist.github.com/AlexJerabek/25c22d809e21c85175b81afd7b445198