Office-js: context.sync() hangs when it is reading values from Range (Office 1908)

[harri-rajala-koivu-solutions]

ExcelJS (Office version 1908) hangs in context.sync when it is reading values from Range object. Office version 1907 works fine.

Expected Behavior

ExcelJS should not hang. It should return values from the Range object.

Current Behavior

ExcelJS hangs. Somehow hang is related to combination of rows, columns and data length of the cells. See example code.

Note! Same code works with Office version 1907.

Steps to Reproduce, or Live Example

    private excelHang = async () => {
        await Excel.run(async (context: Excel.RequestContext) => {
            const sheet = context.workbook.worksheets.getActiveWorksheet();

            // Delete existing test table
            let table = sheet.tables.getItemOrNullObject("TestTable");
            await context.sync();
            if (table && !table.isNullObject) {
                table.delete();
            }

            // Setup test data. 
            // You can change these and try again.
            const testRowCount = 10000;
            const columnDataLength = 8;

            const totalCharcaterCount = testRowCount * columnDataLength;
            sheet.getRange("C1:D1").values = [["Row Count", testRowCount]];
            sheet.getRange("C2:D2").values = [["Column Data Length", columnDataLength]];
            sheet.getRange("C3:D3").values = [["Total character count", totalCharcaterCount]];
            sheet.getRange("C4:D4").values = [["Row Count from Excel Range", ""]];
            sheet.getRange("C5").values = [["Running..."]];
            sheet.getRange("C1:C5").format.autofitColumns();

            // Create test table
            table = sheet.tables.add("A1", true);
            table.name = "TestTable";
            table.getHeaderRowRange().values = [["Test1"]];
            const rows = [];
            const columnData = "a".repeat(columnDataLength);
            for (let i = 1; i <= testRowCount; i++) {
                rows.push([columnData]);
            }
            table.rows.add(undefined, rows);
            await context.sync();

            // Try to read all values from table
            const range = table.getDataBodyRange();
            range.load("values");

            // Code hangs in next context.sync call. 
            // Hang is somehow related to the combination of rows, colums and data length of the cells.
            // You can try with different testRowCount and columnDataLength

            // 10000 * 8 = 80000 = ExcelJS context.sync hangs

            // 4920 * 8 = 39360 = OK, no hangs
            // 4921 * 8 = 39368 = ExcelJS context.sync hangs

            // 7107 * 4 = 28428 = OK, no hangs
            // 7108 * 4 = 28432 = ExcelJS context.sync hangs

            await context.sync(); // ExcelJS hangs here.

            sheet.getRange("D4").values = [[range.values.length]];
            sheet.getRange("C5").values = [["Done"]];
        });
    }

Context

Our Excel add-in does not work any more with latest Office version 1908. This is very critical issue to our customers.

Your Environment

• Platform [PC desktop, Mac, iOS, Office Online]: PC Desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: 1908 build 11929.20254 32-bit
• Operating System: Windows 10 Home version 10.0.18362 Build 18362
• Browser (if using Office Online):

[lumine2008]

Hi @harri-rajala-koivu-solutions , looks like i am not be able to repro this issue.
I just a tried in 1908 build Desktop, looks this is not repro. I also try this gist in my Mac, it also not repro.
below is the gist i am using. https://gist.github.com/lumine2008/fea50e00a84a8329b4218b1884a83d08

[harri-rajala-koivu-solutions]

We have two machine with same 1908 Excel build and both have same hang issue.

Is there anything that we can do to provide more info or internal stack traces etc. to you? So that you could find out the reason for this hang. Should we for example generate crash dump from Excel process?

[lumine2008]

@harri-rajala-koivu-solutions , have you tried my gist, does my gist work in your side?
from your code it doesnt look it has problem, so could you please share me your gist, I want to see if any other part may cause the issue.
Thanks,

[harri-rajala-koivu-solutions]

I tried your gist and Yes, I'm able to repro hang issue also with that code. And is it really so that when you run that script you see "Done" text in C5 and row count in D4. For me C5 is "Running..." and D4 is empty because code hangs in context.sync. Have your tried other numbers for example testRowCount = 100 000 and columnDataLength = 20?

Please note that my Excel is 32 Bit. Have you tried also with 32 bit or did you try with 64 bit?

Do you have same Windows version? I have Windows Home Version 10.0.18362 Build 18362.

[lumine2008]

Just tried testRowCount = 100 000 and columnDataLength = 20, it doesnt repro in my side.

My windows version: Windows 10 enterprise is [Version 10.0.18362.295] 64 bit
My Excel version is 64 bit, let me try to find a 32 bit excel to have a try.

[cyrelk]

Hi,

We experiment the same problem with the latest revision of Excel, the following script hangs on the Excel.run(...) part.

Additionnaly the debug console logs displaying the selected item and range raise an exception. Many of their properties are displayed with a string. So we think that's a new permission issue.

The code works fine with the previous revisions of Excel. We have upgraded to the latest revision of the office-js library in the hope that it will correct the problem but not so.

Needless to say, this situation is critical for the users.

function read_matrix(name, jobname, componentname, columns, callback) {
    console.log(" -- read_matrix");
    var atelier = window.modules["Atelier"];
    Excel.run(function(context) {
        var atelier = window.modules["Atelier"];
        var item = context.workbook.names.getItem(name);

        console.log(item); <<< raises an <Permission denied> exception

        var range = item.getRange();

        console.log(range);  <<< raises an <Permission denied> exception

        range.load(["text"]); // , "colCount", "valueTypes", "values", "formulas"

        console.log(" --- read_matrix component name: " + componentname);

        return context.sync().then(function() { <<< hangs forever
            if (componentname) {
                callback(make_records(jobname, componentname, range.text, columns));
            } else {
                atelier.notify("Echec de l'exportation", "Le nom de l'appareil doit être renseigné");
            }
        });
    }).catch(function(error) {
        console.log(" -- read_matrix error: " + error);
        atelier.notify("Echec de l'exportation", JSON.stringify(error.debugInfo));
    });
}

[VitaliyLavrenko]

Have same problem with Word, try read lot of content controls, and if load only 1 props it good, it load prop where we store our field config it stuck run. To solve problem we just load it by chunks, like this slower run flow, but have no case. On earlier Word version all was ok, on Mac ok too. Only after last Word update appear bug.

[cyrelk]

For informationn we just tried on a windows 7 machine and the code runs fine.

It seems that the problem is between Excel and Edge.

[harri-rajala-koivu-solutions]

FYI
Here is one StackOverflow question (created today) related to same hang issue.
https://stackoverflow.com/questions/57769321/excel-javascript-api-fails-to-read-range-from-sheet-edge-vs-ie

[VitaliyLavrenko]

Maybe I be cursed, but in OfficeJS for add-in I more like IE instead of Edge, after edge appear have lot of bugs like OAuth dialogs didn't close, context.sync() didn't call when sync lot of objects.

P.S. Before context.sync() was near 200ms to run, so basic if user have 200-300 objects, if you load it by groups of 40-50 it took much time, for simple action. I see better get OOXML and parse DOM instead of use Office API. Can say on Mac or older Word all run good with 200-300 objects, only with Edge problems.

[harri-rajala-koivu-solutions]

I think also that Edge is root cause for this hang issue.

We have one machine with same Excel & Office version (1908) and that works fine, no hang issue!! When Excel add-in is started in that machine I can see that it starts IE process (iexplore.exe). So clearly it uses IE.

But then those two machines (again same Excel & Office version 1908) where hang issue occurs does not use IE. When Excel add-in is started I don't see any IE processes in Task Manager. So I think that it must use Edge.

If Edge really is the root cause, then the my question is.
How do I change Excel add-in to use IE instead of Edge?
Why one machine uses IE and other uses Edge even though Excel & Office version is same?

[Andrew-AnalysisPlace]

