Why is pasting data in an excel range using office.js taking too long?

Viewed 94

We are working on an excel workbook(~ 13 MBs in size) having 15 sheets. We are pasting data on a single sheet and all the other sheets have a moderate amount of formulas linked to this data dump sheet. We need to paste approx. 10,000 rows and 30 columns in chunks of 2500 rows each time. We are syncing the context of the workbook using context.sync() after every 4th iteration (i.e. after pasting 10,000 rows). This whole process is taking approx. 70 seconds in total. We have tried using context.sync() after pasting every 2500 rows but that slows down the process even more.

await Excel.run(async function main(context) {
var totalrowcount = result.totalrows;
iterations = Math.ceil(totalrowcount / 2500);
for (i = 1; i <= iterations; i++) {
    let names = context.workbook.names;
    var rng;
    data_arr = result.data;
    data_arr.splice(0, 1);

    var rowOffset = (i - 1) * 2500;
    rng = names.getItem(rangeName).getRange().getOffsetRange(1 + rowOffset, 3);
    rng.getAbsoluteResizedRange(data_arr.length, data_arr[0].length).values = data_arr;
    rng.untrack();

    if(i % 4 == 0){
        await context.sync();
    }
    else if(i == iterations){
        await context.sync();
    }
}});
1 Answers

have you also try to add suspendScreenUpdatingUntilNextSync pauses visual updates to Excel until the add-in calls context.sync(), or until Excel.run ends (implicitly calling context.sync) like https://docs.microsoft.com/en-us/office/dev/add-ins/excel/performance? As we cannot repro the issue locally, so if you can share session Id to let us take a look the detail log that would be helpful. Thanks.

Related