Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

google apps script - email notification if cell is changed

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...