Multiple customers are reporting issues with our add-in (https://appsource.microsoft.com/en-us/product/office/WA104380955) after upgrading to 1908. Issue is far more likely to occur with larger workbooks. Issue sometimes seems related to reading ranges and other times the sync() to get chart images. For example the "Advanced Features" workbook causes the issue, but the simpler workbooks still work fine.

[kbrandl]

Labeling as Area: Edge WebView and adding @jargil to list of assignees. @jargil are you able to weigh in on this issue (since it's been implied that it might be related to add-ins' use of Edge WebView in some way)?

[jargil]

Sorry, I cannot repro. However there was a bug that could have caused this for Edge WebView that I fixed starting with Office's build 16.0.12026.20074 (for Monthly Channel) and 16.0.12102 (for Insider) and newer. Unfortunately this build is not yet deploying to Office Insiders, but should hopefully soon. I'll post here once one Office build becomes available that contains this fix.

[harri-rajala-koivu-solutions]

Could you please answer these question?

How do I change Excel add-in to use IE instead of Edge?
Why one machine uses IE and other uses Edge even though Excel & Office version is same?

[jargil]

@harri-rajala-koivu-solutions - this is controlled by which version of Office/Windows you have, there's no switch.

https://developer.microsoft.com/en-us/office/blogs/microsoft-edge-webview-for-office-add-ins/

[harri-rajala-koivu-solutions]

So the only option for use and our customers is to go back to Office Version 1907 that works fine.

Please send instructions how I can remove Office Version 1908 and install version 1907?
Please note that we are on Monthly Channel.

[harri-rajala-koivu-solutions]

@jargil You said that you cannot repro. Did you try with the same versions that we have?
32-bit Excel: 16.0.11929.20234 32 Bit
Office: 1908 (Build 11929.20254 Click-toRun)
Windows: Microsoft Windows 10 Home, Version 10.0.18362 Build 18362

[thi-mcf]

We are having the same issue with Word. Our issues started late last week, i suspect after the 1908 update for word (only affects users where the add-in is rendering using Edge rather than IE). We have tested with number of machines, each machine with word 1908 build that renders using Edge is broken, and every machine that has 1908 build that renders with IE is fine. A bare bone example for this is posted at #702

We tried to recreate the issue on a very simple usecase, where we get Ooxml of a word document, context.document.body.getOoxml(). Smaller documents where the Ooxml length (character length) less than 60,000 works fine (thats roughly about 1-2 pages on a word doc depending on how many paragraphs you have). The moment the ooxml length goes above some number around 60,000 (this wasent an exact number, but always within couple of hundred characters from 60,000), the context.sync() that follows the getOoxml just hangs (no error, nothing, just hangs, code below it does not execute). since the specific 60,000 number is very close to 64kb, we suspect the issue might be more related to the amount of data coming back rather than the specific sdk/api endpoints.

Here is a very basic code snippet for recreating this issue on a Word Addin (you can find more detail at #702):

Word.run(async function (context) {
            var ooxml = context.document.body.getOoxml();
            console.log("this will log every time");
            await context.sync();
            console.log("this wont log when the payload is big");
            console.log("ooxml length", ooxml.value.length); // when this is < 59,000, it works fine
        });

For those of you who cant recreate this, update your windows to the latest version. On a machine where Word (1908) was using IE to render the addin, i force updated windows to Windows 10 Pro 1903 (Build: 18362.329), which seems force Word (or other office products) to render addins using Edge.

i believe everyone else has provided all the other info that you guys may require to recreate this issue. Can we get some acknowledgement or update on this? This is affecting a big portion of your Office add in users and most of the office add in developers. I assume there is no "work around" we can do on our end to get a fix to our customers (besides not using the word SDK/API).

Note: prior to the Word 1908 update, everything was working fine, we had above mentioned code snippet working on documents that were almost 100x larger.

[thi-mcf]

For anyone who wants to add an exception on their Addins inorder to implement an alternative screen or alternative functionality, here are some sample code to check if you have an affected machine/build:

if(window.navigator.userAgent.indexOf("Edge") > -1 && Office.context.diagnostics.version === "16.0.11929.20254"){
    console.log("word api is likely broken");
}

...use it at your own risk. it basically checks if its running in Edge and office version number matches the specific version that causes the bug (there could be more versions that I'm are unaware of).

Hopefully a fix will come out soon that makes this redundant. Till then, hope this helps.

[IvandeJong]

We have an excel addin which writes an range to an excel sheet and lets user modify it. Once he is done he will press a button on wich we will read the range from the sheet and will try to parse it.

Excel has recently updated its integration browser:
https://docs.microsoft.com/en-us/office/dev/add-ins/concepts/browsers-used-by-office-web-add-ins

Windows 10 ver. < 1903 / Office 365
Internet Explorer 11

Windows 10 ver. >= 1903 / Office 365 ver < 16.0.11629
Internet Explorer 11

Windows 10 ver. >= 1903 / Office 365 ver >= 16.0.11629
Microsoft Edge*

We traced a problem with this edge based implementation. and made a code example for this.

When we load an excel sheet with a range from A1:A100 with random strings (80 karakters each) we fail to load the range from the worksheet.
The task pane in the ie version does hit the comment but in edge it does not get hit.

We try to load the range with the folowing code:

// Run a batch operation against the Excel object model
Excel.run(function (ctx) {
    // Create a proxy object for the used range and load its properties    
    var sourceRange = ctx.workbook.worksheets.getActiveWorksheet().getUsedRange().load("values, rowCount, columnCount");
    // Run the queued-up command, and return a promise to indicate task completion
    return ctx.sync()
                .then(function () {
                    // Unhit code in edge only ????              
                })
                .then(ctx.sync);
        })
        .catch(errorHandler);

Example task pane

For the time being could you suggest a workaround?

[VitaliyLavrenko]

We have an excel addin which writes an range to an excel sheet and lets user modify it. Once he is done he will press a button on wich we will read the range from the sheet and will try to parse it.

Excel has recently updated its integration browser:
https://docs.microsoft.com/en-us/office/dev/add-ins/concepts/browsers-used-by-office-web-add-ins

Windows 10 ver. < 1903 / Office 365
Internet Explorer 11

Windows 10 ver. >= 1903 / Office 365 ver < 16.0.11629
Internet Explorer 11

Windows 10 ver. >= 1903 / Office 365 ver >= 16.0.11629
Microsoft Edge*

We traced a problem with this edge based implementation. and made a code example for this.

When we load an excel sheet with a range from A1:A100 with random strings (80 karakters each) we fail to load the range from the worksheet.
The task pane in the ie version does hit the comment but in edge it does not get hit.

We try to load the range with the folowing code:

// Run a batch operation against the Excel object model
Excel.run(function (ctx) {
    // Create a proxy object for the used range and load its properties    
    var sourceRange = ctx.workbook.worksheets.getActiveWorksheet().getUsedRange().load("values, rowCount, columnCount");
    // Run the queued-up command, and return a promise to indicate task completion
    return ctx.sync()
                .then(function () {
                    // Unhit code in edge only ????              
                })
                .then(ctx.sync);
        })
        .catch(errorHandler);

Example task pane

For the time being could you suggest a workaround?

I use this notation of load and get it by chunks, like this ok
load(option?: { select?: string; expand?: string; top?: number; skip?: number })

[Andrew-AnalysisPlace]

For customers that cannot wait a couple weeks until a fix is published, what can we tell them?
I have been telling them to revert to a prior version of Windows, but that may not be the answer. I have a customer that did that but still seems to have the issues. I can confirm that user has IE by their UserAgent: "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E; .NET CLR 2.0.50727; .NET CLR 3.0.30729; .NET CLR 3.5.30729; LCTE; wbx 1.0.0; rv:11.0) like Gecko"
EdgeView user have "Mozilla/5.0 (Windows NT 10.0; Win64; x64; WebView/3.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36 Edge/18.18362"
All the users with the issue have Excel API version 1.10. I can confirm that by logging Office.context.requirements.isSetSupported("ExcelApi", "1.10")
So, should I tell users to revert to prior version of Office or Windows (or both)?
If Office, will this method work: https://support.microsoft.com/en-us/help/2770432/how-to-revert-to-an-earlier-version-of-office-2013-or-office-2016-clic ? And should they revert to Version 1907 (Build 11901.20218)?
Can anyone confirm?

For the chunks workaround: load(option?: { select?: string; expand?: string; top?: number; skip?: number })
Can anyone else confirm that is a general method that provide a fix? What chunk size will always work, but still be performant?
I have dozens of load() methods throughout my code that gets range data, table data, range images, chart images that are impacted by this issue. So, I like to understand it better before I start applying this fix. In my case, I think this will be very difficult to apply due to my many chained/nested loads.

[jargil]

Thanks everyone for your reports. I can confirm that the fix is coming with the following builds:
Office build 1909: 16.0.12026.20074 or newer.
Office build 1908: 16.0.11929.20300 or newer.
Office Insiders: 16.0.12102 or newer.

[harri-rajala-koivu-solutions]

@jargil When will these builds be available?

[jargil]

The update for 1908 is tentatively scheduled to begin deploying on 9/10, but please note that it depends on multiple factors, so could be delayed.

I'm sorry I don't have a date yet for when the other builds will be available. I'll update here once I have a date to share.

[samuelkdavis-mccarthyfinch]

9 October, or 10 September?

[jargil]

@samuelkdavis-mccarthyfinch - September 10th.

[harri-rajala-koivu-solutions]

@jargil You told originally that you could not reproduce this issue.

So did you finally able to reproduce hang? And you have verified that these new builds really fixes the hang issue?

Or is it still so that you cannot reproduce? And you just hope that maybe these new builds fixes the hang issue?

Which one is correct?

If you think the most important functionality of ExcelJS, it is "get data from range". So how can it be possible that such a basic and core functionality is broken? How can it even possible? You should have a lot of test cases, that prevents to broke that functionality.

Also what was the root cause of this hang issue? Note that we are all developers here, so please give details, not just "there was a bug". That will help us to find possible workaround until we get fixed builds.

Please answer to ALL questions!!!

[tsabbay]

This is a terrible experience, seems like the response from Microsoft is very slow.
We had to rebuild our code from scratch to support chunks.
We got few reported bugs, none is being seriously handled.

[jargil]

Sorry for the confusion, we were able to reproduce. I can confirm the fix works for the multiple samples provided here. To reiterate, the fix is planned to begin deploying this coming week, estimate for 1908 is Sept 10.

The following builds contain the fix:
Office build 1909: 16.0.12026.20074 or newer.
Office build 1908: 16.0.11929.20300 or newer.
Office Insiders: 16.0.12102 or newer.

The core of the issue is a memory optimization where we were deleting function callbacks ahead of time.

[JensMadsen]

Bothered by the same problem in Word. I would also like to know the answer to @harri-rajala-koivu-solutions question:

If you think the most important functionality of ExcelJS, it is "get data from range". So how can it be possible that such a basic and core functionality is broken? How can it even possible? You should have a lot of test cases, that prevents to broke that functionality.

Do you have tests that cover this kind of basic functionality on all office/os versions supported or not?

EDIT: Sorry If my comment was a bit harsh. To be honest I would really like to know this because if test coverage is poor we have to add tests for these kind of functionalities ourselves such that we can explain our customers that we are aware of that the add-in on their particular installation is broken

[samuelkdavis-mccarthyfinch]

@JensMadsen Aside from selenium with Word Online, how would you expect to be able to test MSOffice in any automated fashion?

[jargil]

@JensMadsen - No worries, is a fair question. We do have test coverage, unfortunately this bug slipped through. The bug had been fixed before it was reported here, however I'll still follow up to understand why it was not detected at the moment of the initial code change.

[JensMadsen]

@samuelkdavis-mccarthyfinch no clue (but I must figure it out if it is a problem for our customers) :-) I would like to know that as well :-) If there is an easy way to build tests for word Offline I would be happy to know?

@jargil great. Seized by the confessional spirit here I must admit that I have also deployed several buggy bits and pieces.... ;-)

