google-apps-script - 如果单元格更改，则发送电子邮件通知

Question

需要有关 Google 脚本的帮助。我有多行 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

我找到了有用的信息:

function emailNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var recipient = "me@gmail.com";
  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);
};

该脚本跟踪整个表中的更改。我只想跟踪 G 列中的更改，并从 D 列中获取值。

Question:

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

最后的脚本 - 回答我的问题

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 = "me@gmail.com";
  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);
};

在 onEdit 上设置触发器，脚本将正常工作

Answer 1

您应该在发布问题之前搜索此论坛；我确实搜索了电子邮件+手机并得到了一些结果:

例如，this post answer几乎完全符合您想要做的事情。

编辑后进行编辑:

使用 IF 条件。

类似这样的事情:

    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)