Send email when cell is edited using email in parallel cell

Question

I have read a lot of onEdit and Triggers script but still I cannot achieve what I want to achieve, I code a lot in excel VBA and google sheet is very different. So the thing is based on my screenshot, what I want is to send an email once the cell contains "Approved", "denied", "In progress" and the email address must be based on the parallel of the edited cell. I'm dying to get this work done.

The code is based on the internet but I cannot tweak it based on my data/sheet.

Answer 1

You can add a custom function to a dropdown menu in the Spreadsheets UI with the following script. This will allow you to circumvent the onEdit() restriction that doesn't allow one to use the MailApp class, but it is at the cost of having users manually call the script instead of the script running automatically.

Here the user will select "Send E-Mail" from the dropdown menu, and it will prompt him/her for the Primary Key via an input prompt modal. The row of the corresponding key will be identified and an e-mail sent out after status is automatically changed to "approved". This script assumes that the spreadsheet contains at least four columns with header rows "Primary Key", "Description", "Email", and "Status" in any order.

Please note: this code was tested successfully. Please update lines 20 and 21 by replacing the square brackets and text contained therein that defines sheetURL and workSheetName variables.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
  .addItem('Send E-Mail', 'sendEmail')
  .addToUi();
}

function sendEmail(){

  // Display a dialog box with a title, message, input field, and "OK" and "Cancel" buttons. The
  // user can also close the dialog by clicking the close button in its title bar.
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Pop-Up Prompt', 'Please enter primary key:', ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  if (response.getSelectedButton() == ui.Button.OK) {
    Logger.log('The user entered the following primary key:', response.getResponseText());

    // Map the header rows in order that column position is not hard-coded.
    var sheetURL = '[ENTER YOUR SHEET URL HERE]';
    var workSheetName = '[ENTER YOUR WORKSHEET NAME HERE]';
    var sheet = SpreadsheetApp.openByUrl(sheetURL).getSheetByName(workSheetName);
    var lastColumn = sheet.getLastColumn();
    var headerRange = sheet.getRange(1, 1, 1, lastColumn);
    var headers = headerRange.getValues();

    for (var i=1; i<headers[0].length+1; i++) {
      switch (headers[0][i-1]){
        case "Primary Key":
          var primaryKeyIndex = i;
          break;
        case "Description":
          var descriptionIndex = i;
          break;
        case "Email":
          var emailIndex = i;
          break;
        case "Status":
          var statusIndex = i;
          break;
      }
    }
    // Header rows mapped.

    // Search for row corresponding to primary key.
    var primaryKey = response.getResponseText();
    var keyRow = findInColumn(columnToLetter(primaryKeyIndex), primaryKey);
    if (keyRow == -1){
      ui.alert('Primary Key "'+ primaryKey + '" not found.');
    } else {
      ui.alert('Primary Key "'+ primaryKey + '" found at row: ' +keyRow+ '.');
      sheet.getRange(keyRow, statusIndex).setValue("Approved");

      //Prepare Email
      var subject = "test";
      var email = sheet.getRange(keyRow, emailIndex).getValue();
      var body = "Hi, \n\n Your entry with primary key " + primaryKey + " is now approved.";
      MailApp.sendEmail(email, subject, body);

    }

  } else if (response.getSelectedButton() == ui.Button.CANCEL) {
    Logger.log('The user clicked cancel.');
  } else {
    Logger.log('The user clicked the close button in the dialog\'s title bar.');
  }
}

// Helper function to find corresponding row to data in column.
function findInColumn(column, data) {

  var sheet  = SpreadsheetApp.getActiveSpreadsheet();
  var column = sheet.getRange(column + ":" + column);  // like A:A

  var values = column.getValues(); 
  var row = 0;

  while ( String(values[row]) && String(values[row][0]) !== String(data) ) {
    row++;
  }

  if (String(values[row][0]) === String(data))
    return row+1;
  else 
    return -1;

}


// Helper function to convert Column Number to Column Letter
function columnToLetter(column){
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

Answer 2

I'd suggest that you not use the onEdit trigger for sending email. I think it's over used by many users. If you are, you will have to go with the installable triggers. This is an example email solution that looks pretty clean that came in yesterday.

Answer 3

You can use most of this code below. Modify the email portions to suit your needs. This code checks for sheet name to be 'Form Responses' and edited column header to be 'Status' as from pics given above.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var aSheet = ss.getActiveSheet();
  // check sheet name
  if (aSheet.getName() != 'Form Responses') return;

  var range = ss.getActiveRange();
  var row = range.getRow();
  var col = range.getColumn();
//  Logger.log(col);

  var headers = aSheet.getRange(1,1,1,aSheet.getLastColumn()).getValues()[0];
//  Logger.log(headers[col-1]);

  // check column header
  if (headers[col-1] != 'Status') return;

  var value = range.getValue();
  var values = ["approved", "denied", "in progress"]; // values to check for

  // check values
  if (values.indexOf(value.toString().toLowerCase()) == -1) return;

//  Logger.log(row);
  var rowValues = aSheet.getRange(row, 1, 1, aSheet.getLastColumn()).getValues()[0];
  Logger.log(rowValues);

  // change as necessary
  var recipient = rowValues[1]; // email is in 2nd column
  var body = 'Email body'; // create body
  var subject = 'Test'; // set subject

  // send email
  MailApp.sendEmail(recipient, subject, body);
}