Run script only if today() is in range on sheet-CodePudding

I built this attendance tracker for school. The spreadsheet is serviceable as-is but I’m hoping to make it better with your help. Each morning, apps script makes a copy of the template and renames it. Values are pulled into the sheet from the data sheet(in live version, data sheet is populated from a Google form). To improve the spreadsheet, I want to only run the script if today() is one of the dates in the range Calendar!A2:A. Spreadsheet link is here. Current code is below.

//=================================================================================
// Creates a copy template and renames the new sheet between 0200-0300 every day
//=================================================================================
function createNewSheet(){
  const sh = SpreadsheetApp.getActiveSpreadsheet();
  const ss = sh.getSheetByName("Template");
  const prot = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  const date = Utilities.formatDate(new Date(),"America/New_York","dMMMyy")

  let nSheet = ss.copyTo(sh).setName(date);
  nSheet.showSheet()
  
  let p;
  
  for (let i in prot){
    p = nSheet.getRange(prot[i].getRange().getA1Notation()).protect();
    p.removeEditors(p.getEditors());
    if (p.canDomainEdit()) {
      p.setDomainEdit(false);
    }
  } 
//copy and paste date
  const daily = sh.getSheetByName(date);
  daily.getRange('B1').activate();
  daily.getCurrentCell().setFormula('=today()');
  SpreadsheetApp.flush();  
  daily.getRange('B1').copyTo(daily.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

}

CodePudding user response：

I believe your goal is as follows.

You want to run the script when the today date is included in the cells of Calendar!A2:A.

In this case, how about the following modification?

From:

function createNewSheet(){
  const sh = SpreadsheetApp.getActiveSpreadsheet();

To:

function createNewSheet(){
  const sh = SpreadsheetApp.getActiveSpreadsheet();
  const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "M/d/yyyy");
  const sheet = sh.getSheetByName("Calendar");
  const res = sheet.getRange("A2:A"   sheet.getLastRow()).createTextFinder(today).findNext();
  if (!res) return;

function createNewSheet(){
  const sh = SpreadsheetApp.getActiveSpreadsheet();
  const today = new Date();
  const todayYear = today.getFullYear();
  const todayMonth = today.getMonth();
  const todayDate = today.getDate();
  const sheet = sh.getSheetByName("Calendar");
  const res = sheet.getRange("A2:A"   sheet.getLastRow()).getValues().some(([a]) =>
    a.getFullYear() == todayYear && a.getMonth() == todayMonth && a.getDate() == todayDate
  );
  if (!res) return;

In these modification, when the today date is included in the cells of Calendar!A2:A, the script below if (!res) return; is run.
In the 1st modification, the date values are used as the string.
In the 2nd modification, the date values are used as the date object.

From:

To:

References: