I am looking for a script to help send an email when a certain cell reaches a threshold value.

Here is the sheet that I have built, attached to a form with some of my own formulas.

Script

function filterData(e) {
  /** Get the most recent submission on the Form Responses 1 sheet*/
  var currentStudentRecords = e.source.getSheetByName('Form Responses 1').getDataRange().getValues().slice(-1).pop().filter(z => z);

  /** Gather current data from Tracking sheet get latest count of every students.
  Structure of the columnDTracking array is [sheet_row, student_name, student_count] 
  */
  var columnDTracking = e.source.getSheetByName('Tracking').getRange('C:D').getValues().map((x, index) => { return x[1] != '' ? [(index   1), x[0], x[1]] : null }).filter(x => x);

  /** Filter the latest student record submission from sheet "Form Responses 1" who has more than 4 records on column D
   * from the Tracking sheet
   */
  var res = currentStudentRecords.map(check => {
    return columnDTracking.filter(find => { return find[1].toLowerCase() == check.toLowerCase() && find[2] >= 4 })
  }).map(JSON.stringify).filter((e, i, a) => i === a.indexOf(e)).map(JSON.parse);

  /** Send email to the student(s) who has more than 4 counts*/
  sendEmail(res)
}

/** Send email function */
function sendEmail(studentData) {
  var list = studentData.map(x => { return x.join(',').split(',')[1] == undefined ? null : '\n'   x.join(',').split(',')[1] }).filter(z => z);
  list.length == 0 ? console.log('no student with more than 4 counts') :
  MailApp.sendEmail({
    to: "YOUR_EMAIL@YOUR_DOMAIN",
    subject: "TESTING EMAIL",
    body: "These/This student(s) has more than 4 instances: "   list
  });
}

Demonstration

Latest submission sample:

Current count on Tracking sheet

Sample Email Sent

Sample logs if no student contains 4 counts on the latest submission and no email will be sent

Reference

• Event Objects