Script for deleting all the sheets if the names (are dates) are before the actual date-CodePudding

SITUATION: I have a sheet for every day. Example: 18 january 2022 > sheet name is 18/01/2022

When I open the google spreadsheet file, the sheet with name 18/01/2022, becomes active. I use the script below for doing that.

function onOpen() {
  gotoPage();
}

function gotoPage() {
  const ss = SpreadsheetApp.getActive();
  const name = Utilities.formatDate(new Date(),ss.getSpreadsheetTimeZone(),"MM/dd/yyyy");
  const sh = ss.getSheetByName(name);
  sh.activate();
}

PROBLEM/QUESTION: All the sheets with the past dates are still in the Google spreadsheet. Can I add a script that deletes/hides the sheets with dates in the past, when opening the sheet? Example: When I open the sheet on 20/01/2022, all the sheets with dates before 20/01/2022 should be deleted or hidden.

Thanks!

CodePudding user response：

Try

function onOpen() {
  gotoPage();
}

function gotoPage() {
  const ss = SpreadsheetApp.getActive();
  var name = Utilities.formatDate(new Date(),ss.getSpreadsheetTimeZone(),"dd/MM/yyyy");
  var sh = ss.getSheetByName(name);
  sh.activate();
  var sheets = ss.getSheets()
  sheets.forEach(function(sh){
    if (sh.getName().match(/[0-9]{2}\/[0-9]{2}\/[0-9]{4}/)){
      name = name.replace(/([0-9]{2}).*([0-9]{2}).*([0-9]{4})/g, '$3-$2-$1')
      var newName = sh.getName().replace(/([0-9]{2}).*([0-9]{2}).*([0-9]{4})/g, '$3-$2-$1')
      if (newName < name) {
        // ss.deleteSheet(sh) // to delete
        sh.hideSheet() // to hide
      }
    }
  })
}