How to run a Dynamic Script in Google Sheets (live edit)

Question

I am trying to run a script that will execute when the checkbox is marked "TRUE" and send an email to a client. I can not get the triggers to work and I can seem to make my own work either. I am putting the code below. Please help.

'''code'''
***Email Function
function SendEmail(i) {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //var lr= ss.getLastRow();
//for (var i = 2; i<=lr;i++){
  var currentEmail= ss.getRange(i, 1).getValue();
  //logger.log(currentEmail);
   var currentADID= ss.getRange(i, 3).getValue();
  MailApp.sendEmail(currentEmail,"Customer Ready"+ currentADID,"This customer should be ready to schedule and installtion date and time" );
}


Trigger Function
function CheckCD() {
 var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr= ss.getLastRow();
  for (var i = 2; i<=lr;i++){
    var currentDCheck= ss.getRange(i, 4).getValue();
    var x= onEdit():
if (currentDCheck == true)
SendEmail(i)
  }
}***

user_16118686 · Answer

You actually misunderstood how to use onEdit trigger, although you need to install the trigger in this case due to permission needed by MailApp.sendEmail

Anyways, you need to make use of the event object (e) as it holds the details about the edited cell's properties. See code below:

Code:

// Trigger function, install it under "Triggers" tab as you need permission for MailApp
function CheckCD(e) {
  // get the row and column of the edited cell
  var row = e.range.getRow();
  var column = e.range.getColumn();
  // proceed only if edited cell's range is D2:D and value is "TRUE"
  if (row > 1 && column == 4 && e.value == "TRUE") {
    // get the sheet where the cell was edited
    var ss = e.source.getActiveSheet();
    // get Email and ADID of the same row where checkbox was ticked
    var currentEmail = ss.getRange(row, 1).getValue();
    var currentADID = ss.getRange(row, 3).getValue();
    MailApp.sendEmail(currentEmail, "Customer Ready" + currentADID, "This customer should be ready to schedule and installtion date and time");
  }
}

Install trigger:

Make sure to choose On edit event type and choose the function you want to run. (in this case, CheckCD)

Sample data:

Output:

Note:

The behavior of this script is that every time someone checks a checkbox on range D2:D and the resulting value is TRUE, then it sends an email. (as it seems you have different email per row, but if not, send them all in one email)

References:

Installable Triggers