Google App Script to trigger on cell value change

2
votes

I am a newbie and have been using a simple App Script to send out emails with triggers onEdit and onChange. However, my Worksheet has over ten sheets and any edits/changes (done by me or by computations) in any of the sheets sends out an email, causing unintended spam! To avoid this, if I could use some code that sends the email based only on ANY CHANGE to a specific cell's value, in a specific sheet, my problem would be solved. My outgoing email message is short and the whole message is in just ONE cell (C2). If I can add a line of code which monitors for ANY change in that cell C2, and sends out an email if there is a change, that's it! I'd be done. My Script is as follows:

function sendEmail(){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Email');
var emailAddress = sheet1.getRange(2,1).getValue();
var subject = sheet1.getRange(2,2).getValue();
var message = sheet1.getRange(2,3).getValue();
MailApp.sendEmail(emailAddress, subject, message);
}
2
google-apps-scriptgoogle-sheetstriggers
Are these thread useful for your situation? stackoverflow.com/q/56895237 stackoverflow.com/q/27107903 stackoverflow.com/q/46499503 - Tanaike
The only changes that onEdit trigger responds to are user edits and yes you should put logic in your onEdit function to limit it's actions to a given sheet. Take a close look at the event object it gives you a lot of information about the trigger event. Try use Logger.log(JSON.stringify(e)); There's more info there than they discuss in the documentation. - Cooper
Please check this one How to send email notifications on Google Spreadsheets if any cell value changes - Rocky

2 Answers

3
votes

Answer:

You can do this with an onEdit() and a conditional.

Code Example:

function onEdit(e) {
  const specificSheet = "Email"   // for example
  const specificCell = "C2"       // for example

  let sheetCheck = (e.range.getSheet().getName() == specificSheet)
  let cellCheck = (e.range.getA1Notation() == specificCell)

  if (!(sheetCheck && cellCheck)) {
    return
  }
  else {
    sendEmail()
  }
}

Rundown of this function:

  • Defines the sheet and A1 notation of the specific cell to check
  • Gets the Sheet and the A1 notation of the cell which was just edited
  • Returns if either the Sheet or the Cell are not the defined specific cell (using De Morgan's law)
  • Runs sendEmail() if the cell and Sheet are correct

I hope this is helpful to you!

References:

0
votes

On further research, the following solution seems to work the best:

function sendEmail(){
Utilities.sleep(30000);
var ss=SpreadsheetApp.getActiveSpreadsheet();
var data=ss.getActiveSheet().getActiveCell().getA1Notation();
var sheetname = ss.getActiveSheet().getName();
var sheet1=ss.getSheetByName('Email');
var emailAddress = sheet1.getRange(2,1).getValue();
var subject = sheet1.getRange(2,2).getValue();
var message = sheet1.getRange(2,3).getValue();
if(data.indexOf('A:C')!=-1.23456789) {
MailApp.sendEmail(emailAddress, subject, message);
}

};

The key seems to be the "if statement" on line 10. Please note the time delay of half a minute I added to the script. This is because without it, on the trigger activating, the previous email was going out instead of the current one. Obviously my app has a slight delay in syncing and the trigger fired before all the current data got populated in the relevant cell! Thanks for your help, everyone! Happy holidays!