Google app script send email when sheet have no changes

0
votes

I'm trying to realize email notification at google spreadsheet, when sheet have no changes.

I'm tried to find smth opposite of onEdit() function. I have email script, it fired by the trigger once at day. It's working nice.

function sendEmailAlert2() {

var ss = SpreadsheetApp.openById("MyID");
var sheetname = ss.getSheetByName("Sheet1");
var Toemail = '[email protected]';
var subject = 'New Entry -' + ss.getName();
var body = 'Your file has a new entry in - ' + sheetname + ' Updated by - ' +
' check file- ' + ss.getUrl();
  MailApp.sendEmail(Toemail,subject, body);

}

I need condition to this script when my sheet was no edited on this day. Maybe someone have any idea?

2
google-apps-scriptgoogle-sheets-api

2 Answers

2
votes

You can achieve this by implementing a onEdit function which inserts a new time stamp into the sheet every time the file gets edited. And then, in your sendEmailAlert2() function, you can implement an if condition checking if the current day is equal to the day of last time stamp (i.e. the day of the last edit). If there was no edit today, the script email will be sent.

function onEdit(e) {
    SpreadsheetApp.getActive().getActiveSheet()
        .getRange('A1')
        .setValue(new Date());    
}

function sendEmailAlert2() {

var ss = SpreadsheetApp.openById("MyID");
var sheetname = ss.getSheetByName("Sheet1").getName();
var Toemail = '[email protected]';
var subject = 'New Entry -' + ss.getName();
var body = 'Your file has a new entry in - ' + sheetname + ' Updated by - ' +
' check file- ' + ss.getUrl();
var NoEditSubject = 'No new Entry in' + ss.getName();
var NoEditBody = 'Your file has no new entry in - ' + sheetname +  ss.getUrl();
if(sheetname.getRange('A1').getValue().getDay()==new Date().getDay())
 {
   MailApp.sendEmail(Toemail,subject, body);
 }
else
 {
  MailApp.sendEmail(Toemail,NoEditSubject, NoEditBody); 
 }  
}
1
votes

On my side I would recommend you to use the DriveApp service and check if date Last Update is higher than 24h.

function getAlertedIfNoChange(){
  var id = 'ID_SHEET';
  var file = DriveApp.getFolderById(id);

  var date = file.getLastUpdated();
  var lastUpdated = date.getTime();

  var today = (new Date()).getTime();
  var delta = (24 * 60 * 60 * 1000)- (10*60*1000)
  if((today - lastUpdated) > delta){
    //Fire alert
    Logger.log('No update during the last 24h')
  }
}

For the 'delta' you have to set it to 24h in milliseconds and by experience I recommend you to remove some minutes, because when you set a trigger to run each day it will not fire at the exact same time. By removing some minutes to the 24h you are sure it will fire an alert.

You program a trigger to run each day and it will be ok.