How to delete all rows in a table using Office Scripts

Question

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

If I select all the rows and delete them from the Table I get this:

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.

Sudhi Ramamurthy · Accepted Answer · 2020-09-02T16:28:11.013

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.

score 4 · Answer 2 · answered Sep 29 '20 at 23:06

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);

score 0 · Answer 3 · answered Jun 22 '23 at 13:36

If you want to delete the entire row, we need to use the property “EntireRow,” then, we need to use the method “Delete” to delete the entire row of the cell we have selected.

row.getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);

This example function will loop the first table in the first sheet and delete rows with the word "DELETE" in Column A.

function deleteRows(workbook: ExcelScript.Workbook)
{
  let selectedSheet = workbook.getActiveWorksheet();
  let myTable = workbook.getTables()[0];
  let rowCount = myTable.getRangeBetweenHeaderAndTotal().getRowCount();

  for (let x = 1; x < rowCount; x = x + 1) {
    let row = myTable.getRangeBetweenHeaderAndTotal().getRow(x - 1);
    if (row.getColumn(0).getValue() == "DELETE") {
      console.log("deleting " + row.getColumn(8).getValue());
      row.getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
    }
  }    
}

How to delete all rows in a table using Office Scripts

3 Answers3