Deleting a table then adding a new one in Office Scripts-CodePudding

I'm trying to create a Power automate flow that records a table, then clears it out for the next step. I currently have this script to create the table:

    function main(workbook: ExcelScript.Workbook,
        TableName: string = "Table1",
        SheetName: string = "Sheet1"
    ) {
        // Get the worksheet by name
        const selectedSheet = workbook.getWorksheet(SheetName);
            // Create a table using the data range.
        let newTable = workbook.addTable(selectedSheet.getUsedRange(), true);
        newTable.setName(TableName);
}

The problem is I can't write over an existing table so I need a step before this that clears out the existing table before a new one is drawn. Any suggestions?

CodePudding user response：

You can use workbook.getWorksheet(SheetName).getTables()[0].delete(); This will remove the first table in a sheet, or you can use workbook.getWorksheet(SheetName).getTable(TableName).delete() if you know the table name of the old table you are trying to delete.

You need to have the delete line in the main function. Also, you are using a usedRange to create the table, but after you delete the existing table, there will not be a used range in the workbook unless there is data outside of the table. Below, I have updated the script so that it gets the range of the old table and uses that range to create the new table.

function main(workbook: ExcelScript.Workbook, 
  tableName: string = "Table1", 
  sheetName: string = "Test") {
  let oldTable = workbook.getWorksheet(sheetName).getTables()[0];
  let oldTableRange = oldTable.getRange();
  oldTable.delete();
  // Get the worksheet by name 
  const selectedSheet = workbook.getWorksheet(sheetName); 
  // Create a table using the data range. 
  let newTable = workbook.addTable(oldTableRange, true); 
  newTable.setName(sheetName); 
}