How to trigger an email notification when cell val

问题:

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 = "[e-mail]";
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) ?

回答1:

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);
}