onEdit trigger to send email in google sheets-CodePudding

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(
      '[email protected]',
      studentName   ' enroute to GUIDANCE!',
      url   '&range='   cellLocation
    ); 
  }

     if ( col == 6 && cellValue == "Guidance Counselor") {
    // Browser.msgBox('It works'); 
    MailApp.sendEmail(
      '[email protected]',
      studentName   ' enroute to GUIDANCE!',
      url   '&range='   cellLocation
    ); 
  }; 

  if ( col == 6 && cellValue == "Guidance Counselor") {
    // Browser.msgBox('It works'); 
    MailApp.sendEmail(
      '[email protected]',
      studentName   ' enroute to GUIDANCE!',
      url   '&range='   cellLocation
    ); 
  }; 
 

}

CodePudding user response：

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('[email protected],[email protected],[email protected]',studentName   ' enroute to GUIDANCE!',url   '&range='   cellLocation ); 
  } 
}

bitwise not(~)

CodePudding user response：

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("[email protected], [email protected], [email protected]", 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: