CODEWITHSUNDEEP
javascriptms-officeoffice365office-js
Liang Guo
Liang Guo

Reputation: 33

How to name and reference an Excel range using office.js

I'm developing an Excel add-in using office.js library, and need to create some named ranges to track and access later.

However according to the API documentation:

NamedItemCollection: https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/nameditemcollection.md

NamedItem: https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/nameditem.md

There is no method for creating named items.

Is there a way to name and reference an Excel range using office.js? Thanks.

Upvotes: 3

Views: 2915

Answers (3)

Ricky Patnaik
Ricky Patnaik

Reputation: 21

  async function addNameToHeader() {
    await Excel.run(async(context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const headerRange = sheet.getRange("A1:D1");

        sheet.names.add("ExpensesHeader", headerRange);
        const namedItems = sheet.names.load("name, type");

        await context.sync();
    });
}

async function formatRangeByName() {
    await Excel.run(async(context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const headerRowName = sheet.names.getItemOrNullObject("ExpensesHeader");
        headerRowName.load();
        await context.sync();

        if (headerRowName.value) {
            const headerRange = headerRowName.getRange();
            headerRange.format.fill.color = "red";
        } else {
            console.log("No named item created for the range.");
        }

        await context.sync();
    });
}

Upvotes: 1

Konstantinos Cheilakos
Konstantinos Cheilakos

Reputation: 129

there is the Excel.NamedItemCollection which provides an add() method that generates Named references

https://learn.microsoft.com/en-us/javascript/api/excel/excel.nameditemcollection?view=excel-js-preview

Upvotes: 1

Michael Saunders
Michael Saunders

Reputation: 2668

There isn't a way to name a range in Excel through Office.js. Only the user can name a range, through the Excel UI.

The good news is that there's a different way to accomplish the same goal:

If you want to maintain a reference to a range, there's no need to name it; it already has a name like Sheet1!A1:B10.

If you want to maintain a reference to a range even if the user adds/deletes rows/columns before it or inside it, use a Binding:

var myBindings = Office.context.document.bindings;
var myAddress = "Sheet1!A1:B10";
myBindings.addFromNamedItemAsync(myAddress, "matrix", {id:"myBind"}, function(result){});

And then when you want to access the range later, you can:

Excel.run(function (ctx) { 
    var foundBinding = ctx.workbook.bindings.getItem("myBind");
    var myRange = foundBinding.getRange();
    myRange.load('values');
    return ctx.sync().then(function() {
        console.log(myRange.values);
    });
});

-Michael Saunders, PM for Office add-ins

Upvotes: 4

Related Questions