Excel function parsing in office-js

146 views Asked by At

I would like to write an office-js excel addin that

  1. parses the used-range to find all formulas of the form "=foo(arg)" and
  2. 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();
    });
  });
}); 
0

There are 0 answers