FH_jesse
Feb 16, 2023Copper Contributor
Status:
New
Excel addin API: provide mechanism for consuming formulas with evaluated params
When writing a taskpane app that is a companion to a custom function, it's useful to be able to consume the function parameters for a given cell. I'm able to retrieve a list of functions within a cell/range using the formulas property, but these are the literal strings as they show up in the UI and are thus not easily consumable.
One work-around is to parse each formula, find the function(s) of concern, and evaluate each function parameter. This can get quite expensive if they contain non-primitive values, and there's no direct API for evaluating formulas.
Another potential work-around is to store each function parameter somewhere when the custom function is called, but there can be multiple instances within a cell and there's no way of knowing which one is currently being called.
Ideally there'd be an API for getting a list of functions for a given Excel.Range, that'd provide the function name, and each evaluated parameter. For example:
Excel.run(async (context) => {
const range = context.workbook.getSelectedRange();
range.load({ address: true, functions: { name: true, resolvedArgs: true } });
await context.sync();
for (let i = 0; i < range.functions.length; i++) {
let fn = range.functions[i];
console.log(`${range.address} ${fn.name}`, fn.resolvedArgs);
// e.g. if range.address is A1, and A1 = `My.CustomFunction(1, 'Two', CONCAT('TH', 'REE'), B1)`, and B1 = `4`
// then `A1 My.CustomFunction, [1, 'Two', 'THREE', 4]` would be logged to the console
}
});
No CommentsBe the first to comment