Send email if cells' value is below certain value

Question

I am absolutely new to this and pulling my hair out trying to make a script for work. I need to check employee's certifications on a daily basis and have them re-certified if expired. Here is a "testing" spreadsheet with random data: https://docs.google.com/spreadsheets/d/1vJ8ms5ZLqmnv4N1upNHD4SRfgIgIbEAAndvUNy-s9S4/edit?usp=sharing

It lists personnel working for my department along with their badge numbers and number of days their certifications are valid for. The original sheet takes the days value from another spreadsheet, but it shouldn't affect this (I think?).

What I'm trying to achieve is write a script that checks all numbers in C3:G24. If any cell in this range has a value lower than 15, it should pull their badge number and name from the same row, along with the "days" their certificates are valid for and send an email containing all this data.

For example

Subject: Certifications about to expire

E-mail content: Your employee's (Name from col B) certification with Badge# (# from Col A) will expire in X days (where X = the number from range C3:G24).

So far my best attempt was to at least make it send ANY e-mail on edit, but failing miserably trying to adapt any script found online. Here is what worked to at least send an e-mail but then I did something to break it:

function checkValue()
{
  var ss = SpreadsheetApp.getActive();//not sure if needed, the spreadsheet eventually doesn't have to be open/active
  var sheet = ss.getSheetByName("Certifications");

  //not sure if this is ok
  var valueToCheck = sheet.getRange("C3:G24").getValue();

  //Here I'd like to change the "days" based on needs
  if(valueToCheck < 15)
  {
    MailApp.sendEmail("[email protected]","Certifications","Your employee certification will expire soon" + valueToCheck+ ".");
  }
}

Can someone please help guide me in the right direction?

Answer 1

here is what I would do:

function checkValue()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Certifications");
  var valueToCheck = sheet.getDataRange().getValues();
  var resultValues = [];
  valueToCheck = valueToCheck.filter(function(element){
    var val = 0
    if (parseInt(element[2]) < 15)
    {
      resultValues.push({col: "Cert1", value: element[2]})
      return (true);
    }
    else if (parseInt(element[3]) < 15)
    {
      resultValues.push({col: "Cert2", value: element[3]})
      return (true);
    }
    else if (parseInt(element[4]) < 15)
    {
      resultValues.push({col: "Cert3", value: element[4]})
      return (true);
    }
    else if (parseInt(element[5]) < 15)
    {
      resultValues.push({col: "Cert4", value: element[5]})
      return (true);
    }
    else if (parseInt(element[6]) < 15)
    {
      resultValues.push({col: "Cert5", value: element[6]})
      return (true);
    }
  })
  for(var i = 0; i < valueToCheck.length; i++)
  {
    MailApp.sendEmail("[email protected]","Certifications","your employee's " + valueToCheck[i][1] + "with badge " + valueToCheck[i][0]  + " certification will expire in " + resultValues[i].value + " days (column " + resultValues[i].col + ").");
  }
}

1. use the getValues() function to retrieve datas.
2. then filter the values based on condtion of value being less than 15
3. at the same time grab the column name and the less than 15 data.
4. parse through both arrays to send datas to your mail