Deleting rows in excel table with office-js

Question

I have an ajax call in my add in which it should create or update the table in excel. If table is already exists, it should remove the rows and add the new results. When deleting the rows in loop, it is deleting some rows and then I am getting following error:

Debug info: {"code":"InvalidArgument","message":"The argument is invalid or missing or has an incorrect format.","errorLocation":"TableRowCollection.getItemAt"}

My ajax call in my excel web add-in looks like this:

$.ajax({
        //....
    }).done(function (data) {
        Excel.run(function (ctx) {
            var odataTable = ctx.workbook.tables.getItemOrNullObject("odataTable");
            //rows items are not available at this point, that is why we need to load them and sync the context
            odataTable.rows.load();

            return ctx.sync().then(function () {
                if (odataTable.rows.items == null) {
                    odataTable.delete();
                    odataTable = ctx.workbook.tables.add('B2:G2', true);
                    odataTable.name = "odataTable";
                } else {
                    console.log("Rows items:" + odataTable.rows.items.length);
                    odataTable.rows.items.forEach(function (item) {
                        console.log("Removing row item: " + item.values);
                        item.delete();
                    });
                    console.log("rows cleaned");
                }
            }).then(function () {
                //add rows to the table
                });
            }).then(ctx.sync);

        }).catch(errorHandler);

    }).fail(function (status) {
        showNotification('Error', 'Could not communicate with the server. ' + JSON.stringify(status));
    }).always(function () {
        $('#refresh-button').prop('disabled', false);
    });

Answer 1

The idea of the iterable collections is that they consist of different items. Once you remove something from these items in a not appropriate way, the collection stops being a collection. This is because they are implemented as a linked list, in which every unit knows only the next unit. https://en.wikipedia.org/wiki/Linked_list

In your case, you are deleting wtih a for-each loop. After the first deletion, the collection is broken. Thus, you need another approach.

---

Another approach:

Start looping with a normal for loop. Reversed. E.g.:

for i = TotalRows to 1 i--
  if row(i) something then delete