I have a script that deletes sheets and retains a list of sheets that I do not want to delete, but the list of tabs I want to retain changes

Question

I have a project in which I want to delete a set of sheets in order to clean up and start over. I have a script that does this, but I am also adding some backup sheets that I do not want to delete. In other words the sheets not to delete changes as I add these backup sheets.

function DeleteAllSheets() {
  const sheets = ss.getSheets();
  //for (i = 0; i < sheets.length; i++) 
  sheets.forEach(function (item, i) {
    switch (sheets[i].getSheetName()) {
      case "roster":
      case "template":      
      case "Test":      
        break;
      default:
        ss.deleteSheet(sheets[i]);
    }
  })
}

I have another script that creates backups of the roster sheet from time to time and names the backups "Backup Test (Date)" or where the name of the back up sheet name always begins with 'Backup Test' but has a different date. Instead of having to go into the script to add these backups from being deleted, is there a way with the 'case' function to use some logic like "contains Backup Test" or "begins with Backup Test" to keep these from being deleted.

Answer 1

is there a way with the 'case' function to use some logic like 'contains Backup Test' or 'begins with Backup Test' to keep these from being deleted.

The function you're looking for is String.includes

if(sheet.getName().includes("Backup Test"))

You can also use regex with Regex.test:

if(/^(?:Backup Test.*|roster|template|Test)$/.test(sheet.getName())))

const deleteAllExcept = () => { 
  const ss = SpreadsheetApp.getActive()
  ss.getSheets().forEach(sheet => 
    /^(?:Backup Test.*|roster|template|Test)$/.test(sheet.getName()) && ss.deleteSheet(sheet))
}

Answer 2

A better way to do it is to have the names of the sheets to be saved in an array.

function deleteSheets() {
  const save = ["roster","templete","Test"]
  const sheets = ss.getSheets();
  sheets.forEach(function (sheet) {
      let name = sheet.getSheetName();
      if( save.indexOf(name) >= 0 ) return;
      if( name.indexOf("Backup Test") >= 0 ) return;
      ss.deleteSheet(sheet);
    }
  );
}

Answer 3

Does this do it?

function myfunction() {
  const shts = ss.getSheets();
  const xcl = ["roster", "template", "Test"]
  shts.forEach((sh, i) => {
    let nam = sh.getName()
    let idx = xcl.indexOf(nam);
    if (!~idx && !nam.includes("backup")) {
      ss.deleteSheet(sh);
    }
  });
}

Answer 4

SUGGESTION

You can also use the MATCH with REGEX in your existing SWITCH statement script to check if there are any sheet names that contain the word "backup" / "roster" / "Test" / "template", as seen on this tweaked script:

Tweaked Script

function DeleteAllSheets() {
  const sheets = ss.getSheets();
  sheets.forEach(function (item, i) {
    switch (sheets[i].getSheetName()) {
      case ((sheets[i].getSheetName().match(/(?:backup|roster|test|template)/gi)) ? sheets[i].getSheetName() : undefined):     
        break;
      default:
        ss.deleteSheet(sheets[i]);
    }
  })
}

This method was derived from this existing answer.

Demonstration

• Test spreadsheet before running the script

• Test spreadsheet after running the script