Send an Email When a Cell is Edited with a Certain Value

Question

I've got a Google Form set up to record user-reported errors about a database we maintain. The responses go into a Google Sheet and the users email is recorded. Essentially, I'd like to have a status field in that Google Sheet -- and when it's set to something like "Complete" (which would be in the same row as the response) I would like an email to be automatically sent to the user that submitted the response, letting them know the status of their response is complete. So sort of like a ticket system that many companies use (but we don't have a lot of bandwidth to set this up, so we're looking for something simple/free).

Answer 1

You cannot send an email inside the onEdit trigger. So you'll have to save the edits somewhere, maybe inside UserProperties, and have a time-based trigger that sends this value to your email every minute.

See: Email Notifications in Google Spreadsheets.

Google Spreadsheet support email notifications for row edits (tools - notification rules) but the last time I tried it, it never worked.

Answer 2

Create an onEdit() function, capture the cells value when it's edited, and run the code.

function onEdit(e){
  // Capture the cells value after it is edited
  var valCell = e.value;
  Logger.log('The cell value is: ' + valCell);

  if (valCell === "Complete") {
    //Get the range of the cell
    var theRange = e.range;

    //Get the row of the range
    var theRowOfEdit = theRange.getRow();

    // Returns the cell with email
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var cell = sheet.getRange("B" + theRowOfEdit.toString());

    //Get the user email
    var userEmail = cell.getValue();

    //Send the email
  }
}

This is not the complete code that you need. But, set this code up; test it, debug it, and if you have a specific question, post another question with the error message and line of code that isn't working.

Use debug in the code editor, and/or Logger.log() statements to debug the code.