CODEWITHSUNDEEP
office-jsoffice-addins
mehrandvd
mehrandvd

Reputation: 9116

How to get all formulas of a sheet in office.js

Having a sheet name say Sheet1, I want to have an array of all formulas used in cells of that sheet. Something like this:

Excel.run(function (ctx) {
    var formulas = getAllFormulasOfSheet(ctx, 'Sheet1')
    // or
    getAllFormulasOfSheet(ctx, 'Sheet1').then(function(result){
        var formulas = result;
    });

    // formulas should be like:
    ['SUM(A1:B3)', 'SUM(A3:C3)']
})

Upvotes: 2

Views: 1236

Answers (2)

Kim Brandl
Kim Brandl

Reputation: 13500

(Sudhi's answer is correct and was first, so it should be the "Accepted" answer. I'm adding this one to provide some supplemental info.)

As Sudhi's answer suggests, you should use the Worksheet.GetUsedRange() method to get the range of the worksheet that's actually been used, and then reference the formulas property of that range to identify the formulas that the range contains.

Please note, however, that the Range.formulas property will contain an array of arrays that has contents of all cells in the range -- not just the formulas in the range. For example, if the worksheet contains this data, where only the cells in range E3:E5 and cells in range C6:E6 contain formulas:

enter image description here

Then the Range.formulas property for the "used range" of the worksheet will return this JSON:

[
    [
        "Product",
        "Qty",
        "Unit Price",
        "Total Price"
    ],
    [
        "Almonds",
        2,
        7.5,
        "=C3*D3"
    ],
    [
        "Coffee",
        1,
        34.5,
        "=C4*D4"
    ],
    [
        "Chocolate",
        5,
        9.56,
        "=C5*D5"
    ],
    [
        "TOTAL",
        "=SUM(C3:C5)",
        "=SUM(D3:D5)",
        "=SUM(E3:E5)"
    ]
]

I've created a gist that you can use with Script Lab (a free add-in you can get here: aka.ms/getscriptlab) to see how this all works. Once you've installed the Script Lab add-in in Excel, open the Script Lab Code pane there, select Import from the menu, and then import this gist: https://gist.github.com/kbrandl/01858318da8cbdff606e8bba32145882.

Upvotes: 2

Sudhi Ramamurthy
Sudhi Ramamurthy

Reputation: 2478

Getting usedRange formulas may be more reasonable as you don't need the entire sheer's formula. Below script will result in 2-D array formula being returned for used range. [['=sum(a1:b2)', '=sum(a2:b2)']]. You can also load formulaR1C1 or formulaLocal if you need them. Retrieval of unbounded range is not supported (entire row, col, sheet). You have to call usedRange() method on them to get all useful cells.

async function getFormulas() {
    try {
        await Excel.run(async (context) => {
            const sheet = context.workbook.worksheets.getItem("Sample");
            const range = sheet.getUsedRange();
            // const range = sheet.getRange("B2:E6"); //if you need specific address. You can also use named item based fetch.
            range.load("formulas");

            await context.sync();

            console.log(JSON.stringify(range.formulas, null, 4));
        });
    }
    catch (error) {
        //handle error
    }
}

Upvotes: 5

Related Questions