1

How to attach an onChange method to a cell in google spreadsheet. I want to send an email to someone as soon a value in a cell becomes greater that 1000?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
coure2011
  • 40,286
  • 83
  • 216
  • 349

1 Answers1

6

In this example the mail is sent if cell A1 has a value >1000 and if you didn't change its value yourself.

You should of course customize the email address and the cell name to your needs.

function sheetTracker(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var cell = ss.getActiveCell().getA1Notation();

    if(Session.getActiveUser()!='yourmail@gmail.com' &&
       Number(ss.getActiveCell().getValue())>1000 && cell=='A1'){
        MailApp.sendEmail('yourmail@gmail.com', 'Modification in the spreadsheet', 
       'Cell ' + cell + ' has been modified by '+ Session.getActiveUser() +
       ' - new value = ' + ss.getActiveCell().getValue().toString());
    } 
}

You should add an installable trigger on edit (go to resource>current sheet triggers>add new trigger) and authorize the script.

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • 1
    Note that `var val = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue().toString();` is how to get the resulting value from a changed cell in an installable trigger. In a simple trigger the triggered `onChange(e)` provides the value `var val = e.value;` . – Matthew Aug 15 '17 at 19:02