CODEWITHSUNDEEP
excel-onlineoffice-scripts
nouptime
nouptime

Reputation: 10457

How to delete a worksheet if it exists and create a new worksheet with the same name with Office Scripts

I have a script that makes a copy of an existing worksheet and generates a table within this new worksheet called Lists.

Looking for a way to delete the new worksheet whenever the script runs again, but I keep getting the 'Worksheet activate: The requested resource doesn't exist' error with selectedSheet.activate();

let selectedSheet = workbook.getWorksheets()[0];
selectedSheet.getAutoFilter().remove();

// Duplicate worksheet.
let itemsName = "Lists";
let sheetItem = workbook.getWorksheet(itemsName);
// If `null` wasn't returned, then there's already a worksheet with the same name.
if (sheetItem) {
  console.log(`Worksheet ${itemsName} already exists. Deleting...`);
  // Delete the sheet.
  sheetItem.delete();
 } else {
  let selectedSheet = workbook.getWorksheets()[0];
}
selectedSheet.activate();
let itemsSheet = selectedSheet.copy(ExcelScripWorksheetPositionType.before, selectedSheet);
itemsSheet.setName("Lists");
itemsSheet.activate();

Upvotes: 0

Views: 2148

Answers (1)

kinuasa
kinuasa

Reputation: 91

I suggest to use the optional chaining operator (?.).

function main(workbook: ExcelScript.Workbook)
{
  const itemsName = "Lists";
  workbook.getWorksheet(itemsName)?.delete();
  const selectedSheet = workbook.getFirstWorksheet();
  const itemsSheet = selectedSheet.copy(ExcelScript.WorksheetPositionType.before, selectedSheet);
  itemsSheet.setName(itemsName);
  itemsSheet.activate();
}

Please refer to the below link.

  1. https://learn.microsoft.com/en-us/office/dev/scripts/develop/best-practices#verify-an-object-is-present
  2. https://github.com/sumurthy/officescripts-projects/tree/main/Top%205%20Tips

Upvotes: 3

Related Questions