sheet onChanged listener is very slow until F12 tools are opened

[turdsniffer]

Expected Behavior

onChanged should fire immediately after a data change to a sheet has take place.

Current Behavior

I have a sheet that should be listening for changes to a notes column. When I start the addin it adds the onChange event listeners to 3 sheets. The change listener just updates cell A1 with the current timestamp on any change. When I start the addin and change a cell, A1 sometimes changes fairly quickly and pretty often takes a while to change. However if I open F12 tools and change values in the sheet then cell A1 updates immediately. On my computer running version 1904 build 11601 the difference is pretty large as in sometimes A1 won't update for 10+ seconds if I haven't opened F12 tools but every time I open F12 tools the changes happen almost instantaneously. I have noticed that if I resize Excel it will force A1 to update. I tried this same experiment on a box running Office 365 and the difference was less but still quite noticeable.

Steps to Reproduce, or Live Example

Steps to reproduce:

1. create a new Vue.js app using the vue.js cli
2. add link to Office.js to index file "https://appsforoffice.microsoft.com/lib/1/hosted/Office.js"
3. in main.js add the following code to the bottom of the file:
   `const launch = async () => {
   await window.Office.onReady(() => {
   console.log("setting up notes column change listener")
   setupNotesColumnChangeListener('P_11217_12-31-2018');
   setupNotesColumnChangeListener('UM_CW11217_12-31-2018');
   setupNotesColumnChangeListener('UM_CL11217_12-31-2018');
   });
   }

const setupNotesColumnChangeListener = async(sheetName)=>{
console.log('setting up notes change listener for sheet: ' + sheetName);
await Excel.run(async (ctx) => {
const sheet = ctx.workbook.worksheets.getItem(sheetName);
sheet.onChanged.add(await notesChangedHandler(sheetName));
await ctx.sync();
});
}

const notesChangedHandler = async (sheetName) => {
return async (event) => {
if (!event.address.includes("A")) {
console.log("hey")
await Excel.run(async (ctx) => {
const sheet = ctx.workbook.worksheets.getItem(sheetName);
const range = sheet.getRange("A1");
range.values = [[changed: ${new Date().getTime()}]];
await ctx.sync();
});
}
};
};

launch();`

4. launch the app using npm run serve -- --port 8080
5. create a workbook with sheets: 'P_11217_12-31-2018', 'UM_CW11217_12-31-2018', 'UM_CL11217_12-31-2018', These are the sheets I added the onchanged listeners to
6. create an excel manifest file for the app and launch it
7. start changing values in sheet 'P_11217_12-31-2018' after a couple of changes notice the lag between the changes and the updates to cell A1 of the sheet.
8. start F12 tools
9. make changes and notice A1 changes immediately even after F12 is closed

Context

In our case we are trying to listen for changes to notes a person is making on there spreadsheet. When they make the changes we want to persist the notes to a database so the notes are accessible in other ways.

Your Environment

• Platform PC desktop
• Host Excel
• Office version number: 1904 build 11601
• Operating System: windows 10

[catchingyu]

Thanks for reaching us, turdsniffer.

