Include Range in GAS Switch Case, Delete Google Sheets tabs not referenced in the range-CodePudding

I have a sheet with list of tabs names in A2:A of "Masters" tab. I would like to delete existing tabs which are not in the above list. I found this script helpful:

 function DELETESHEETS() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheets = ss.getSheets();
      for (i = 0; i < sheets.length; i  ) {
         switch(sheets[i].getSheetName()) {
         case "Master":
         break;
         default:
         ss.deleteSheet(sheets[i]);}}}

How to add the range of tab names to this exemption?

CodePudding user response：

I believe your goal is as follows.

There are the sheet names in the column "A" of "Master" sheet. You want to retrieve these sheet names as a list.
You want to delete the sheets that the sheet name is not included in the list.

In your script, the list of sheet names of the column "A" of "Master" sheet is not used. When this list is reflected to your script, how about the following modified script?

Modified script:

function DELETESHEETS() {
  var sheetName = "Master";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var list = [[sheetName], ...sheet.getRange("A2:A"   sheet.getLastRow()).getValues()].reduce((o, [a]) => (o[a] = true, o), {});
  ss.getSheets().forEach(s => {
    if (!list[s.getSheetName()]) ss.deleteSheet(s);
  });
}

Note:

When this script is run, the sheets in the active Spreadsheet are deleted. Please be careful about this. So for testing this script, I would like to recommend using a sample Spreadsheet including the sample sheets.

References:

CodePudding user response：

Switch isn't suited in this scenario. Use filter with Set:

function DELETESHEETS_so70376198() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const exclude = new Set(
    ss
      .getSheetByName('Master')
      .getRange('A2:A')
      .getValues()
      .flat()
  );
  ss.getSheets()
    .filter((sh) => !exclude.has(sh.getName()))
    .forEach((sh) => ss.deleteSheet(sh));
}

Make sure to also include the Master name in Master!A2:A, else it will get deleted as well.

CodePudding user response：

function delShts() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Master');
  const kshts = sh.getRange(2,1,sh.getLastRow() - 1).getValues().flat();
  ss.getSheets().filter(sh => !~kshts.indexOf(sh.getName())).forEach(sh => ss.deleteSheet(sh));
}