Office-js: Unstable behaviour of onSelectionChanged

[catchingyu]

I've registered an onSelectionChanged event handler, which mimics the behaviour of cell picker, on current active worksheet in a Taskpane application, but the handler is occasionally triggered.

I tried to debug with F12/IEChooser and found once the tool is open, the event handler is triggered perfectly fine 100% of time. The same behaviour happens even when the handler is registered on workbook.

I also notice that it helps to trigger event handle to move mouse cursor back to the application Taskpane.

Is this an expected behaviour? or I missed something to properly implement the handler.

Here is the code snippet:

if (typeof Excel !== 'undefined') {
      try {
        Excel.run(
          this.eventResult ? this.eventResult.context : null,
          async (context: Excel.RequestContext) => {
            if (this.eventResult) {
              this.eventResult.remove();
              await context.sync();
              this.eventResult = null;
            }

            this.eventResult = context.workbook.worksheets
              .getActiveWorksheet()
              .onSelectionChanged.add(
                async (event: Excel.WorksheetSelectionChangedEventArgs) => {
                  this.setState({ address: event.address });
                }
              );
            await context.sync();
          }
        );
      } catch (error) {
        console.log(error);
      }
    }

[catchingyu]

@mm90849491 Is it possible for you to provide us more info

Is it possible for you to provide us more information on this issue? Thanks a lot for the support.

Description :

Expected Behavior

Current Behavior

Steps to Reproduce, or Live Example

Context

Your Environment
Platform [PC desktop, Mac, iOS, Office Online]:
Host [Excel, Word, PowerPoint, etc.]:
Office version number:
Operating System:
Browser (if using Office Online):

[mm90849491]

Description :
I implemented a cell picker which displays address of current selected cell. Once the selection change, the cell picker in a task-pane would update accordingly.
On Mac, iOS and Office Online (IE11, Chrome, etc), the cell picker refreshes properly, no matter how fast the selection changes; whereas on Win7/10, when the selection changes too fast, sometimes the task-pane stops to refresh, until moving mouse cursor back to task-pane area. In addition, when F12 is active (even after closing F12), the cell picker works perfectly fine like on the other platforms.
By checking internal log, I found the following behaviours of onSelectionChanged():
--registered on a worksheet, all events are queued and fired, when mouse move back to task-pane
--registered on the workbook, only the first event and the last event are fired, when mouse move back to task-pane

Expected Behavior:
onSelectionChanged should behave the same on all platforms, either in the current PC way or other platforms, preferably the other platforms way

Current Behavior:
onSelectionChanged sometimes behaves differently on PC than other platforms

Steps to Reproduce, or Live Example:

1. set cell selection by mouse or keyboard
2. keep changing cell selection by fast mouse clicking or holding arrow key on keyboard
3. observe the delay or pause of emission of onSelectionChanged
4. move mouse cursor back to task-pane area
5. observe the last selection change is correctly triggered now
6. run F12/IEChooser, select task-pane site
7. close F12
8. repeat step 1 & 2
9. observe onSelectionChanged keeps emitting instantaneously after selection change
   (step 3 is not reproducible on other platform)

Context
NodeJS Express + Fabric React + Redux
https://appsforoffice.microsoft.com/lib/1.1/hosted/office.debug.js
webpack config:

{
    test: /\.tsx$/,
    loader: "babel-loader",
    include: /src/,
    options: {
      plugins: [
        "@babel/plugin-proposal-class-properties",
        "@babel/plugin-transform-runtime"
      ],
      presets: [
        "@babel/react",
        "@babel/typescript",
        ["@babel/env", { modules: false }]
      ]
    }
  }

tsconfig.json

{
  "compilerOptions": {
    "target": "es5",
    "module": "commonjs",
    "jsx": "react",
    "moduleResolution": "node",
    "sourceMap": true,
    "esModuleInterop": false,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "removeComments": true,
    "outDir": "dist",
    "allowUnusedLabels": false,
    "noImplicitReturns": true,
    "noUnusedParameters": true,
    "noUnusedLocals": true,
    "lib": ["es7", "dom"],
    "pretty": true,
    "typeRoots": ["node_modules/@types"]
  },
  "exclude": ["node_modules"],
  "compileOnSave": false,
  "buildOnSave": false
}

