Excel JavaScript Office API evaluate formula

Excel JavaScript Office API evaluate formula
7

Upvotes

Upvote

 Jan 11 2023
0 Comments 
New

Currently, it is not possible to pass a formula to the Office API and receive an evaluation of it.

There are workarounds, like writing to an empty cell, or writing to a scoped name. Both solutions are not directly pretty.

 

Ideal API:

 

  // ...
  await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    const evaluatedFormula = await sheet.evaluateFormula(`=SUM(A1:A5,Sheet2!B1:B5)`)
    // best possible return value
    /*
    {
      "value": 165,
      "type": number,
      "cells_included_in_calculation": ["Sheet1!A1:A5","Sheet2!B1:B5"],
      "precedents": ["Sheet1!A1:A5","Sheet2!B1:B5"] // can deviate from cells included, in case of formulas like SUMIF / SUMIFS / COUNTIF / COUNTIFS / INDEX / ...
    }
    => in case of an error, it could simply return one
    */
  }
  // ...