Answered by:
How to get formula from Excel spreadsheet cell via JavaScript API?
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!
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 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, March 21, 2014 1: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.
 Proposed as answer by C. Zhang Tuesday, March 18, 2014 8:27 AM
 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, March 21, 2014 1:30 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 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, March 21, 2014 1: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.
 Proposed as answer by C. Zhang Tuesday, March 18, 2014 8:27 AM
 Marked as answer by Fei XueMicrosoft employee, Moderator Friday, March 21, 2014 1:30 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.

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]); });

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]); });