tableObject.clearFilters();

tableObject.convertToRange();

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    table.convertToRange();
    return ctx.sync(); 
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

tableObject.delete();

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    table.delete();
    return ctx.sync(); 
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

tableObject.getDataBodyRange();

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    var tableDataRange = table.getDataBodyRange();
    tableDataRange.load('address')
    return ctx.sync().then(function() {
            console.log(tableDataRange.address);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

tableObject.getHeaderRowRange();

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    var tableHeaderRange = table.getHeaderRowRange();
    tableHeaderRange.load('address');
    return ctx.sync().then(function() {
        console.log(tableHeaderRange.address);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

tableObject.getRange();

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    var tableRange = table.getRange();
    tableRange.load('address'); 
    return ctx.sync().then(function() {
            console.log(tableRange.address);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

tableObject.getTotalRowRange();

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    var tableTotalsRange = table.getTotalRowRange();
    tableTotalsRange.load('address');   
    return ctx.sync().then(function() {
            console.log(tableTotalsRange.address);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

tableObject.reapplyFilters();

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    table.load('index')
    return ctx.sync().then(function() {
            console.log(table.index);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

Excel.run(function (ctx) { 
    var index = 0;
    var table = ctx.workbook.tables.getItemAt(0);
    table.load('id')
    return ctx.sync().then(function() {
            console.log(table.id);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

Excel.run(function (ctx) { 
    var tableName = 'Table1';
    var table = ctx.workbook.tables.getItem(tableName);
    table.name = 'Table1-Renamed';
    table.showTotals = false;
    table.style = 'TableStyleMedium2';
    table.load('tableStyle');
    return ctx.sync().then(function() {
            console.log(table.style);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

by Zlatko Michailov
Microsoft Corp.

This article points out three pitfalls that Excel add-in developers may encounter and ways to work around them. It is important to have an understanding of these scenarios especially because they don't cause the add-ins to fail under normal circumstances. The add-in may seem perfectly normal when used over a small range; however they may degrade in a linear fashion as the target range that add-in operates on grows over time.

The first two issues manifest when formulas are set on table columns; in specifc, columns with formulas and columns with totals row.

Setting Formulas in Calculated Table Columns

This article gives an overview of calculated columns.

The key feature is in Step 4:

When you press Enter, the formula is automatically filled into all cells of the column — above as well as below the cell where you entered the formula. The formula is the same for each row, but since it's a structured reference, Excel knows internally which row is which.

That means that every single formula update may get multiplied N times where N is the number of rows in the table.

Users may not notice a substantial lag when dealing with a table with 1,000 rows, but interacting with a table containing 10,000 such rows may lead to degraded experience.

Luckily, Excel's automatic column calculation is clever enough, and you may not notice the above problem. For a column to get automatically recalculated, it has to either be empty or be entirely auto-calculated. If you break the "purity" of the column by inserting a value (not a formula) in any cell, Excel will not try to auto-recalculate it. Also, if you are trying to set the formula that Excel has already set in that column, the recalculation would be a no-op.

Example, let's say you are setting the formula =B2+C2 on cell A2. If the column is empty, Excel will calculate all the cells of this column adjusting the row index. Then, when you move to the next row, and you set the formula =B3+C3 on A3, there will be no column recalculation, because this formula is already auto-set on the whole column.

However, if you want your column to represent a function of the row index, e.g. =i * i where i is the row index, not only will this cause a whole column recalculation on every update, but you will also end up with a column that shows the same (last) formula.

Setting Formulas on a Table with a "Totals" Row

Setting formulas on tables with totals row enabled may sometimes cause performance issues. It is important to mention that even a default Totals row, i.e. one that has a static value in the left-most cell and a Count on the right-most cells and having all cells in between None, could repro the problem.

While there is a simpler workaround - set all the formulas, and then add the totals row on the table, the generic workaround pattern that is recommended for both of above issues is to use a plain range while setting formulas, and then to convert that range into a table.

Here is a generic function that updates a range of data and creates a table on the target range.

function createAndPopulateTable(context, worksheetName, rangeAddress, hasHeaderRow, headerValues, bodyFormulas, tableCustomizer) {
    var worksheet = context.workbook.worksheets.getItem(worksheetName);

    // Calculate table-, body-, and header- ranges
    var tableRange = worksheet.getRange(rangeAddress);
    var bodyRange = tableRange;
    if (hasHeaderRow) {
        bodyRange = tableRange.getResizedRange(-1, 0).getOffsetRange(1, 0);
        if (headerValues) {
            // Set header values
            var headerRange = tableRange.getRow(0);
            headerRange.values = headerValues;
        }
    }
    
    // Set body formulas
    bodyRange.formulas = bodyFormulas;

    return context.sync()
        .then(function() {
            // Create the table
            var table = context.workbook.tables.add(tableRange, hasHeaderRow);

            // Invoke the caller's customizer
            if (tableCustomizer) {
                tableCustomizer(table);
            }

            return context.sync();
        });
}

The above function is available online at public location.

It can be used like this:

    return Excel.run(function(context) {
        return createAndPopulateTable(context, "Sheet1", "B3:E6", true, [['Alpha', 'Beta', 'Gamma', 'Delta']], 
                    [ ['=1+1', null, null, '=B4'], 
                      ['=2+2', null, null, '=B5'],
                      ['=3+3', null, null, '=B6'] ],
                    function (table) {
                        table.style = 'TableStyleLight1';
                        table.showTotals = true;
                    });
    });

Automatic column calculation can be disabled in Excel desktop client (it is ON by default), however it is always ON in Excel Online. Therefore, as an add-in developer, you should assume that it is ON for the majority of your add-in's users.

Getting a Range Object

This issue is specific to the JavaScript API implementation.

In order to correctly track the range during insertions and deletions of rows/columns, a binding is internally created every time a Range object is requested. Later, when a cell is updated, all relevant bindings have to get notified to update themselves.

Thus, the following code (line 8), that seems benign from a general programming perspective escalates complexity quadraticly:

    Excel.run(function(context) {
        var n = 10000;
        var worksheet = context.workbook.worksheets.getActiveWorksheet();
        worksheet.load();

        var arr = [];
        for (var i = 2; i <= n + 1; i++) {
            var range = worksheet.getRange("C3:C" + (n + 1)); /* <-- PROBLEM! */
            arr.push(["=A" + i + " + B" + i]);
        }
        range.formulas = arr; 
        return context.sync();
    });

The workaround is to avoid the unnecessary get's to same Range object by taking the relevant line outside of the loop:

    Excel.run(function(context) {
        var n = 10000;
        var worksheet = context.workbook.worksheets.getActiveWorksheet();
        worksheet.load();

        var arr = [];
        var range = worksheet.getRange("C3:C" + (n + 1)); /* <-- OK */
        for (var i = 2; i <= n + 1; i++) {
            arr.push(["=A" + i + " + B" + i]);
        }
        range.formulas = arr; 
        return context.sync();
    });