How to identify the Excel Sheet changed event in office js api?

Question

We have an Excel office js add-in written on angular. It has different functionalities based on the sheet the user is in. When users switch the sheets of excel, how can the application know it, so it can change the UI to match the sheet's functionalities?

Hustler

Answer 1

UPDATE May 18 2017: With ExcelApi 1.2+, you can use a new syntax

The new syntax is as follows: context.workbook.onSelectionChanged.add(yourHandler);

You can find a full sample by using Script Lab, a free add-in for trying out Office Add-in snippets. One of the samples has a "Selection Changed" event snippet:

==================

Original answer:

As Michael Saunders said, you can use the selection change event. See the code below. Note that in this case I'm mixing the "Office 2013" syntax with the newer host-specific ("Excel." namespace) syntax of Office 2016. In the "ExcelApi 1.3" release that's coming in a couple months, we actually have a way for you to do this entirely using the new syntax, but that is currently only on the preview CDN, and may not work on your machine, depending on how recent your version of Ofice 2016 is. The code below, meanwhile, is going to work on any 2016 installation, RTM included.

Office.context.document.addHandlerAsync(Office.EventType.DocumentSelectionChanged, function() {
    Excel.run(function(context) {
        var sheet = context.workbook.worksheets.getActiveWorksheet();
        sheet.load("name")
        return context.sync().then(function() {
            console.log('You are now on sheet "' + sheet.name + '"');
        })
    }).catch(function(error) {
        console.log(error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
    });    
});

Gamone

Will try this. When is this event fired? Any performance issues if there multiple sheets? If this is the event fired in all the cell selections, it didn't work earlier due to performance issues. – Hustler

@michael-zlatkovsky-microsoft What's the API 1.3's approach to implementing this? – Picco

Answer 2

There isn't a sheet-changed event. However, one workaround would be to subscribe to DocumentSelectionChanged events, then check the user's active sheet each time to see if it changed.

Dix

Is this use case of different sheets having different functionality not supported by office js? – Hustler

I'm not sure that I understand - the suggested solution will indeed let you customize your add-in's functionality depending on the active sheet, using Office.js – Dix

Since the suggested answer need to call Excel.run in every cell change event, it didn't scale really well. So wondering if there is a better approach. – Hustler

Many Office.js add-ins fetch data from Office every time the user's selection changes. For an example, you can install the popular Wikipedia add-in from the Office Store. As you'll see, performance continues to be fast even though the add-in is making frequent cross-process requests. – Dix

May be my understanding is wrong. From, the documentation Excel.run didn't look like a lightweight operation. – Hustler

That sounds good. For us, we were already doing a lot of process to identify the cell changed, additional business functionalities based on changed data. May be that caused it to slow down entire process. – Hustler

I just tried calling Excel.run to get the current sheet name with perf markers. It took 4 milliseconds. That should be much faster than necessary for your scenario. The performance of Excel.run mostly depends on the complexity of the operation that you're asking Excel to do. – Dix

Great. That's 4ms in every time user move to a new cell. Binding Selection changed is also being used by us to identify the cell changed. So it may be adding up for us. – Hustler

Answer 3

You can add handler for context.workbook.worksheets.onActivated

Excel.run((context) => {
  context.workbook.worksheets.onActivated.add(({ worksheetId }) => {
    console.log('Selected worksheet', worksheetId)
  })

  return context.sync()
    .then(function () {
        console.log("Event handler successfully registered");
    });
}).catch(errorHandlerFunction);

More info here

Superscribe

Answer 4

Here is an example which uses both onActivated and onChanged and a handle event function.

Note: I noticed that onChanged also included the Worksheet ID, so you can just use that if you need to use onChanged, but my assumption is it uses more resources.

Resources:

https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/events-worksheet.yaml

https://learn.microsoft.com/en-us/javascript/api/excel/excel.worksheet?view=excel-js-preview#excel-excel-worksheet-onactivated-member

https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-events#register-an-event-handler

Office.onReady(async function (info) {
    //Office.addin.setStartupBehavior(Office.StartupBehavior.load);
    //CommandsFunc(event)
    await Excel.run(async (context) => {
        const worksheet = context.workbook.worksheets.getItem("Sheet1");
        worksheet.onChanged.add(handleChange);
        worksheet.onActivated.add(handleChange);

        await context.sync();
    });
}); 

---

async function handleChange(event) {
    await Excel.run(async (context) => {
        await context.sync();
        var ws_id = event.worksheetId
        const worksheet = context.workbook.worksheets.getItem(ws_id);
        worksheet.load("name")
        await context.sync();
        console.log("Change type of event: " + event.changeType);
        console.log("Address of event: " + event.address);
        console.log("Source of event: " + event.source);
        console.log("The activated worksheet ID is: " + ws_id);
        console.log("The activated worksheet Name is: " + worksheet.name);
    });
}

Deon