Support permanent Application.screenUpdating setter instead of suspendScreenUpdatingUntilNextSync
https://docs.microsoft.com/en-us/office/dev/add-ins/excel/performance#suspend-screen-updating states:
Don't call suspendScreenUpdatingUntilNextSync repeatedly (such as in a loop). Repeated calls will cause the Excel window to flicker.
At the same time, Excel Online has a payload size limitation that causes context.sync() to fail if it has accumulated too big a changeset against the current workbook.
Same applies to Application.suspendApiCalculationUntilNextSync().
This produces a dilemma: to avoid the payload size limit, we'll have to call context.sync() intermittently when processing a large dataset, however doing so will result in unnecessary recalculations and screen updates (flicker).
There is a way to avoid flicker and unnecessary recalculations when processing a large dataset.
Can we have something similar to on-off methods like in VBA?
try {
context.application.screenUpdating = false;
context.application.calculationSuspended = false;
...
} finally {
context.application.screenUpdating = true;
context.application.calculationSuspended = true;
}Or alternatively:
// Will only revert as soon as Excel.run() finishes.
// -
context.application.suspendScreenUpdatingUntilExcelRunFinishes();
context.application.suspendApiCalculationUntilExcelRunFinishes();We have submitted this on Office.js forum previously: https://github.com/OfficeDev/office-js/issues/2734
3 Comments
https://3.basecamp.com/3496385/buckets/16689259/card_tables/cards/7759984531
AdrianWu MSFT-SiruiSun following our discussion today,
This is our C# example of the "long operation scope" that we use in a using statement in C# with the legacy COM-based add-in, and on the screenshot there are some of the "permanent" APIs that we would like to use instead of "until next sync".
They are all available in VBA so maybe can be supported easily on Desktop and Mac in Office.js? It's fine if some of those are "no-op" or error in Excel Online.
This would be really helpful for us as well, as we open a Blob into a new worksheet and then find the used range, then copy the relevant portion into the active worksheet as sort of an 'insert-in-place' operation. This requires several context.sync() calls to load properties on the new worksheet we just inserted. But, once we copy the contents from it, we close that worksheet. I'd love the user to never even know that we inserted it.
I can understand Excel not wanting an add-in to shut down screen updating without some checks on that. What if it was something where we could disableScreenUpdating until the Excel.run() block finishes? Then we could sync as much as we needed, but when we were done with all of our updates and we release control to Excel again, it could update once.
