deleting sheets with a date older than the past two days-CodePudding

Working in Google Scripts, I'm trying to create a function that will look at the names of all tabs in a Google Sheet, and delete all sheets that meet these conditions: 1) tab name include a date (sheet names that do have a date are prefaced with some other text - the mm/dd/yyyy formatted date is in the string) and 2) the date in that sheet name is older than today's date minus 2 days).

There are two sheet names that include dates: "Leadership Review mm/dd/yyyy" and "Leadership Review w/notes mm/dd/yyyy". I have a script that auto-runs DAILY to create these sheets, so the goal is to automate a one-time clean-up for old sheets and set up a daily trigger to auto-run that function.

So far, I've created an array to capture the names of each sheet name ("tabNameArray") and have a regexp for use in matching for mm/dd/yyyy text that shows up in a sheet name.

My thought on how this would work - not sure how to accomplish 2 and 3:

create that array
parse the array - match each sheetname in the array against the regexp mm/dd/yyyy to identify sheetnames with a date.
A loop through that array... IF that sheetname has a date (create a new array or subarray with just those? doesn't seem necessary, but a thought), AND that date is > 2 days from today(), delete those sheets.

function deleteOldReportSheets() {
  
  var sheetNameArray = new Array();
  var sheetWithDatesArray = new Array(); //not sure this is necessary
  var dateRegex = new RegExp("[0-3]?[0-9]\/[0-3]?[0-9]\/(?:[0-9]{2})?[0-9]{2}"); //for mm/dd/yyyy match, tested successfully on regex101.com
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
for (var i=0 ; i<sheets.length ; i  ) sheetNameArray.push( [ sheets[i].getName() ] ) // populates the sheetNameArray with names of each sheet

(do this: for sheets that have a date that is older than 2 days from today, delete...)

I'd appreciate support to code this - even better if there's a more efficient way than what I started thinking through.

Thanks for your assistance.

CodePudding user response：

Description

The following example will compare the named sheets containing the date to today and indicate which sheets should be deleted. I leave the deletion operation to the OP.

Script

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheets = spread.getSheets();
    let dateRegex = new RegExp("[0-3]?[0-9]\/[0-3]?[0-9]\/(?:[0-9]{2})?[0-9]{2}"); //for mm/dd/yyyy match, tested successfully on regex101.com
    let today = new Date();
    console.log("today = " Utilities.formatDate(today,"GMT","MM/dd/yyyy"));
    today = new Date(today.getFullYear(),today.getMonth(),today.getDate()-2);
    for( let i=0; i<sheets.length; i   ) {
      let sheet = sheets[i];
      let name = sheet.getName();
      console.log("Sheet name = " name);
      let match = name.match(dateRegex);
      if( match ) {
        match = new Date(match[0]);
        if( match.valueOf() <= today.valueOf() ) {
          console.log("delete");
        }
        else {
          console.log("keep");
        }
      }
      
    }
  }
  catch(err) {
    console.log(err);
  }
}

Console.log

11:45:16 AM Notice  Execution started
11:45:17 AM Info    today = 05/01/2022
11:45:17 AM Info    Sheet name = Sheet1
11:45:17 AM Info    Sheet name = Sheet 04/29/2022
11:45:17 AM Info    delete
11:45:17 AM Info    Sheet name = Sheet 04/30/2022
11:45:17 AM Info    keep
11:45:17 AM Info    Sheet name = Sheet 05/01/2022
11:45:17 AM Info    keep
11:45:16 AM Notice  Execution completed

Reference

CodePudding user response：

I think it can be something like this:

function myFunction() {
  const reg = new RegExp(/\d{2}\/\d{2}\/\d{4}/);
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const if_two_days_ago = date =>
    new Date(date).valueOf() < new Date().valueOf() - 3600000 * 48;

  ss.getSheets()
    .filter(s => reg.test(s.getName()))
    .filter(s => if_two_days_ago(s.getName().match(reg)))
    .forEach(s => { console.log('bye-bye -->', s.getName()); ss.deleteSheet(s) });
}

CodePudding user response：

Delete Sheets with dates older than two days

function delSheets() {
  const ss = SpreadsheetApp.getActive();
  const dt = new Date();
  const dtv = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate() - 2).valueOf();//date threshold value
  ss.getSheets().forEach(sh => {
    let m = sh.getName().match(/\d{1,2}\/\d{1,2}\/\d{4}/g);//includes a date
    if(m && new Date(m[0]).valueOf() < dtv) {
      ss.deleteSheet(sh);
    }
  });
}