Google App Script to trigger on cell value change

Question

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

Are these thread useful for your situation? https://stackoverflow.com/q/56895237 https://stackoverflow.com/q/27107903 https://stackoverflow.com/q/46499503 — Tanaike, Dec 17 '20 at 03:00
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, Dec 17 '20 at 03:00
Please check this one [How to send email notifications on Google Spreadsheets if any cell value changes](https://talentnett.com/how-to-send-email-notifications-on-google-spreadsheets-if-any-cell-value-changes/) — Rocky, Dec 17 '20 at 09:37

score 9 · Accepted Answer · edited Nov 04 '21 at 09:41

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

References:

Hi Rafa, Many thanks for the solution! I have added it to two of my scripts, just before my scripts and am testing them now. I'll keep you posted. However, please note that in both cases the "edits" are computed and not user entered and so both my original script triggers were onChange (since onEdit did not work) and your script starts with onEdit. Would I need to change my Triggers to onEdit, or should I change your script to onChange? Thanks so much again! — The Ravals, Dec 18 '20 at 02:18

score 1 · Answer 2 · answered Oct 31 '21 at 19:02

Working from the answer, this is how it ended up for me since I had several ranges to check:

function onEdit(e){
  if(wasEdited(e, range1)){  // e.g. range1 = "Sheet1!A5"
    // handle range1 change
  }
  if(wasEdited(e, range2)){  // e.g. range2 = "Sheet1!A7"
    // handle range2 change
  }
}
function wasEdited(e, range){
  let tab = getTabFromA1Range(range)
  let cell = getRangeFromA1Range(range)
  return e.range.getSheet().getName() == tab && e.range.getA1Notation() == cell
}
function getTabFromA1Range(a1Range){
  return a1Range.substring(0, a1Range.indexOf("!"))
}
function getRangeFromA1Range(a1Range){
  return a1Range.substring(a1Range.indexOf("!")+1)
}

score 0 · Answer 3 · edited Nov 04 '21 at 09:41

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!

Please format your code properly. – isherwood Feb 04 '22 at 17:15 — isherwood, Feb 04 '22 at 17:15

Google App Script to trigger on cell value change

3 Answers3

Answer:

Code Example:

References: