email notification if cell is changed

Question

need help with google script. I have multiple row spreadsheet.

Need a script that does the following:

If any cell in column G has been changed, then send email notification to custom address with information from this row: information from cell D and new value of cell G.

UPD

I found useful information:

function emailNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var recipient = "[email protected]";
  var subject = 'Update to '+sheet.getName();
  var body = sheet.getName() + ' has been updated. Visit ' + ss.getUrl() + ' to view the changes on cell: «' + cell + '» New cell value: «' + cellvalue + '»';
  MailApp.sendEmail(recipient, subject, body);
};

This script tracks the changes in the entire table. And I would like track changes only in column G, and get values ​​from column D.

Question:

How to get the value of the cell in column D when the value has changed cell in column G

Finally script — answer to my question

spreadsheet

function sendNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var recipients = "[email protected]";
  var message = '';
  if(cell.indexOf('G')!=-1){ 
    message = sheet.getRange('D'+ sheet.getActiveCell().getRowIndex()).getValue()
  }
  var subject = 'Update to '+sheet.getName();
  var body = sheet.getName() + ' has been updated. Visit ' + ss.getUrl() + ' to view the changes on row: «' + row + '». New comment: «' + cellvalue + '». For message: «' + message + '»';
  MailApp.sendEmail(recipients, subject, body);
};

Set trigger on onEdit and script will work fine

Answer 1

You should search this forum before posting questions; I did search on email+cell and got a few results :

For example, this post answer does almost exactly what you want to do.

EDIT following your edit :

use an IF condition.

Something like this :

    var cellG = ''
    if(cell.indexOf('D')!=-1){ // = if you edit data in col D
cellG = sheet.getRange('G'+ sheet.getActiveCell().getRowIndex()).getValue()
// add more condition if necessary and/or send your mail (cellG contains the value in column G of the active row
}
    Logger.log(cellG)

Answer 2

The correct way to track a cell-change event is to use the event object "e" with onEdit trigger. The event object contains the range that was edited and hence you can always get the content of the cell that was changed.

function sendNotification(e){
  var range = e.range;
  range.setNote('Last modified: ' + new Date());
}

NOTE: The function name shouldn't be onEdit which is a special name in apps script. The onEdit function won't allow to send email because of LIMITED authMode.

Here is an apps script which allows to send an email if a cell is edited in a specific range. It also allows to send the entire row, entire column or a custom range to be sent in the notification email.