Office Script to delete all rows in a table receives timeout error

Question

I have an office script that is designed to delete all rows in a table. When I run it for 50 or less rows it runs with no problem, but times out when I test it for a larger number of rows (~600). Once this project is live I anticipate the table will have over 9000 rows when it needs to be cleared once a week. The error I recieve when running the script manually is:

Line 6: Table deleteRowsAt: Timeout

I'd prefer this clearing happen as part of the pre-existing power-automate process, rather than a manual process. The power automate process creates a copy of the file in an archive folder (which is working correctly) and then runs the script to clear the table in the live file.

Note the timeout occurs both within power automate and when running the script in excel online was well.

My script is below:

function main(workbook: ExcelScript.Workbook) {
  let Submission_Table = workbook.getTable("Table1");

  //Count the number of rows in the table
  let rowCount = workbook.getTable("Table1").getRowCount()

  //Delete the rows in the submission table
  Submission_Table.deleteRowsAt(0, rowCount);
}

Is there a way I can improve the efficiency of the deletion or increase the timeout limit?

OS: Windows 11

Office: 365

Thank you in advance for your help.

Answer 1

You can try this

function main(workbook: ExcelScript.Workbook) {
  let Submission_Table = workbook.getTable("Table1");
  //Get Table sheet
  let sheet1 = workbook.getWorksheet("Sheet1"); 

  //Get Table1 Range Address
  let TableRange = workbook.getTable("Table1").getRangeBetweenHeaderAndTotal().getAddress();

  //Clear all datas of Table1   
  sheet1.getRange(TableRange).clear()
}

Answer 2

I ended up using the below script that deletes 5 rows at a time and tells power automate to run it again until no rows are remaining. I tested at deleting 10, 15, 25, and 50 rows at a time, and 5 rows was the most efficient.

function main(workbook: ExcelScript.Workbook): boolean {
  let submissionTable = workbook.getTable("Table1");

  // Count the number of rows in the table
  let rowCount = submissionTable.getRowCount();

  // Variable to determine if the script needs to be run again
  let needsToRunAgain = false;

  // Define batch size for deletion
  const batchSize = 5;  // Adjust batch size as desired

  // Check if there are more rows than the batch size
  if (rowCount > batchSize) {
    // Delete delete the specified rows
    submissionTable.deleteRowsAt(0, batchSize);
    needsToRunAgain = true; // There may be more rows to delete
  } else {
    // Delete all remaining rows
    submissionTable.deleteRowsAt(0, rowCount);
    needsToRunAgain = false; // No more rows left to delete
  }

  // Return the result to Power Automate
  return needsToRunAgain;
}