SpreadsheetNotification.js

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var dataRange = sheet.getRange(startRow, 1, sheet.getLastRow(), sheet.getLastColumn())
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var notificationSent = row[3];
    Logger.log(notificationSent);
    var emailAddress = row[0];
    Logger.log(emailAddress);
    var specimenId = row[1];       // Second column
    var modality = row[2];
    var note = row[5];
    if (notificationSent != true && emailAddress && specimenId && modality) {
      var rowNum = Number(i) + 2;
      if (note != '') {
        var subject = "Specimen #" + specimenId + " returned with note";
        var message = "Specimen #" + specimenId + " was returned with the following note: \n\n" + note;
      } else {
        var subject = "Images Ready for Specimen #" + specimenId;
        var message = "Images for " + modality + " specimen #" + specimenId + " are now available.";
        // If we have a network path, add it to the email body
        if (row[6] != '') {
          message += "\n\n" + row[6];
        }
      }
      MailApp.sendEmail(emailAddress, subject, message);
      //For testing, uncomment the line below, and comment the line above
      //MailApp.sendEmail('[email protected]', subject, message);
      SpreadsheetApp.getActiveSheet().getRange(rowNum,4).setValue(true);
    }
  }
}