Google Sheets app script : How to send email on change in dropdown values?

Question

We have two sheets:

Sheet1 - It has one column & 10,000 rows with drop-down values - "approved" and "not approved".
Sheet2 - It has email-related details i.e. email, subject, message-approved, message-not-approved.

I want to send an automatic email if the drop-down value is changed. I want to send message-approved if someone changes Sheet1 dropdown selection to "approved" and message-not-approved if it is changed to "not approved".

I have been able to successfully send the email message when i run it in script editor but it is not triggering on drop down change. I understand that i need to use onedit trigger but not sure how to do that. Any help is appreciated. Here is the code.

function sendEmail(){

var ss1 = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss1.getSheetByName('Sheet1');
var valueToCheck = sheet1.getRange("A2").getValue();

if(valueToCheck == "approved"){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet2=ss.getSheetByName('Sheet2');
  var emailAddress = sheet2.getRange(2,1).getValue();
  var subject = sheet2.getRange(2,2).getValue();
  var message = sheet2.getRange(2,3).getValue();
  MailApp.sendEmail(emailAddress, subject, message);
}
else{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet2=ss.getSheetByName('Sheet2');
  var emailAddress = sheet2.getRange(2,1).getValue();
  var subject = sheet2.getRange(2,2).getValue();
  var message = sheet2.getRange(2,4).getValue();
  MailApp.sendEmail(emailAddress, subject, message);
}

}

soMario soMario · Accepted Answer · 2021-02-09T12:41:34

Explanation:

Make sure you take full advantage of the event object e.
Because your function uses MailApp which is a service that needs authorization, you can only use an installable onEdit trigger to execute as an onEdit function.
You can either create the installable trigger manually or programmatically. In the solution below, I show you the way to execute it programatically.

Solution:

Execute only and once the function create_onEdit and then upon user edits on cell A2 in Sheet1, the script will send emails.

// execute create_onEdit only and once 
function create_onEdit(){
    ScriptApp.newTrigger('sendEmail')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create(); 
}

// this function should never be executed manually!


function sendEmail(e){
  const ss = e.source;
  const arng = e.range;
  const ash = arng.getSheet();
  const row = arng.getRow();
  const col = arng.getColumn();
  // trigger upon edits in Sheet1 and A2 till A10000
  if(ash.getName()=="Sheet1" && row>=2 && row<=10000 && col == 1){
    if(arng.getValue() == "approved"){
      const sheet2=ss.getSheetByName('Sheet2');
      const emailAddress = sheet2.getRange(2,1).getValue();
      const subject = sheet2.getRange(2,2).getValue();
      const message = sheet2.getRange(2,3).getValue();
      MailApp.sendEmail(emailAddress, subject, message);
    }
    else{
      const sheet2=ss.getSheetByName('Sheet2');
      const emailAddress = sheet2.getRange(2,1).getValue();
      const subject = sheet2.getRange(2,2).getValue();
      const message = sheet2.getRange(2,4).getValue();
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

Be careful:

You should never execute sendEmail(e) manually as it will drop undefined errors. This function is a trigger function and it is meant to be automatically executed (triggered) upon user edits.
The function create_onEdit is responsible for creating the trigger, so you only need to execute that once and you are ready to go.

Google Sheets app script : How to send email on change in dropdown values?

1 Answers

Explanation:

Solution: