Office script to delete certain sheets while keeping others

Question

I've created the following code to delete all excel worksheets except for four sheets, namely Water, NH3, AB, and NA.

The code runs fine till row 46 and stops with the following error, please help as I can't unhide those sheets once I set their status to "veryHidden".

I tried setting their status to "hidden" but gave the same error.

I think they were actually deleted and that's why the code stops when I try to bring them back.

Line 46: Cannot read properties of undefined (reading 'setVisibility')

function main(workbook: ExcelScript.Workbook) { //Assign the "Water" worksheet to the variablelet sa1 = workbook.getWorksheet("Water");//Set the visibility of the sa1 worksheet to veryHiddensa1.setVisibility(ExcelScript.SheetVisibility.veryHidden);

//Assign the "NH3" worksheet to the variablelet sa2 = workbook.getWorksheet("NH3");//Set the visibility of the sa2 worksheet to veryHiddensa2.setVisibility(ExcelScript.SheetVisibility.veryHidden);

//Assign the "AB" worksheet to the variablelet sa3 = workbook.getWorksheet("AB");//Set the visibility of the sa3 worksheet to veryHiddensa3.setVisibility(ExcelScript.SheetVisibility.veryHidden);

//Assign the "NA" worksheet to the variablelet sa4 = workbook.getWorksheet("NA");//Set the visibility of the sa4 worksheet to veryHiddensa4.setVisibility(ExcelScript.SheetVisibility.veryHidden);

//Assign the worksheet collection to the wsArr variablelet wsArr = workbook.getWorksheets();

//Assign the worksheet "Sheet3" to the wsToKeep variablelet wsToKeep = workbook.getWorksheet("Sheet3");

//Loop through all worksheets in the worksheet collectionfor (let i = wsArr.length - 1; i >= 0; i--) {

//Check if the item in the wsArr collection is equal to the wsToKeep variable//Only proceed if not equal toif (wsArr[i].getName() != wsToKeep.getName()) {

//Make the worksheet hidden prior to deletion to remove error riskwsArr[i].setVisibility(ExcelScript.SheetVisibility.hidden)

//Delete the worksheetwsArr[i].delete();};}

//Assign the "Water" worksheet to the variablelet sa5 = workbook.getWorksheet("Water Consumption-ORG");//Set the visibility of the sa5 worksheet to veryHiddensa5.setVisibility(ExcelScript.SheetVisibility.visible);

//Assign the "NH3" worksheet to the ws variablelet sa6 = workbook.getWorksheet("NH3 Consumption");//Set the visibility of the sa6 worksheet to veryHiddensa6.setVisibility(ExcelScript.SheetVisibility.visible);

//Assign the "AB" worksheet to the ws variablelet sa7 = workbook.getWorksheet("AB Down Time");//Set the visibility of the sa7 worksheet to veryHiddensa7.setVisibility(ExcelScript.SheetVisibility.visible);

//Assign the "NA" worksheet to the ws variablelet sa8 = workbook.getWorksheet("NA Down Time");//Set the visibility of the sa8 worksheet to veryHiddensa8.setVisibility(ExcelScript.SheetVisibility.visible);//Delete the wsToKeep worksheetwsToKeep.delete();

}

Answer 1

Dear Saad G.,

Good day! Thanks for posting in Microsoft Community.

We would love to help you on your query, however, our team focuses on general query, for example, installation and activation issue of Office 365 products. The situation you mentioned is related to Office Scripts, and we have a specific channel Microsoft Q&A Community coping with issues in SharePoint server, I suggest you post a new thread (assign tabs office-scripts-excel-dev) there for expert help. Sorry for that our category may have limited resources on checking on the situation further.

At the same time, we will also keep this thread open, so other Community members and Experts can also share their suggestions and inputs.

Thank you for your cooperation and understanding!

Sincerely,

George | Microsoft Community Moderator