onEdit trigger to send email in google sheets

user_8453330 0
Viewed 88

I am limited in my abilities so please be patient with me. I have the following task. My first tab has a log of student names, time stamps, and locations. I have written a script that triggers an onEdit email when the last column has “Guidance Counselor” typed in. I am trying to alter the script so that the following task can occur. I want an onEdit email to be triggered when a specific student’s name is entered into column 1. However, I do not want to enter the script each time and type the list of student names that I want/need an email to be triggered for. My thought process is that I want to be able to have a separate tab with a list of names that I can update on a regular basis and when a cell value in column 1 matches a name that is in the other tab it triggers an email on that edit.
Here is the url for my sheet https://docs.google.com/spreadsheets/d/1JiZXEtB3-B0C10TEy2CIFjdckhH3THNZaVo7lVsIg9k/edit?usp=sharing

Here is the code for my script so far that triggers an email on “Guidance Counselor” I know its not that clean.

function emailonEdit(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 

  // EVENT VARIABLES 
  let range = e.range; 
  let row = e.range.getRow(); 
  let col = e.range.getColumn(); 
  let cellValue = sheet.getActiveCell().getValue(); 

  let studentName = sheet.getRange(row,1).getValue(); 
  let user = Session.getActiveUser().getEmail(); 
  let cellLocation = sheet.getActiveCell().getA1Notation(); 
  let url = "https://docs.google.com/spreadsheets/d/1z7j1qB6JJ3q6IzGn8eL1R4-hJs8-GqMXlu9NpiakUts/edit#gid=0"

  if ( col == 6 && cellValue == "Guidance Counselor") {
    // Browser.msgBox('It works'); 
    MailApp.sendEmail(
      'fayj@bathwildcats.org',
      studentName + ' enroute to GUIDANCE!',
      url + '&range=' + cellLocation
    ); 
  }

     if ( col == 6 && cellValue == "Guidance Counselor") {
    // Browser.msgBox('It works'); 
    MailApp.sendEmail(
      'jimfay55@icloud.com',
      studentName + ' enroute to GUIDANCE!',
      url + '&range=' + cellLocation
    ); 
  }; 

  if ( col == 6 && cellValue == "Guidance Counselor") {
    // Browser.msgBox('It works'); 
    MailApp.sendEmail(
      'NavianceStudent2022@gmail.com',
      studentName + ' enroute to GUIDANCE!',
      url + '&range=' + cellLocation
    ); 
  }; 
 

}
google-apps-script google-sheets triggers
2 Answers

Alternative Answer

First, you may convert the student list from 2 dimensional into 1 dimensional by using .toString() and .split() functions. Afterwards, you can use the .includes() function to check if the student name is included in your list.

function emailonEdit(e) {
  var url = "https://docs.google.com/spreadsheets/d/1z7j1qB6JJ3q6IzGn8eL1R4-hJs8-GqMXlu9NpiakUts/edit#gid=0";
  var cellLocation = e.range.getA1Notation();
  var dataRange = e.range;
  var dataColumn = dataRange.getColumn();
  var dataRow = dataRange.getLastRow();
  var studentName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Log").getRange(dataRow,1).getValue();
  var studentListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
  var studentList = studentListSheet.getRange(2,1,studentListSheet.getLastRow()-1,1).getValues();
  var checkName = studentList.toString().split(",").includes(studentName); /**returns true if the student name is included in the List*/
  (dataColumn == 6 && checkName && e.value == "Guidance Counselor") ? MailApp.sendEmail("fayj@bathwildcats.org, jimfay55@icloud.com, NavianceStudent2022@gmail.com", studentName + ' enroute to GUIDANCE!', url + '&range=' + cellLocation) : SpreadsheetApp.getActiveSpreadsheet().toast("sending failed");
}

I also added a toast() function to send a popup notification if the conditions of the trigger were not met.

References:

user_187037120 
function emailonEdit(e) {
  const sh = e.range.getSheet()
  const lsh = e.source.getSheetByName("student name list");
  const list = lsh.getRange(2,1,lsh.getLastRow() - 1).getDisplayValues().flat();
  let studentName = sh.getRange(e.range.rowStart,1).getValue(); 
  let user = Session.getActiveUser().getEmail(); 
  let cellLocation = e.range.getA1Notation(); 
  let url = "https://docs.google.com/spreadsheets/d/1z7j1qB6JJ3q6IzGn8eL1R4-hJs8-GqMXlu9NpiakUts/edit#gid=0"
  if ( e.range.columnStart == 6 && ~list.indexOf(studentName) && e.value == "Guidance Counselor") {
    MailApp.sendEmail('fayj@bathwildcats.org,NavianceStudent2022@gmail.com,jimfay55@icloud.com',studentName + ' enroute to GUIDANCE!',url + '&range=' + cellLocation ); 
  } 
}
user_72150910
Related