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?

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));
        }
    });    
});

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.

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

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://docs.microsoft.com/en-us/javascript/api/excel/excel.worksheet?view=excel-js-preview#excel-excel-worksheet-onactivated-member

https://docs.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);
    });
}