Office JS "TableRowCollection.add" not worked in Excel Online but works well in Excel

Question

This code works well in Excel but not works in Excel Online. However, in Excel Online it generate error in browser log, but also insert empty row into table.

I've learn version of API for this function. This seems worked since 2017 in Excel Online.

 function onWorksheetAdded(eventArgs) {
 Excel.run(function (context) {
    var table = context.workbook.tables.getItem(INDEX_TABLE_NAME);
    var tableRows = table.rows;
    var tableColumns = table.columns; 
    tableRows.load('items'); tableColumns.load('items');

    return context.sync().then(function () {
        var row = []; for (var j = 0; j < tableColumns.items.length; j++) row.push(0);
        var newRow = [];
        newRow.push(row);
        tableRows.add(0, newRow); //here the mistake, but I can't fix it
        return context.sync();
    });
}).catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
}

I've found this in log: Error: GeneralException: There was an internal error while processing the request. Home.js:204:21 Debug info: {"code":"GeneralException","message":"There was an internal error while processing the request.","errorLocation":"TableRowCollection.add","statement":"var add=rows.add(...);", ...

Please help me with this issue.

I've just noted, this error depend form Excel table. When I prepare Workbook with table it sometimes do not generate this error. But I can't understand what exactly happened there. — Anjey Pro.

philip yu philip yu · Accepted Answer · 2019-07-23T06:47:14

Try to add await context.sync(); after tableColumns.load('items') to see whether it can help you.

I did it on my Excel Online with below piece of code, it works well:

expensesTable.rows.load('items'); expensesTable.columns.load('items');
await context.sync();

var row = []; for (var j = 0; j < expensesTable.columns.items.length; j++) row.push(0);
var newRow = [];
newRow.push(row);
expensesTable.rows.add(0, newRow);

Office JS "TableRowCollection.add" not worked in Excel Online but works well in Excel

1 Answers