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

Question

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.

Answer 1

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