Your Environment (problem environment in bold)
Platform: PC desktop, Mac, iOS, Office Online
Host: Excel x86 and x64
Office version number: 16.0.11601.20174, 16.25 on mac, current version on iOS and online
Operating System: Win7/10, OS X 14.4, iOS 12.3
Browser (if using Office Online): IE 11.0.9600.19356, Chrome 74.0.3729.169 (Official Build) (64-bit)

[jordandev]

I also saw this behaviour with onSelectionChanged.

It was required to mouse over the task pane to cause the event to be fired. If F12 was opened the events would always be fired immediately.

Platform: Excel for Office 365, build 16.0.11629.20164, 64 bit
Operating System: Windows 10

[catchingyu]

Bug 3384014 is created to track the issue.
Similar issue with https://github.com/OfficeDev/office-js/issues/521

[RyanMcArthur]

I have found a workaround that appears to work for me. I'll put an example below. The workaround is within the 'Important Part' comments.

Within the function being attached to the 'Office.EventType.DocumentSelectionChanged' event (in the case below that function is 'NewSelectionHandler') I found if you get an html element and set its textContent to a value, even the same value as it already is, causes the event to be handled as intended.

// The attaching of the event handler function occurs here
if (typeof Excel !== 'undefined') {
    Office.context.document.addHandlerAsync(Office.EventType.DocumentSelectionChanged, NewSelectionHandler);
}

// The function that is being attached to the event
function NewSelectionHandler(eventArgs) {
    /* --------------------------------------------- Important Part -------------------------------*/
    // Just simply grab an element that exists within your HTML
    let randomElement = document.getElementById('random_element');

    // Set that element's textContent to a value
    randomElement.textContent = 'Random Element Text';
    /* --------------------------------------------- Important Part -------------------------------*/

    Excel.run(function (ctx) {
        // Create a proxy object for the selected range and load its properties
        let sourceRange = ctx.workbook.getSelectedRange().load('values');

        // Run the queued-up command, and return a promise to indicate task completion
        return ctx.sync()
            .then(function () {
                if (sourceRange.values === null) {
                    randomElement.textContent = '';
                } else {
                    randomElement.textContent = sourceRange.values[0][0];
                }
            });
    }).catch(function (error) {
        console.log(error);
    });
}

This has worked for me in all test cases I've tried. I'm not sure why, I haven't looked into it. I could come up with guesses as to why, but it would have no evidence or research to back it up.

Let me know if this works for others.

[soorajhealthplotter]

I am also facing the similar issue.
i have tried with the above solution in windows desktop excel version.
but no hope.if console is opened it works fine without any delay

[soorajhealthplotter]

I tested the application in Excel on Mac and office online. It works perfectly fine.
This unstable behaviour only happens in Excel on Windows

[RyanMcArthur]

@soorajhealthplotter
I am setting the textContent of an \

[RyanMcArthur]

@soorajhealthplotter
I tested a few more scenarios and \

[mm90849491]

I have found a workaround that appears to work for me. I'll put an example below. The workaround is within the 'Important Part' comments.

Within the function being attached to the 'Office.EventType.DocumentSelectionChanged' event (in the case below that function is 'NewSelectionHandler') I found if you get an html element and set its textContent to a value, even the same value as it already is, causes the event to be handled as intended.

// The attaching of the event handler function occurs here
if (typeof Excel !== 'undefined') {
    Office.context.document.addHandlerAsync(Office.EventType.DocumentSelectionChanged, NewSelectionHandler);
}

// The function that is being attached to the event
function NewSelectionHandler(eventArgs) {
    /* --------------------------------------------- Important Part -------------------------------*/
    // Just simply grab an element that exists within your HTML
    let randomElement = document.getElementById('random_element');

    // Set that element's textContent to a value
    randomElement.textContent = 'Random Element Text';
    /* --------------------------------------------- Important Part -------------------------------*/

    Excel.run(function (ctx) {
        // Create a proxy object for the selected range and load its properties
        let sourceRange = ctx.workbook.getSelectedRange().load('values');

        // Run the queued-up command, and return a promise to indicate task completion
        return ctx.sync()
            .then(function () {
                if (sourceRange.values === null) {
                    randomElement.textContent = '';
                } else {
                    randomElement.textContent = sourceRange.values[0][0];
                }
            });
    }).catch(function (error) {
        console.log(error);
    });
}

This has worked for me in all test cases I've tried. I'm not sure why, I haven't looked into it. I could come up with guesses as to why, but it would have no evidence or research to back it up.

Let me know if this works for others.

Appreciate your efforts but I am afraid you misunderstand the problem.

First, notice you are using Office.context.document.addHandlerAsync to register the handler, which is through Common API. The original issue is about Excel API. Anyway I tried and the unstable behaviour still exists through this way.

The issue is about the event handler is not always triggered. In your example, the function NewSelectionHandler is occasionally skipped.

As I mentioned above, this unstable behaviour only occurs under specific environments. For example, OS X does not have this problem at all; once F12 is opened, this problem goes away; I heard if you copy and paste the code to CodeLab, it works perfectly fine.

Additional information: desktop version of Excel which uses Edge on Windows (requires Windows 10 ver. >= 1903 / Office 365 ver >= 16.0.11629) does not have this problem either, so I strongly suspect the problem is related to IE 11 with polyfill.

[soorajhealthplotter]

Hi @RyanMcArthur
I am using Coffescript

$scope.cellLink = (event) ->
console.log("----------------------------------"+event.address)
document.getElementById('random_element').textContent = event.address
return

$scope.enableLookUpCellAction = ()->
Excel.run((context) ->
worksheet = context.workbook.worksheets.getActiveWorksheet()
worksheet.onSelectionChanged.add $scope.cellLink
context.sync().then ->
console.log 'Event handler successfully registered for cell click or change in the worksheet.'
return
).catch (error) ->
console.log 'Error: ' + error
return

Additional Info: onActivated event also gets delayed to trigger when the sheet changes(Problem only in windows. works fine in OSX,office online)

[soorajhealthplotter]

As a immediate or temporary fix we added a setInterval function inside initialize function
Office.initialize = function()
{
setInterval(function(){ }, 1000);
console.log('office is ready')
}

[Zlatkovsky]

@soorajhealthplotter , I don't have much context on this particular issue, but FYI that rather than using Office.initialize you would be much better off using Office.onReady(). See more info here for what the advantages of the latter are: https://github.com/OfficeDev/office-js/issues/5#issuecomment-382189046

[RyanMcArthur]

I have found a workaround that appears to work for me. I'll put an example below. The workaround is within the 'Important Part' comments.
Within the function being attached to the 'Office.EventType.DocumentSelectionChanged' event (in the case below that function is 'NewSelectionHandler') I found if you get an html element and set its textContent to a value, even the same value as it already is, causes the event to be handled as intended.

// The attaching of the event handler function occurs here
if (typeof Excel !== 'undefined') {
    Office.context.document.addHandlerAsync(Office.EventType.DocumentSelectionChanged, NewSelectionHandler);
}

// The function that is being attached to the event
function NewSelectionHandler(eventArgs) {
    /* --------------------------------------------- Important Part -------------------------------*/
    // Just simply grab an element that exists within your HTML
    let randomElement = document.getElementById('random_element');

    // Set that element's textContent to a value
    randomElement.textContent = 'Random Element Text';
    /* --------------------------------------------- Important Part -------------------------------*/

    Excel.run(function (ctx) {
        // Create a proxy object for the selected range and load its properties
        let sourceRange = ctx.workbook.getSelectedRange().load('values');

        // Run the queued-up command, and return a promise to indicate task completion
        return ctx.sync()
            .then(function () {
                if (sourceRange.values === null) {
                    randomElement.textContent = '';
                } else {
                    randomElement.textContent = sourceRange.values[0][0];
                }
            });
    }).catch(function (error) {
        console.log(error);
    });
}

This has worked for me in all test cases I've tried. I'm not sure why, I haven't looked into it. I could come up with guesses as to why, but it would have no evidence or research to back it up.
Let me know if this works for others.

Appreciate your efforts but I am afraid you misunderstand the problem.

First, notice you are using Office.context.document.addHandlerAsync to register the handler, which is through Common API. The original issue is about Excel API. Anyway I tried and the unstable behaviour still exists through this way.

The issue is about the event handler is not always triggered. In your example, the function NewSelectionHandler is occasionally skipped.

