deleting excel worksheets with javascript api is very slow

24 views Asked by At

We are making value-only copies of all data in all tabs of an excel report which is stored in sharepoint 2016. The report is viewed in the browser, and we have an add-in that creates a new sheet for every existing sheet, and then copies all the values (no formulas, filters, or data connections are copied). The code executes reasonably quickly up until the sheets.load() and await context.sync() commands (line 27/28) that occur AFTER the sheets are deleted. It may take 300 seconds for that command to complete. I'm looking for any recommendations for how we can remove the sheets without that delay or reduce the time to execute.

To add a little more context, the workbook has external data connections to a SSAS model. If we view the spreadsheet and execute the add-in code, it completes in about 30 seconds. But, if I select data.. "refresh all connections" in the browser, AND THEN execute the code to copy the data from the sheets, THEN the code takes around 300 seconds to complete - and all the delay is in the context.sync() after the code to delete the original worksheets. (it takes about 20 seconds to get to that line)

function CopySheetsAndDeleteOriginals() {
    // Run a batch operation against the Excel object model
    Excel.run(async (context) => {
        const sheets = workbook.worksheets;
        sheets.load('items/name,shapes,address, values,visibility')
        await context.sync();
        var originalNames=[]
        for (const ws of sheets.items) {
            let sheetName = ws.name;
            originalNames.push(sheetName)
            if (ws.visibility === Excel.SheetVisibility.visible) {
                console.log('originalNames.length : ' + originalNames.length)
                let snapshotsheet = workbook.worksheets.add(originalNames.length.toString());
                const range = ws.getRange();
                snapshotsheet.getRange().copyFrom(range, Excel.RangeCopyType.values);
                snapshotsheet.getRange().copyFrom(range, Excel.RangeCopyType.format);
                await context.sync();
            }
        }
        for (const ws of sheets.items) {
            if (originalNames.includes(ws.name)) {
                console.log(`sheet: ${ws.name}`);
                ws.delete();
            }
        }

        sheets.load('items/name')
        await context.sync();       // THIS LINE IS VERY SLOW when executed after 'refresh all connections'
        for (let i = 0; i < sheets.items.length; i++) {
            const ws = sheets.items[i];
            ws.name = originalNames[i];
        }
        await context.sync();
    });
};
0

There are 0 answers