CODEWITHSUNDEEP
exceloffice-js
Pavan Divekar
Pavan Divekar

Reputation: 449

Not able to read values from the named range using Office.js API

I am new to Excel Add-ins and Office.js. I am trying to explore to code add-in projects.

Here i'm trying to read values from a Named Range. Firstly I'm trying to get range object and then load values for that range.

I'm not able to get those values but this code neither throws an error nor prints values to the console:

Excel.run(function (ctx) {
    var range = ctx.workbook.names.getItem("MyRange").getRange();
    range.load("values");
    return ctx.sync(function () {
        console.log("required range values -> ");
        console.log(range.values);
    });
}).catch(function (e) {
    console.log("error info is" + e.message);
});

Here I already have MyRange in my sheet.

Upvotes: 2

Views: 492

Answers (1)

Kim Brandl
Kim Brandl

Reputation: 13480

In the code that you've posted, you're missing the .then(...) after return ctx.sync().

The following (JavaScript) code snippet gets the named range ExpensesHeader and then writes the values of that range to the console.

Excel.run(function (ctx) {
    var sheet = ctx.workbook.worksheets.getItem("Sample");
    var myNamedItem = sheet.names.getItem("ExpensesHeader");
    var myNamedRange = myNamedItem.getRange();

    myNamedRange.load("values");

    return ctx.sync()
        .then(function () {
            console.log("values: " + JSON.stringify(myNamedRange.values));
        });
});

And here's the same snippet, but in TypeScript:

Excel.run(async (ctx) => {
    const sheet = ctx.workbook.worksheets.getItem("Sample");
    const myNamedItem = sheet.names.getItem("ExpensesHeader");
    const myNamedRange = myNamedItem.getRange();

    myNamedRange.load("values");

    await ctx.sync();

    console.log("values: " + JSON.stringify(myNamedRange.values));
});

You can quickly and easily try these code snippets yourself in Excel by using Script Lab (https://aka.ms/getscriptlab). Simply install the Script Lab add-in (free), then choose "Import" in the navigation menu, and use the following GIST URL: https://gist.github.com/kbrandl/da3f56d9510329d5ea04bf00ced5c870.

Upvotes: 2

Related Questions