Google Sheet/script trigger for auto email when new cell value is yes/no

Question

New user here and not versed in code much. Im working on a COVID-19 form for our company and looking for some help with google script/trigger where when an employee fills out the google form and selects yes/no on the google form the google sheet that collects the data will send off an email based on the value in the cell.

IE: employee A enters "no" to agreeing to comply to policy it will email the manager informing them that someone entered "no".

I have a test formula that is working but when I set up a min by min trigger for it to kick off it just continues to kick off. Im assuming this is obviously due to it not having a code to only send new entries??

Any help would be greatly appreciated.

To reiterate im trying add a script that sends an email only ONCE per user when someone fills out the google form and they choose the wrong answer.

Code I have now:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var currentValue = sheet.getRange("F2:F1000").getValue();
  if (currentValue = ("Yes")) {
    MailApp.sendEmail("[email protected]", "ALERT: Please see person in question!", "The message body that you want to send.");
  }
}

Cooper Cooper · Accepted Answer · 2020-05-13T19:52:25

Try this:

You haven't identified the person in question.

function onMyEdit(e) {
  const sheet=e.range.getSheet();
  if(sheet.getName()=='Sheet1') && e.range.columnStart==6 && e.range.rowStart>1 && e.value=='No') {
    //MailApp.sendEmail("[email protected]", "ALERT: Please see person in question!", "The message body that you want to send.");
    Logger.log("ALERT: Please see person in question!");
  }
}

Since this is sending an email which requires authorization you will have to create an installable trigger using the Edit/Current Project Triggers menu.

Google Sheet/script trigger for auto email when new cell value is yes/no

1 Answers