CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
The Ravals
The Ravals

Reputation: 111

Google App Script to trigger on cell value change

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

Upvotes: 10

Views: 21561

Answers (3)

The Ravals
The Ravals

Reputation: 111

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!

Upvotes: 0

Rafa Guillermo
Rafa Guillermo

Reputation: 15357

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:

Upvotes: 14

kztd
kztd

Reputation: 3415

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

Upvotes: 2

Related Questions