none
How to get formula from Excel spreadsheet cell via JavaScript API? RRS feed

  • Question

  • Is it possible to get the formula that has been entered into a spreadsheet cell via the JavaScript API for office? I've looked through all the APIs, and done some testing, but I only ever seem to be able to get the resulting computed value out, not the underlying formula. I don't seem to have a problem setting a cell to a formula.

    If this is possible in the API, could someone provide a short example? Thanks!

    Thursday, March 13, 2014 4:22 PM

Answers

  • I ever saw a similar question about getting formula from Excel spreadsheet. It seems that it's not supported.

    Can you read the formula from an Excel cell using JavaScript?

    Thanks,
    Tao

    Friday, March 14, 2014 7:30 AM
  • Hi Craig,

    Unfortunately I haven't found a way for you to do this with JavaScript only, but if you could put an UDF in the workbook, you'll have a workaround like this:

    UDF

    Public Function GetFormula(cel As Range) As String
    GetFormula = cel.Formula
    End Function
    


    JavaScript

    var cell = "E8";
    Office.context.document.setSelectedDataAsync("=GetFormula(" + cell + ")", function (asyncResult) {
      if (asyncResult.status != Office.AsyncResultStatus.Failed) {
        Office.context.document.getSelectedDataAsync("text", function (asyncResult) {
          if (asyncResult.status != Office.AsyncResultStatus.Failed) {
            // use asyncResult.value
          }
        });
      }
    });
    

    This code gets the formula in E8.

    Hopefully this helps.

    Tuesday, March 18, 2014 7:15 AM

All replies

  • I ever saw a similar question about getting formula from Excel spreadsheet. It seems that it's not supported.

    Can you read the formula from an Excel cell using JavaScript?

    Thanks,
    Tao

    Friday, March 14, 2014 7:30 AM
  • Thanks. That post is over a year old. It seems like a pretty severe limitation of the API if you can not actually get what is in a cell. Is there any way to confirm this?

    Friday, March 14, 2014 3:23 PM
  • Hi Craig,

    Unfortunately I haven't found a way for you to do this with JavaScript only, but if you could put an UDF in the workbook, you'll have a workaround like this:

    UDF

    Public Function GetFormula(cel As Range) As String
    GetFormula = cel.Formula
    End Function
    


    JavaScript

    var cell = "E8";
    Office.context.document.setSelectedDataAsync("=GetFormula(" + cell + ")", function (asyncResult) {
      if (asyncResult.status != Office.AsyncResultStatus.Failed) {
        Office.context.document.getSelectedDataAsync("text", function (asyncResult) {
          if (asyncResult.status != Office.AsyncResultStatus.Failed) {
            // use asyncResult.value
          }
        });
      }
    });
    

    This code gets the formula in E8.

    Hopefully this helps.

    Tuesday, March 18, 2014 7:15 AM
  • I had the same problem. A pure JavaScript solution without UDFs is possible with the Excel 2013 function FORMULATEXT. I'm using it to retrieve formulas from embedded Excel Online spreadsheets. A less elegant solution in that context is to browse the EWA DOM.
    Monday, May 12, 2014 10:30 PM
  • As Jeeped mentioned, Excel has native ISFORMULA and FORMULATEXT functions. You can create worker cells that will display the formula and simply return the data.

    //Bind Cells
    Office.context.document.bindings.addFromNamedItemAsync("A1:XFD1048576", "matrix", { id: 'objSelect'},function(e){
    
    });
    
    //Set Worker Cell
    Office.select('bindings#workerCell').setDataAsync([['=FORMULATEXT('+Cell+')']],
        {startRow: 0, startColumn:99},function (asyncResult){
    
    });
    
    //Get Function
    Office.select('bindings#objCell').getDataAsync({ coercionType: 'matrix', startRow: 0, startCol: 99, rowCount: 1, colCount: 1 }, 
            function (result) {
                console.log(result.value[0]);
        });
    Saturday, December 20, 2014 5:14 AM
  • There can be a pure Js implementation using worker cells.

    As Jeeped mentioned, Excel has native ISFORMULA and FORMULATEXT functions. You can create worker cells that will display the formula and simply return the data.

    //Bind Cells
    Office.context.document.bindings.addFromNamedItemAsync("A1:XFD1048576", "matrix", { id: 'objSelect'},function(e){
    
    });
    
    //Set Worker Cell
    Office.select('bindings#workerCell').setDataAsync([['=FORMULATEXT('+Cell+')']],
        {startRow: 0, startColumn:99},function (asyncResult){
    
    });
    
    //Get Function
    Office.select('bindings#objCell').getDataAsync({ coercionType: 'matrix', startRow: 0, startCol: 99, rowCount: 1, colCount: 1 }, 
            function (result) {
                console.log(result.value[0]);
        });


    Saturday, December 20, 2014 5:15 AM