CODEWITHSUNDEEP
javascriptgoogle-sheets
user3511512
user3511512

Reputation: 115

Deleting all but specified sheets in Google Script

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;
    }
 }
}

Upvotes: 1

Views: 9877

Answers (2)

Amit Ghosh
Amit Ghosh

Reputation: 1606

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.

Upvotes: 0

Konstant
Konstant

Reputation: 2199

  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]);
    }
  }
}

Upvotes: 3

Related Questions