Missing method in JavaScript API for Excel: copy sheet

Question

Since the changes made by the add-in cannot be undone, we need to provide users with backup options.

There is no way in the current version of the JavaScript API for Excel to copy a sheet with its data and formatting.

Does anyone know of workarounds or plans to add such a method?

Answer 1

There is currently no easy way to duplicate a worksheet, so do not hesitate to request it on “Extend Application Extension Platform Platform . ” Although such an API may appear in the future, you could add a new worksheet using worksheetCollection.add(), grab a worksheet using a range, using worksheet.getUsedRange()and copying its values ​​to another sheet.

Then your code will look something like this:

function duplicateSheet(worksheetName) {
    Excel.run(function(ctx) {
        var worksheet = ctx.workbook.worksheets.getItem(worksheetName);
        var range = worksheet.getUsedRange();
        range.load("values", "address");

        var newWorksheet = ctx.workbook.worksheets.add(worksheetName + " - Backup");
        return ctx.sync().then(function() {
            var newAddress = range.address.substring(range.address.indexof("!") + 1);
            newWorksheet.getRange(newAddress).values = range.values;              
        }).then(ctx.sync);
    });
}

Let me know how it works for you.

Gabriel Royer - Developer Team Extensibility Team, MSFT