Deleting empty rows in a table in Excel with Office.js

Question

Sorry for what feels like a really basic question. I'm trying to iterate through an Excel table and delete any empty rows using the new add-in model. I'm fairly new to javascript and all the asynchronicity and callbacks are tying me in knots!

Since I think this is simple, I wondered if someone might be kind enough to post a quick code sample or suggestion has to the cleanest way to do it? Sorry if I've missed it in the docs.

Many thanks in advance.

Tim

Answer 1

The following code should do what you are looking for.

Excel.run(function(ctx) {
    var rows = ctx.workbook.tables.getItem('YourTableName').rows;
    rows.load("values"); // We'll need the rows values to check if they're empty.
    return ctx.sync().then(function() {
        // Important to go through the items in reverse fashion as deleting a row shifts the rest up.
        rows.items.reverse().forEach(function(row) {
            // row.values is a double array. Although, we know it can only contain one row.
            var isEmpty = row.values[0].every(function(col) {
                return col === "";
            });

            if (isEmpty) {
                row.delete();
            }
        });
    }).then(ctx.sync);
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

Hope that helps,

Gabriel Royer - Developer on the Office Extensibility Team, MSFT