I am trying to have this script loop per column. If a date in the column is the same as today's date, the script will send an email using the persons name and email address detailed in the same row.

Table of Expiry Dates

function myFunction() 
{
  var spreadSheet = SpreadsheetApp.getActiveSheet();
  var dataRange = spreadSheet.getDataRange();

  var data = dataRange.getValues();
  var date = Utilities.formatDate(new Date(), "GMT", "dd/MM/yyyy")
  
  
  var engcheck1 = dataRange.getColumn[2];
  
  for (var i = 1; i < data.length; i  )
  if (engcheck1 == date)
  {
      var row = data[i];
      var emailAddress = row[7]; //position of email header — 1
      var name = row[1]; // position of name header — 1
      var subject = "Your Currency for Engineering is now expired";
      var text = "Please note that your currency has expired today ("   date  "). You are now unauthorised to carry out any engineering tasks until you have been signed off by a member of the engineering team.";
      var message = "Dear "   name   ","   "\n\n"   text   "\n\n"   "Please get back in currency at your earliest convenience."   "\n\n"   "Many thanks,"   "\n"   "Dominic Paul"
      MailApp.sendEmail(emailAddress, subject, message);
  }(i);
}

Without the 'If' statement, the script will loop through each row and sending out an email address. When I include the 'If' statement, nothing is sent to the email addresses. Either I am using the if statement incorrectly or I am not targeting the column accurately. I tried creating a variable engcheck1 for column 2 ONLY but no email is sent despite today being one of the dates in column 2 (C).

CodePudding user response：

I did not understand, what is var engcheck1 = dataRange.getColumn[2];.
If you need to send and email if date is the same as the value in C column, provided column C has type Plain text, this is the answer:

function myFunction() {
  var spreadSheet = SpreadsheetApp.getActiveSheet();
  var dataRange = spreadSheet.getDataRange();

  var lastRow = SpreadsheetApp.getActiveSpreadsheet().getDataRange().getNumRows();
  var dateData = spreadSheet.getRange("C1:C"   lastRow).getDisplayValues().flat();
  var data = dataRange.getValues();
  
  var date = Utilities.formatDate(new Date(), "GMT", "dd/MM/yyyy")

  for (var i = 1; i < data.length; i  ) {
    var rowDate = dateData[i];
    var row = data[i];
    // if the date in row is the same as date variable
    if (rowDate === date) {
      var emailAddress = row[7]; //position of email header — 1
      var name = row[1]; // position of name header — 1
      var subject = "Your Currency for Engineering is now expired";
      var text = "Please ...";
      var message = "Dear ...";
      MailApp.sendEmail(emailAddress, subject, message);
    };
  }
}

CodePudding user response：

Try this:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();
  var rg = sh.getDataRange();
  var vs = rg.getValues();
  const dt = new Date();
  var dtv = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  vs.forEach((r,i) => {
    let d = new Date(r[2]);
    let dv = new Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf();
    if(dv == dtv ) {
      //send you email
    }
  })
}