CODEWITHSUNDEEP
excelms-officeoffice-jsexcel-addinsexcel-online
BuZz
BuZz

Reputation: 17485

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

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.

Upvotes: 2

Views: 572

Answers (3)

Brian Gonzalez
Brian Gonzalez

Reputation: 1354

This is now possible. In the examples below I use cells A1:C1. Cell A1 contains the value of 1. Cell B1 contains the formula =A1 + 1 and cell C1 contains the formula =B1. The active cell used is cell B2.

This example uses and modifies Microsoft's code on their documentation. Their documentation used precedents. So I've added additional example using dependents.

Note: If you do not use an active cell that has both precedents and dependents (like B1 in this case), the code will raise an error:

$("#run").click(() => tryCatch(run));

async function run() {
  await Excel.run(async (context) => {
    let range = context.workbook.getActiveCell();

    let precedents = range.getPrecedents();
    let dependents =  range.getDependents();

    let directPrecedents = range.getDirectPrecedents();
    let directDependents = range.getDirectDependents();

    range.load("address");
    
    precedents.areas.load("address");
    directPrecedents.areas.load("address");
    
    dependents.areas.load("address");
    directDependents.areas.load("address");

    await context.sync();

    for (let i = 0; i < precedents.areas.items.length; i++) {
      console.log(`  ${precedents.areas.items[i].address}`);
    }

    for (let i = 0; i < directPrecedents.areas.items.length; i++) {
      console.log(`  ${directPrecedents.areas.items[i].address}`);
    }

    for (let i = 0; i < dependents.areas.items.length; i++) {
      console.log(`  ${dependents.areas.items[i].address}`);
    }

    for (let i = 0; i < directDependents.areas.items.length; i++) {
      console.log(`  ${directDependents.areas.items[i].address}`);
    }
  });
}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
    console.error(error);
  }
}

the difference between the direct and non-direct methods is that direct only returns direct cell dependents / precedents. The non-direct methods also return indirect cell references. E.g. if cell B1 refers to A1 and cell C1 refers to B1, getDependents() for cell A1 will return 2 cells for the direct (B1) and indirect (C1) references. Whereas getDirectDependents() for cell A1 will only return 1 cell (B1)

You can read more here

Upvotes: -1

Charles Williams
Charles Williams

Reputation: 23540

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.

Upvotes: 2

Michael Saunders
Michael Saunders

Reputation: 2668

Why do you want to know a cell's precedents? Is it to implement formula-like functionality so that your add in can enable a function such as =JEROMESUM(A1,A7)?

If so, the JavaScript API has recently added this capability in preview for you to try, called "custom functions": https://aka.ms/customfunctions

Upvotes: -1

Related Questions