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

Question

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.

Kim Brandl Kim Brandl · Accepted Answer · 2017-11-08T19:42:37

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.

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

1 Answers