How to copy range formatting in office.js?

Question

Tried the following code that I pieced together from various sources but it doesn't seem to be working. Do I need to go through each individual property and assign them one by one?

Excel.run(function (ctx) {
        var worksheet = ctx.workbook.worksheets.getItem(worksheetName);
        var range = worksheet.getUsedRange();
        range.load(["formulasLocal", "address", "format/*", "format/fill", "format/borders", "format/font"]);

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

Answer 1

Unfortunately, the scenario is not currently supported (though it is on our backlog). You can get Values and Text and Formulas as arrays, but not the formatting properties.

When you do access something like range.format.fill.color, it will return a value for you if the range is identically formatted; and "null" if the range has multiple colors, and hence there is no single answer to return.

So for now, you would need to go through each cell (range.getCell(i,j)) and put them into your own 2D array, load each range's value individually, then sync, and then use that information to individually apply formatting back. Should be possible with a reasonably small range, but we do realize that there are better solutions possible with more targeted APIs,

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT