Cannot access event object

[Congestion]

What I want to achieve is that when the content of a certain cell in the spreadsheet is changed to a certain value, an automated mail should be sent.

First Try:

using the onEdit(e) function.

If I use this standard Google Script function I can access the event object e. The onEdit() function does not need an extra separate trigger.

function onEdit(e) 

{ 

var range = e.range; 

range.setNote('Last modified: ' + new Date()); 

... 

MailApp.sendEmail(...) 

 }

However it is not allowed to send a mail with onEdit(). For security reasons the access to MailApp.sendEmail() is prohibited when the onEdit() fuction is used.

https://developers.google.com/apps-script/guides/triggers They cannot access services that require authorization. For example, a simple >trigger cannot send an email because the Gmail service requires authorization...

For this reason, I have declared a separate function. For example my MyEdit() function:

function MyEdit(e) 

{ 

var range = e.range; 

range.setNote('Last modified: ' + new Date()); 

... 

MailApp.sendEmail(...) 

 }

I created a trigger for this function. With the settings:

• Function to run: MyEdit
• Deployment: Head
• Event source: From spreadsheet
• Event type: On edit

When the cell is changed, my MyEdit() is called, via the trigger but I cannot access the event object. For whatever reason. When accessing the object I always get error messages.

TypeError: Cannot read property 'setNote' of undefined at MyEdit(code:3:9)

The access to setNote is just an example. When I try to access different data on Event e I also get an error (i.e.:e.range.getRow();

I cannot found the root cause of this behaviour.

Maybe somebody has a solution!

Many thx in advance

[Clark Lind]

You may have to do this the hard way:

function MyEdit() {
 var range = SpreadsheetApp.getActive().getActiveCell();  //whatever triggered the event will be the active cell

 range.setNote('Last modified: ' + new Date()); 

... 

MailApp.sendEmail(...) 

}

Here is an old function that still works. It has an onEdit trigger and checks if the dropdown in Col 2 has been changed to RED, then sends an email to me. 
This could probably be refactored with modern JS.

//=====================

function sendAlertEmail() {

  

  var sh = SpreadsheetApp.getActive();

  var sheet = sh.getActiveSheet();

  var aRow = sheet.getActiveCell().getRowIndex();

  var aCol = sheet.getActiveCell().getColumn();

  var cellCheck = sheet.getRange(aRow, 2).getValue();

 

  if (cellCheck == "RED" && aCol == 2) {

   

    var subject = "Alert Notification from Ops Report";

    var systemProduct = sheet.getRange(aRow, 1).getDisplayValue();

    var status = sheet.getRange(aRow, 2).getDisplayValue();

    var trendRaw = sheet.getRange(aRow, 3).getDisplayValue();  

    var email = "XXXXXXX"

    var trend = "";

  

  //convert Trend (I,W,NC from Col 3) to Text (Improving, Worsening, No Change)

  switch(trendRaw) {

    case "NC":   

      trend = "No Change";

      break;

    case "I":

      trend = "Improving";

      break;

    case "W":

      trend = "Worsening";      

  }

 

    MailApp.sendEmail({

      to: email,

      subject: subject,

      htmlBody: "The following Status has changed to RED.<P>" + 

      "Product or service:  " + systemProduct + "<P>" +

      "Status:  " + status + "<P>" +

      "Trend:  " + trend + "<P>" +

      "From Row:  " + aRow 

    });

  }

}