Send email when certain data is selected from drop down

Question

For example, if an action is taken within Column A, then an email is sent to the corresponding email address in Column E. We use Google Sheets to send route schedules to our customers and it would be very helpful if when our field workers are en route to a customer, an email could be generated. I've seen options like this using scripting, but none which sends to different email addresses depending on which cell is edited.

The best way would be to have a data validation drop down menu with statuses "En route, servicing, complete." So that once status is set to "en route" an email notification would be sent to the address in another row.

Below is the script I've got so far:

function sendEmail() { 
    // return all data in active spreadsheet
    var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
    for (i in values.length) { //iterate over each row
        // get value of column 1 (aka B) for this row 
        // this is the conditional cell for emailing
        var data= values[i][1];
        // get email address from column 2 (aka C)
        var emailAddress = values[i][2];
        var subject = "We are en route with your order"
        var message = "Our crew will arrive in 5 minutes";
        // if data cell equals En Route, send email for this row
        if (data = "En Route") ;{
            MailApp.sendEmail(emailAddress, subject, message);
        }
    }
}

I'm guessing there might need to be a onEdit() function somewhere in there.

Unknown Unknown · Accepted Answer · 2016-03-11T22:53:48

You can't send email from onEdit() because simple triggers are not authorized to do this. Instead, you should create an installable triggers, e.g., by selecting Resources > This project's triggers in the script editor. Set it as "from spreadsheet > on edit".

Also, your approach (scanning the entire sheet with every edit, and sending email to anyone with matching "En Route") would likely result in multiple emails sent to the same person: every time an edit is made while their cell is "En Route", they'd get an email. You don't want that. Instead, use the event object which pinpoints the location of edit and the new value entered. This is what the function could look like.

Note that the row/column numbers in Apps Script methods are 1-based: column 2 is B, etc.

function emailOnRoute(e) { 
  if (e.value == 'En Route' && e.range.getColumn() == 2) {
    var row = e.range.getRow();
    var sheet = e.range.getSheet();
    var emailAddress = sheet.getRange(row, 3).getValue();
    var subject = "We are en route with your order"
    var message = "Our crew will arrive in 5 minutes";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Send email when certain data is selected from drop down

1 Answers