Anyway to obtain cell Precedents and Dependents in Excel Javascript API?

Question

Unlike the desktop Excel APIs, which contain a "Precedents" and a "Dependents" on the Range object, the short answer is no for the Excel Javascript APIs, which brings me here.

Is there a way or third-party piece of code to circumvent this huge lack from Microsoft ? If I can't navigate the formula tree, there is just not much useful I can add in terms of add-ins for Excel Online.

Javascript API for Excel

Office Online

UPDATE: to add some context, I've been writing auditing add-ins in C# in the past, to evaluate formula complexity, perform advanced reconciliation of formulas, reconstruct formulas differently, etc... I'm trying to convert a small piece of it, as a javascript prototype add-in, to see whether Excel online can handle it.

Charles Williams Charles Williams · Accepted Answer · 2017-11-15T15:42:24

I looked at this earlier in the year.
The only way at the moment would be to parse out the references from the formula, which is non-trivial (but achievable) given the wide variety of possible reference types.

For a starter look here http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

And even then you would still have limitations for functions returning references like OFFSET, INDIRECT etc since the JS API does not currently have any Evaluate methods.
And to be comprehensive you would also need to handle things like Implicit Intersection etc.

The latest version of the JS API does contain methods for handling Named Formulas, which is a good step forward so a parser would also be able to find references in those.

Anyway to obtain cell Precedents and Dependents in Excel Javascript API?

2 Answers