how to attach onChange cell value event/script to google sheet

0
votes

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?

1
google-apps-scriptgoogle-sheetstriggers

1 Answers

6
votes

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()!='[email protected]' &&
       Number(ss.getActiveCell().getValue())>1000 && cell=='A1'){
        MailApp.sendEmail('[email protected]', '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.