Multiple consequent Excel.run() calls with delayForCellEdit option throws an exception when user is editing cell

Question

I have an Excel 2016 addin (task pane) that inserts about 92000 rows and 100 columns into worksheet. All data is divided on packets on the backend and is sent to the task pane page by packets. On the task pane page java script inserts data from every packet to the current worksheet using Excel.run with parameter { delayForCellEdit: true }.

All data to be inserted is a double array of strings

[
["a_1_1", "a_1_2", "a_1_3", ..., "a_1_100"],
["a_2_1", "a_2_2", "a_2_3", ..., "a_2_100"],
...
["a_92000_1", "a_92000_2", "a_92000_3", ..., "a_92000_100"],
]

Packet - JavaScript object, for example first packet will be
Packet={
"number": "1of920",
"data":[
["a_1_1", "a_1_2", "a_1_3", ..., "a_1_100"],
["a_2_1", "a_2_2", "a_2_3", ..., "a_2_100"],
...
["a_100_1", "a_100_2", "a_100_3", ..., "a_100_100"],
]
}

Function that inserts packet's data is looking like that.

function addDataIntoSheet(Packet){
    var startindex = 100 * (parseInt(Packet.number.split('of')[0]) - 1 );

    var pExRun = Excel.run({ delayForCellEdit: true }, function (context) {

        var currwsheet =  context.workbook.worksheets.getActiveWorksheet();

        Packet.data.forEach(function (row, index, obj) {  
            var rowsrange = currwsheet.getRangeByIndexes(startindex + index, 1, 1, row.length);
            var rangevalues = new Array(1);
            rangevalues[0] = row;
            rowsrange.values = rangevalues;                               
        });

        return context.sync();
    });

    pExRun.then(function(){
        console.log("Packet " + Packet.number + " inserted");

    });

    pExRun.catch(function (error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("addRowsAndDataIntoSheet Debug info: " + JSON.stringify(error.debugInfo));
        }
    });  
}

ISSUE: During tests I noticed, that if user started to edit cell of pressed any Excel buttons or resized any row or column the following exception will be thrown: "code":"GeneralException","message":"Wait until the previous call is completed" and as a result some packets are not inserted to the worksheet.

If a user is calmly waiting, data insertion completes without any errors.

QUESTION: Please, recommend how to overcome this issue and to insert large volume of data, despite on the user's activity.

Dan Saunders - MSFT Dan Saunders - MSFT · Accepted Answer · 2019-04-01T06:52:27

Can you give me the exact version of Excel you are using (File > Account> About Excel). Delay for cell edit support was added in recent versions of Excel, but may not exist in your version. Also looking at the code, are you only sending one long-running context.sync() call to Excel or is addDataIntoSheet called many times? There is a limit to the number of outstanding requests from JavaScript to Excel of 50 that you may run into if you send thousands of packets faster than Excel can handle them with multiple context.sync(). If delayed, you are more likely to run into this limit as requests are stalled until user exits cell edit mode, although it is possible to hit the limit without going into cell edit mode depending on the speed of your packet delivery. To workaround, you could add your packets to a queue to be handled after receiving previous responses from Excel and making sure you do not have 50 outstanding requests.

Multiple consequent Excel.run() calls with delayForCellEdit option throws an exception when user is editing cell

1 Answers