I would like to write an office-js excel addin that
- parses the used-range to find all formulas of the form "=foo(arg)" and
- populates an array with the addresses of the occurrences and the evaluation of arg. arg could for example be a cell reference such as $A$1.
I managed the first part, but how would I implement part two? I understand office-js doesn't allow evaluate. As workaround to evaluate I considered replacing "=foo(arg)" with "=arg", reloading the value and then putting the original "=foo(arg)" back into the cell.
Is there any cleaner solution? With the solution described above, how would I keep track of the objects to load and modify?
My code to find foo()
Office.onReady(function() {
Excel.run(function (context) {
var sheet = context.workbook.worksheets.getActiveWorksheet();
var rng = sheet.getUsedRange();
rng.load("formulas");
var foundCells = new Array();
return context.sync().then(function () {
for(var i = 0; i < rng.formulas.length; i++) {
var f = rng.formulas[i];
for(var j = 0; j < f.length; j++) {
if (/^=foo\(.*\)/i.test(f[j])) {
foundCells.push({i: i, j: j, val: f[j]});
}
}
}
document.write(JSON.stringify(foundCells, null, 4));
return context.sync();
});
});
});