CODEWITHSUNDEEP
javascriptexcel
Malhar Soni
Malhar Soni

Reputation: 21

Excel Online | Return Cell Formula - Javascript

For Excel Online, I want to get the formula contained inside a cell.

Currently, I'm using getDataAsync() but it only returns the final result but not the formula:

Office.select('bindings#ObjSelect').getDataAsync({ coercionType: 'matrix', startRow: 0, startCol: 0, rowCount: 1, colCount: 1 }, 
    function (result) {
        console.log(result.value);
});

Is there a way to return the formula?

Upvotes: 1

Views: 361

Answers (2)

user1108069
user1108069

Reputation: 513

You can also use range's formulas property, reference here

    Excel.run(function (ctx) {
        var selectedRange = ctx.workbook.getSelectedRange();
        selectedRange.load('formulas'); // IMPORTANT, it tells Excel to load formulas

        return ctx.sync().then(function () {
            var formula = '';
            if (selectedRange.formulas !== undefined)) {
                formula = selectedRange.formulas[0][0];
            }

            if (formula.length > 0) {
                // do something here with the 'formula'
            }
        });
    });

Upvotes: 0

Malhar Soni
Malhar Soni

Reputation: 21

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

And that will sort it :)

Upvotes: 1

Related Questions