I am trying to delete all sheets except for specifically named ones (INPUT, Template, Summary). Very new to JS so I am sure it's way off:

//Delete sheets that are not named 'Input', 'Template', and 'Summary'
function deleteRedundantSheets() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheets()[0];
   var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

for (i = 0; i <= sheets.length; i++) {
  switch(sheets[i]) {
   case sheet.getSheetName() === "INPUT":
     break;
   case sheet.getSheetName() === "Template":
     break;
   case sheet.getSheetName() === "SUMMARY":
    break;
   default:
    ss.setActiveSheet(sheet[i]);
    ss.deleteActiveSheet;
    }
 }
}

I am trying to delete all sheets except for specifically named ones (INPUT, Template, Summary). Very new to JS so I am sure it's way off:

//Delete sheets that are not named 'Input', 'Template', and 'Summary'
function deleteRedundantSheets() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheets()[0];
   var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

for (i = 0; i <= sheets.length; i++) {
  switch(sheets[i]) {
   case sheet.getSheetName() === "INPUT":
     break;
   case sheet.getSheetName() === "Template":
     break;
   case sheet.getSheetName() === "SUMMARY":
    break;
   default:
    ss.setActiveSheet(sheet[i]);
    ss.deleteActiveSheet;
    }
 }
}

• javascript
• google-sheets

2 Answers 2

1. Once you have assigned the ActiveSpreadSheet to a variable, you can use that reference. In your code var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); can be replaced with var sheets = ss.getSheets(); as variable ss contains the reference to the active spreadsheet.

2. getSheets returns an array of sheets. So when you write var sheet = ss.getSheets()[0];, you are actually assigning the first element of the array in variable sheet. Hence in your case statements, you are always referring to the same sheet. So instead of using sheet.getSheetName() === "INPUT", you should use sheets[i].getSheetName() === "INPUT"

3. To delete a sheet, you can also use the deleteSheet function. This was added recently in Google Scripts.

Below is a modified code that you could use (I have not tested this, but you should get the idea).

function deleteRedundantSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  for (i = 0; i < sheets.length; i++) {
     switch(sheets[i].getSheetName()) {
     case "INPUT":
     case "Template":
     case "SUMMARY":
         break;
     default:
        ss.deleteSheet(sheets[i]);
    }
  }
}

function DeleteNewSheets(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var allowedSheetNames = ["My Stupid Sheet1", "Sheet 2"];

  for (var i = sheets.length - 1; i >= 0; i--) {
    var sheet = sheets[i];
    var sheetName = sheet.getName();

    if (!allowedSheetNames.includes(sheetName)) {
      ss.deleteSheet(sheet);
    }
  }
}

Do a Trigger on DeleteNewSheets() onchange, it will delete all sheets except those two. Simple.