cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Excel JavaScript Office API evaluate formula

Excel JavaScript Office API evaluate formula
14

Upvotes

Upvote
 Jan 11 2023
2 Comments (2 New)
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
    */
  }
  // ...

 

 

Comment
Comments
ry_mac_202
Copper Contributor
‎Apr 23 2024 08:50 AM
‎Apr 23 2024 08:50 AM

Yes, we absolutely need a way to evaluate formulas programmatically.  Entering formulas into blank cells to read their values is a hack, and one that violates multiple best practices for Excel development.  For example, when changing contents of a cells, you want to suspend screenUpdating.  But to read the value in a cell you have to call context.sync(), which turns screenUpdating back on.  So, when you need to evaluate multiple formulas, there is effectively no way suspend screenUpdating.  Also, changing cell contents can trigger application events and calculations that the developer needs to be cognizant of.  And, the blank cell hack probably has a performance hit in some scenarios.

johannesboyne
Brass Contributor
‎Apr 24 2024 05:30 AM
‎Apr 24 2024 05:30 AM

100%! @ry_mac_202 adds some important points!

Would be great to get some further attention from the Microsoft product team!

Similar Ideas
No similar ideas