Automatically Generating an Email Notification When Certifications are Expiring-CodePudding

I have an excel spreadsheet containing the expiration dates for all certifications for each member of my team. As the expiration approaches, the cell automatically color shifts based on the remaining validity of the certification.

What I am looking to do is send an email notification when the certification is expiring in 90 days or less.

function sendEmail() {
  // EMAIL Check & Date
  // Fetch Cert Dates & Employees
  var CertExpiration = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(4,3,11,9); //All Cert Dates on Sheet
  var CertDate = CertExpiration.getValue(); // Get Certificate Date as a value
  var Employee = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(4,2,9,1); //Retrieve list of employee Names
  var EmployeeName = Employee.getValue(); // Set variable for email notification
  var Today = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(2,2,1,1); // Sets Today to Today() on sheet
  // Check Cert Expiration
  if (CertDate - Today < 90);  
    {
    // Fetch the email address
     var emailAddress = '[email protected]';
    // Send Alert Email.
    var message = 'Certificate for '   EmployeeName   ' expiring soon'; //
    var subject = 'CERT EXPIRING SOON | CV CERT TRACKING';
    MailApp.sendEmail(emailAddress, subject, message);
   
    }
}

I would like this email to contain a list of all EmployeeName that match the condition (CertDate - Today <90) if possible. Additionally, a method by which to prevent the email from being sent multiple times. Currently, the email sends successfully, but only contains the first referenced EmployeeName.

CodePudding user response：

I'm guessing you need a loop function to go through all your data.

function sendEmail() {
   const data =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(4,2,11,9).getValues();
   for (let i = 0; i< data.length ;i  ){
    //change the [i][0] the second number in [] to the actual index of your number column.  If your column number is 5, then the index will be 4
    //index start from 0.
    const EmployeeName = data[i][0];
    const CertDates = data[i].slice(1);
    const Today = new Date()
    //this arr will hold each cert date less than 90 days. 
    const arr = [];
    for(let j=0; j<CertDates.length ;j   ){
      const CertDate = CertDates[j];
      if(typeof(CertDate)== 'object' && Math.floor((CertDate  - Today ) / (1000*60*60*24)) < 90){
        console.log(CertDate)
        arr.push(CertDate);
      }
    }

    //if any emlpoyees has expire date less than 90 days. 1 or many. It will send email to employee name.
    if(arr.length>0){
      // Fetch the email address
      var emailAddress = '[email protected]';
      // Send Alert Email.
      var message = 'Certificate for '   EmployeeName   ' expiring soon'; //
      var subject = 'CERT EXPIRING SOON | CV CERT TRACKING';
      MailApp.sendEmail(emailAddress, subject, message);
    } 
 }