I have verified your scenario with the scripts example you pasted here in the Script Lab (which is a great env for debugging and writing the Add-In, Details will be here: https://github.com/OfficeDev/script-lab). And the performance of the notifications seems all good even without the F12 tools opened. Is it possible you could also have a try on that?

Meanwhile, Could you also share us more about the test sheet document , is it complex with lost of data in there? And by the way, were you testing the add-in Inside the Windows Excel App or testing the add-in in the Web Browser Excel App?(I am assuming when you were saying desktop in Platform, you are referring to the Windows Excel Add-In development, right?)

Feel free to reply the thread or direct ping me if you have other questions when you are trying the Script Lab.

[turdsniffer]

@catchingyu yeah I have pulled up script labs and launched the "worksheet events" sample code and I don't see any delay there. Even if I substitute my code in it seems to be very quick if I just run it through script labs. I only saw the delay when I side loaded my vue web app though like I said it is a newly generated vue app with no additional logic added beyond the generated starter project. As far as how much data is in my sheets I originally had a few thousand rows in them but I can reproduce this with blank sheets. Sometimes the cell A1 won't get updated at all until I resize the excel window, but as soon as I open F12 tools I don't see the delay again and everything works very quickly and reliably... I am using the excel desktop.

[turdsniffer]

Here is my sample app. npm install and npm run serve. Then side load it

test.zip

[4tti]

We are facing such issues from time to time as well and not only with the onChanged event but also SelectionChanged events. We did some measurement comparing ScriptLab, sample addin and our addin and it turned out that sometimes there is difference ~1s. BUT occasionally (and randomly) we see much bigger difference, suddenly the events have like 3 seconds delay and once debugger is attached it starts to be fast. So far we don't have clear idea what is happening.

[catchingyu]

Bug 3384014 is created to track the issue.

[catchingyu]

@turdsniffer we found some limitations in the system and the solutions are still under discussion. Meanwhile, we notice a workaround with adding setInterval(30000, () => {}) to your initialization function. Could you try it and let us know whether it can solve your issue or not?

[4tti]

@catchingyu: can you please clarify what do you mean by initialization function?

[catchingyu]

@4tti taking the example you attached, you can add it here:

[4tti]

@catchingyu it did the trick, it is much much better now! May I ask for explanation? Thank you!

[catchingyu]

Great to hear that your problem can be mitigated with this workaround. The issue is due to some limitations in the IE, we are working hard now to find a real fix for it.
If anything else need our help, do not hesitate to contact with us.

[4tti]

@catchingyu : I am not reporter of this issue, I think he should provide the feedback if it helps or not... :) but thanks anyway!

[turdsniffer]

@catchingyu : using the same app the I had attached earlier I get the same behavior with or without your setinterval.

[turdsniffer]

Here is what the behavior looks like. You can see that before I open the F12 tools the time stamp updates occasionally but is quite slow and appears to mostly quite updating. Then when I open the F12 it suddenly is very responsive.

[misaunde]

I'm not sure why this was closed, but I recently updated to Excel 1902 (11328.20368) & Angular 8.1.3 from Excel 1808 (I believe) and Angular 5, and I'm seeing the same behavior as described in this case though not as pronounced - I'm seeing a 1-3 second delay.

The only other differences from what's described above is instead of using Vue, I'm using Angular and instead of onChanged I'm using onSelectionChanged.

I've pretty much copied the onSelectionChanged code from ScriptLab with a few exceptions, and I can't get the performance I'd expect until opening the console and then it's instant, every time. I would prefer to get this performance right out of the box.

Deviations from ScriptLab - "Worksheet events":

//In registerSelectionChangedHandler():
...
sheet.onSelectionChanged.add(onSelectionChange.bind(this));//I'm binding to this
...

//My onSelectionChange() function:
async onSelectionChange() {
    this.result = Math.random(); //This is tied to the view, ie. <p>{{result}}</p>
   
    //this writes to the sheet, similiar to original issue
    await Excel.run(async (ctx) => {
      let sheet = ctx.workbook.worksheets.getItem("Sheet1");
      let range = sheet.getRange("A1:A1");
      range.values = [[ this.result ]];
      await ctx.sync();
    });

    this.appRef.tick();
}

In my case, I actually want to update the view. I don't want to write to the sheet (like the original poster), however, I see the same delay regardless of where I output the results - there's a pronounced delay until the console is opened. After the console has been opened (even if immediately closed) the performance improvements remain.

Suggested workaround:
The suggested 30 second interval didn't make a difference in my case:

//from my main.ts:
function launch() {
    platformBrowserDynamic().bootstrapModule(AppModule);
}
 
if (window.hasOwnProperty('Office') && window.hasOwnProperty('Excel')) {
    window['Office'].initialize = reason => { 
        setInterval(() => {}, 30000);
        launch(); 
    };
} else {
    launch();
}

Possibly related (although without a solution):
https://stackoverflow.com/questions/48580255/slow-javascript-execution-in-ie11-until-developer-tools-are-enabled

Any ideas?

[catchingyu]

@misaunde

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

[MrP]

@turdsniffer we found some limitations in the system and the solutions are still under discussion. Meanwhile, we notice a workaround with adding setInterval(30000, () => {}) to your initialization function. Could you try it and let us know whether it can solve your issue or not?

Wow!

First of all: Thanks! this helped me with my issue where events didn't register properly in the add in until I hovered the mouse over the pane.

Second: What is this sorcery?! @catchingyu can you explain what's going on here? I tried with the setInterval in the "right" way, and it didn't work. Am I seeing things right? What am I missing?