Trigger email if cell changed in Google Sheet

Question

I have a sheet where we track department metrics in Google Sheets. I am trying to get it to email when a metric changes. I am using counta (in range P2) to monitor if a new metric came in and I want to trigger an email if a value in the range changes.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var value = ss.getSheetByName("EmailServices").getRange("P2").getValue().toString();
  var last = ScriptProperties.getProperty("last");

  if(value != last) 

  {
  ScriptProperties.setProperty("last",value);
  MailApp.sendEmail("[e-mail]", "cell P2 changed",
    "new value: " + value + "\n\n" + ss.getUrl())
  }
}

I have trending charts and ultimately I'd like to email the actual trending chart is changed (a new monthly value is entered) but I thought I'd walk before I ran. :)

Answer 1

Cells that change because of a formula do not qualify as an 'edit' to trigger the onEdit function. You should either monitor the source cell(s) (if they are edited manually) or the second alternative is to have a function running under a trigger that runs every minute (or whatever frequency you like) and send out the email. The function you've written looks good to be run under a trigger.

Answer 2

Dave Lalande. Take a look at this code.

function getTimeEditRange(Range){
  return YourFunction(Range);
}

If the value is the same, then the event will not happen.

Best regards!