How to trigger an email notification when cell value is modified by function

Question

I would like to create a Google Sheets with event triggers. I'm using Google Apps Script.

I succeeded, thanks to Stack Overflow, to create a Google Sheets with an automatic mail notification when a cell is modified by a user.

Now I would like to know if this is possible when cell is modified by a function (not user's modification), such as :

if (today() >= B3 ; "late" ; "not late")

The function checks date, and give result "late" or "not late".

When deadlines are reached, the function would return "late" and a mail would be sent to warn me. The body mail would have the value of the cell in the B, D and E column and in the same row of the cell modified (I know how to do this using e.source, getRange and getRow)

So far, i've tried this, but it's not working

function sendNotification(e) {

if("F" == e.range.getA1Notation().charAt(0)) {

if(e.value == "Late") {

//Define Notification Details
var recipients = "[email protected]";
var subject = "Deadlines" ;
var body = "deadline reached";

//Send the Email
MailApp.sendEmail(recipients, subject, body);
}
}
}

How can I set up mail notifications when cells in F column have the "late" value (with "late" being the result of a function) ?

My answer below won't let you use e.range because it is not triggered by a sheet event but nevertheless you have the row number so you can easily get any value in that row with their column number. Il semble que vous soyez francophone, bienvenue sur stackoverflow ;-) — Serge insas
Yes I'm French :) Very glad to join Stackoverflow community Thx so much for the scritp ! it works very fine for me — Lionelus

Serge insas Serge insas · Accepted Answer · 2016-02-09T18:48:50

You can use a simple script that runs on a timer trigger and checks for any modification in a specific column in your sheet.

I use script like that for a lot of tasks, including calendar and sheets monitoring.

Below is a test code that works on column F, you have to run it once manually to create the scriptProperties value that I use to detect changes.

Then create a time trigger to run it every hour or any other timer value you find useful.

The only issue would be if you have a very long sheet, you could reach the length limit of the properties... (right now I don't remember the max length, will have to check ;-)

Code :

function checkColumnF() {
  var sh = SpreadsheetApp.getActiveSheet();
  var values = sh.getRange('F1:F').getValues().join('-');
  if(PropertiesService.getScriptProperties().getKeys().length==0){ // first time you run the script
    PropertiesService.getScriptProperties().setProperty('oldValues', values);
    return;
  }
  var oldValues = PropertiesService.getScriptProperties().getProperty('oldValues').split('-');
  var valuesArray = values.split('-');
  while (valuesArray.length>oldValues.length){
    oldValues.push('x'); // if you append some rows since last exec
  }
  Logger.log('oldValues = '+oldValues)
  Logger.log('current values = '+valuesArray)
  for(var n=0;n<valuesArray.length;n++){
    if(oldValues[n] != valuesArray[n]){ // check for any difference
      sendMail(n+1,valuesArray[n]);
    }
  }  
  PropertiesService.getScriptProperties().setProperty('oldValues', values);
}

function sendMail(row,val){
  Logger.log('value changed on row '+row+' value = '+val+' ,  mail sent');
  // uncomment below when you are sure everything runs fine to avoid sending dozens of emails while you test !
  //MailApp.sendEmail(Session.getActiveUser().getEmail(),'value changed in your sheet','Row '+row+' is now '+val);
}

How to trigger an email notification when cell value is modified by function

1 Answers

Code :