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() {
  varspreadsheet= SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  varsheet= spreadsheet.getActiveSheet();
  varlastRow= sheet.getLastRow();
  varstartRow=2;
  varrange= sheet.getRange(2,5,lastRow-startRow+1,1 );
  varnumRows= range.getNumRows();
  varsV= range.getValues();
  varbGColors= range.getBackgroundColors();// get range background colorsvarrange_reminder= sheet.getRange(2, 1, lastRow-startRow+1, 1);
  varreminder_info_values= range_reminder.getValues();

  varwarning_count=0;
  varmsg="";


  for (vari=0; i <= numRows - 1; i++) {
    varstatusC= sV[i][0];
    Logger.log(bGColors[i][0]);
    if(statusC > 3 && bGColors[i][0] != '#ff0000') { // if not already sentvarreminder_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 colorsif(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