[cyrelk]

Our machines are now updated to the latest revisions (for 365 and 2016/19) and everything is working again.

Thanks

[jchilde]

Our machines are working using Version 1909 (12026.20108 Click-To-Run).

[samuelkdavis-mccarthyfinch]

@JensMadsen I don't know of any easy way.

One option would be using something like AutoHotKey on windows to simulate clicks + button presses at pre-defined locations + take screenshots and run an image diff (something like https://storybook.js.org/docs/testing/automated-visual-testing/). This wouldn't be particularly easy or stable.

The addon can be spun up via command line with 'office-toolbox' I believe. A test version of the applications entrypoint could be set up to run integration tests on load. Integration tests could be written to simulate events on the side bar, and use MSOffice's API to set up and assert the tests against the document. The test results could be posted to any alerting framework set up.

On a side-note, we use the PageObjectModel pattern (https://martinfowler.com/bliki/PageObject.html) typically used in testing to abstract away the atomic business behaviors that Word offers to keep Word's API from over-extending in our code base. Endpoints like "NavigateToDetailWeCareAbout(DTO)" hide multiple word api calls, and allows us to refactor which apis are called without changing the applications behavior. If the fix wasn't ready in time we would have modified the internals of "NavigateToDetailWeCareAbout(DTO)" to work in batches due to the memory limit etc. We are using another implementation of the PageObjectModel to get the same behaviors working with a VSTO implementation while still utilizing the code-base of the web-addin.

Any tests I would automate would firstly be against the PageObjectModel, then I would look into end-to-end tests. End to end tests could either run against a mocked PageObjectModel to be able to run in any node environment, or run against the real thing within word - or both.

[samuelkdavis-mccarthyfinch]

Confirmed the update has fixed our system.

Version 1908 (Build 11929.20300) using Edge. Our issue was selecting content controls & selecting the OOXML crashing when working with large data sets.

[jargil]

Thanks everyone who have confirmed the fix is working for them.

I'll keep this issue open until we reach 100% deployment across the multiple builds affected.

[harri-rajala-koivu-solutions]

I can also confirm that Version 1908 (Build 11929.20300) fixed this issue. Thanks.

[jargil]

This is now deploying across all the builds that were affected. Thanks again for your feedback and repro steps.