CODEWITHSUNDEEP
google-apps-script
Dave Lalande
Dave Lalande

Reputation: 194

Trigger email if cell changed in Google Sheet

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("[email protected]", "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. :)

Upvotes: 1

Views: 1815

Answers (2)

oshliaer
oshliaer

Reputation: 4979

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!

Upvotes: 0

Srik
Srik

Reputation: 7965

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.

Upvotes: 1

Related Questions