Get new named cell with Excel JS API

Question

We're using Excel JS API to get changed on worksheets.

Is there an event to get new named cells?

The only way that I see is to store the named cell collection (using NamedItemCollection) & compare it whenever we detect a change to worksheet.

For the moment I've implemented a function that gets all named cell with address and store them.

var namedItemsColl : NamedCell[] = [];
const namedItems : Excel.NamedItemCollection = ctx.workbook.names.load('items');
await ctx.sync();

for (let i = 0; i < namedItems.items.length; i++) {
      var it= namedItems.items[i].getRange().load('address');
      await ctx.sync();
      var namedCell : NamedCell = new NamedCell;
      namedCell.name = namedItems.items[i].name;
      namedCell.range = it.address;
      namedItemsColl.push(namedCell);
        }

Each time I get a change, I get the named cells collection again and compare it to the store one.

But that takes around 5s each time only to get the entire collection (with 1000 named cells).

Thanks

Denton Thomas · Accepted Answer · 2025-03-21 22:39:15Z

Looks like this was a similar issue in VSTO - there isn't an event for adding/removing named ranges.

As a workaround, this page suggests hooking the SheetSelectionChange event and monitoring the number of named ranges in the collection (storedCount = namedItems.getCount()). Then just watch for getCount() > storedCount. This method avoids the time-consuming loop when checking unrelated sheet changes.

For porting that solution to office.js, I would attach the NamedRangeCollection handler to onSelectionChanged as in this solution. From your question, I don't know how you're hooking the sheet change.

Other: Try monitoring just the last item in the collection (Excel.NamedItemCollection.items). This is an Excel.NamedItem[], so it -may- be possible to monitor and watch for a different/new last item in the array. Sorry this isn't definite - I don't have a working Excel Add-In environment, and I can't tell from the reference whether you can access array members directly. I'll update if I can confirm.

Items in the NamedRangeCollection are in alphabetical order by name (so checking the first or last items won't work). You have an await inside the loop, however. Perhaps that is avoidable. This works for me, albeit inside the event handler:

async function onWorksheetCollectionSelectionChange(args: Excel.WorksheetSelectionChangedEventArgs) {
    
  await Excel.run(async (context) => {

    const namedItems: Excel.NamedItemCollection = context.workbook.names.load('items');
    await context.sync();
    let itemCount = namedItems.items.length;

    console.log(`Event: address ${args.address}`);
    console.log(`named items count ${itemCount}`);

    for (let i = 0; i < itemCount; i++)
      console.log(`    item: ${namedItems.items[i].name}`)

  });
}

FlorentS · Accepted Answer · 2025-03-24 08:02:26Z

Actually I've found a workaround that without performance issue. I keep the collection in memory and compare it each time there is a change.

But I cannot base this on the count of named items as some can be added/deleted.

I get all items like this

async function getNamedItems(ctx:Excel.RequestContext):Promise<NamedCell[]>{
    var namedItemsColl : NamedCell[] = [];
    const namedItems : Excel.NamedItemCollection = ctx.workbook.names.load('items');
    
    await ctx.sync();

    for (let i = 0; i < namedItems.items.length; i++) {
        var namedCell : NamedCell = new NamedCell;
        namedCell.name = namedItems.items[i].name;
        namedCell.range = namedItems.items[i].value;
        namedItemsColl.push(namedCell);
    }

    return namedItemsColl;
}

And here is how I compare :

var namedItems: NamedCell[] =[];

    await getNamedItems(ctx).then(result => namedItems = result);

for (let i = 0; i < namedItems.length; i++) {            
        if(namedCollection.find(x => x.name == namedItems[i].name) ==null)
        {
            namedcoll = new CellChangeDataClient;
            namedcoll.changeType = Excel.DataChangeType.rangeEdited;
            var nameItem : CellNameUpdate = new CellNameUpdate;
            nameItem.change='added';
            nameItem.name = namedItems[i].name;
             nameItem.range = namedItems[i].range;
            namedcoll.namedItems = nameItem;
            changesArray.push(namedcoll);
        }
    }
    var tempCell: NamedCell|undefined;

for (let i = 0; i < namedCollection.length; i++) {            
        tempCell= namedItems.find(x => x.name == namedCollection[i].name);
        if(tempCell == undefined)
        {
            namedcoll = new CellChangeDataClient;
            namedcoll.changeType = Excel.DataChangeType.rangeEdited;
            var nameItem : CellNameUpdate = new CellNameUpdate;
            nameItem.change='deleted';
            nameItem.name = namedCollection[i].name;
            namedcoll.namedItems=nameItem;
            changesArray.push(namedcoll);
        }
        else if(tempCell.range != namedCollection[i].range)
        {
            namedcoll = new CellChangeDataClient;
            namedcoll.changeType = Excel.DataChangeType.rangeEdited;
            var nameItem : CellNameUpdate = new CellNameUpdate;
            nameItem.change='updated';
            nameItem.name = tempCell.name;
            nameItem.range = tempCell.range;
            namedcoll.namedItems=nameItem;
            changesArray.push(namedcoll);
        }
    };

Collectives™ on Stack Overflow

Get new named cell with Excel JS API

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related