As I mentioned above, this unstable behaviour only occurs under specific environments. For example, OS X does not have this problem at all; once F12 is opened, this problem goes away; I heard if you copy and paste the code to CodeLab, it works perfectly fine.

Additional information: desktop version of Excel which uses Edge on Windows (requires Windows 10 ver. >= 1903 / Office 365 ver >= 16.0.11629) does not have this problem either, so I strongly suspect the problem is related to IE 11 with polyfill.

@mm90849491

I'm looking for some clarification to ensure I'm understanding everything correctly.

I'm currently on Windows 10 ver. =1809 and I made sure my Excel is using IE 11. I was experiencing the exact behavior described with the event not working until I moused over the task pane or until I opened F12 developer tools. The issue did not occur in Excel online or on Mac.

You said "The issue is about the event handler is not always triggered. In your example, the function NewSelectionHandler is occasionally skipped." Would that explain how I was experiencing the exact same behavior but it is still a different issue?

[mm90849491]

@RyanMcArthur

Yes, I think you are experiencing the same issue, and altering text content of HTML elements does not change the behaviour.

@soorajhealthplotter
I tried the set Interval function, it really works like a magic. It also works through onReady callback. By the way, I reduced the interval to 50 milliseconds so I would not feel any lag when pressing arrow keys to change cell selection.

[RyanMcArthur]

@mm90849491

Thanks for the fast reply. I tested the setInterval function and that seemed to solve the issue also, but it wasn't as responsive as altering textContent. I decided to include both. I'm still not sure why textContent seems to work for me, but I'm just going to go with it.

[mm90849491]

@RyanMcArthur
If textContent works for me, I would not include setInterval as it might adds unnecessary side effects.
Instead of onReady, now I put setInterval right after event registration and clear it before unregister so it would not run forever.

[misaunde]

What an interesting workaround.

I can get rid of the lag if I go all the way down to 10 milliseconds:
setInterval(() => {}, 10);
but that seems sketchy/risky, even though (locally) everything appears to be behaving like normal.

[catchingyu]

How about trying this workaround to see whether the problem can solved :
add MutationObserver=null to the beginning of the main html

[misaunde]

That doesn't appear to change anything for me. Using MutationObserver=null I still see the delay.

Here's my main.ts:
````
//imports...

if (environment.production) {
enableProdMode();
}

function launch() {
platformBrowserDynamic().bootstrapModule(AppModule);
}

if (window.hasOwnProperty('Office') && window.hasOwnProperty('Excel')) {
MutationObserver=null
window['Office'].initialize = reason => {
// setInterval(function(){ }, 10);
launch();
};
} else {
launch();
}
````

[catchingyu]

@misaunde can you try to put add MutationObserver=null to the beginning of the main html

[misaunde]

@catchingyu, to clarify, you said main html - do you mean main.ts or another file?

Assuming you meant main.ts, I tried at the beginning of the file, I tried after the imports, I tried on the succeeding lines. I tried adding MutationObserver=null to each place, one at a time and then to all of those places at once. So far though, I haven't noticed any difference using that line regardless of where or how often I've used it.

[RyanMcArthur]

@misaunde
Before the import of the office.js file in the main.html I was able to add

and it appears to be working correctly now. I have removed the previous workaround I mentioned above.

[misaunde]

Thanks @catchingyu and thanks for clarifying @RyanMcArthur.

I put that at the top of my index.html and looks like that fixed it for me too:
````




....

````

[cskr11]

@catchingyu Thanks.
We were also facing the same issue with sheetChange event (Task pane excel addin) and
seems to have fixed that issue.
We have put exactly like how @misaunde has put.
What is the idea behind doing this? Can we rely on this solution for production roll out of our OFFICE JS/EXCEL ADDIN?

[pkkj]

The reason is, in IE, Office.js is using a special implementation of Promise, which uses MutationObserver to schedule a task "in idle". However, MutationObserver has weird issue in IE and sometimes it gets stuck at the very beginning. Therefore, setting MutationObserver =null can force the Office.js to use the most native solution: setTimeout(0, func()) to schedule a task "in idle".

[AlexJerabek]

I've documented the workaround with https://github.com/OfficeDev/office-js-docs-pr/pull/1358. @catchingyu and @tong-1324, please keep me up-to-date on the product fix.

[AlexJerabek]

@catchingyu Why was this issue closed? While we have a workaround the underlying issue remains.