How to delete all rows in a table using Office Scripts

user_4474195 0
Viewed 3820

I have a table on a spreadsheet and I want to delete all the existing data. I use the code below which works except when the table is already empty.

// Get the row count
let rowCount = table.getRangeBetweenHeaderAndTotal().getRowCount();

// Delete all the rows
table.deleteRowsAt(0,rowCount);

The problem is that rowCount will return 1 even if the table is empty. When deleteRowsAt tries to delete a row in an empty table it returns an error.

In VBA we can use table.ListRows.Count and this will return 0 if the table is empty.

Example: I have 3 rows in the table enter image description here

If I select all the rows and delete them from the Table I get this: enter image description here

This table now has no rows but I have no way to get this result. As I said, in VBA we would use table.ListRows.Count and this would return 0 but I cannot seem to find the equivalent for Office Scripts.

office-scripts
2 Answers

UPDATE:

We now have getRowCount API on the table that you can use to solve this scenario. It'll return the actual rows (not counting the header or expansion row).

    // Assuming there's a table in the workbook named Table1
    let rowCount = workbook.getTable('Table1').getRowCount(); // Table1
    // Assuming there's a table in the workbook
    let rowCount = workbook.getTables()[0].getRowCount(); // First table

====

OLD ANSWER

I think we are lacking an API that will provide row count. We'll add that to the backlog. A workaround is this -

function main(workbook: ExcelScript.Workbook) {
  let table = workbook.getTable("Table26");
  let rowCount = table.getRangeBetweenHeaderAndTotal().getRowCount();
  try { 
    table.deleteRowsAt(0, rowCount);
  } catch (e) {
    if (rowCount === 1 && e.code === 'InvalidArgument') {
       console.log("This error means there's no row to delete.")
    }
  }
}

If row count is 1 and the error code is a specific one that's returned when we delete the 'insert-row', then we can ignore the error. Again, a better approach would be to use an API on the table object to get the row count and delete rows only when row count >= 1. We'll investigate further to improve the experience.

user_41624150

I ran some quick tests deleting 5,000 rows (5 columns, different data types) using deleteRowsAt() and it consistently took 20 to 30 seconds. But ~10,000 rows or more and I got a timeout every time, the script never finished. Calling the script from Flow also gave me gateway timeouts with multiple reattempts before failing.

table.deleteRowsAt(0, rowCount);

But using getRangeBetweenHeaderAndTotal() to reference a range for deletion worked well, 200,000 rows of the same test data in about 2 seconds. The documentation mentions ranges are faster, they certainly are in my tests.

table.getRangeBetweenHeaderAndTotal().delete(0);
user_129496970
Related