How To Send A Notification Based On Changed Cell On Google Spreadsheet

I know it is similar to email notification if cell is changed but I would like to do the following: In column A I have the reminder Name, column B I have the Telephone, and column

Solution 1:

your script was missing an important feature : you should remember if a mail has already been sent before sending it otherwise each time you read the cell value a mail will be sent.

There are many ways to achieve that, adding a column with a "mail sent" flag is probably the most common but definitely not the one I prefer ...

Here is a version that colorizes the cell when a mail is sent, I choose red but you can change to a more discrete color of course. (I added a logger so you can see the colors hex code easily)

function checkReminder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  var sheet = spreadsheet.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var startRow = 2;
  var range = sheet.getRange(2,5,lastRow-startRow+1,1 );
  var numRows = range.getNumRows();
  var sV = range.getValues();
  var bGColors = range.getBackgroundColors();// get range background colors
  var range_reminder = sheet.getRange(2, 1, lastRow-startRow+1, 1);
  var reminder_info_values = range_reminder.getValues();

  var warning_count = 0;
  var msg = "";


  for (var i = 0; i <= numRows - 1; i++) {
    var statusC = sV[i][0];
    Logger.log(bGColors[i][0]);
    if(statusC > 3 && bGColors[i][0] != '#ff0000') { // if not already sent
      var reminder_name = reminder_info_values[i][0];
      bGColors[i][0] = '#ff0000';
      msg = msg + "The reminder: "+reminder_name+" changed status to "+statusC +" ";
      warning_count++;
    }
  }
  range.setBackgroundColors(bGColors);// update sheet with colors
  if(warning_count>0) {
    MailApp.sendEmail("email@email.com",
                      "CC Changed status of "+ reminder_name, msg);
  }

};

You can set a trigger calling this script either onEdit or on a timer, both method will work, it's more a matter of choice (unless you absolutely need to have an instantaneous reaction).

Solution 2:

You can use the onEdit()

If the value is in Column F and it now is 4 or 5 then send the email.

https://developers.google.com/apps-script/understanding_events