当单元格值被功能修改时，如何触发电子邮件通知 [英] How to trigger an email notification when cell value is modified by function

问题描述

我想使用事件触发器创建Google表格。我正在使用Google Apps脚本。

I would like to create a Google Sheets with event triggers. I'm using Google Apps Script.

由于Stack Overflow，我成功地创建了一个自动邮件通知的Google表格，当单元格被修改为时一个用户。

I succeeded, thanks to Stack Overflow, to create a Google Sheets with an automatic mail notification when a cell is modified by a user.

现在我想知道这是否可以通过功能修改单元格（而不是用户的修改），例如：

Now I would like to know if this is possible when cell is modified by a function (not user's modification), such as :

if (today() >= B3 ; "late" ; "not late")

功能检查日期，并给结果晚或不晚。

The function checks date, and give result "late" or "not late".

当达到最后期限时，该函数将返回邮件将被发送警告我。身体邮件将具有B，D和E列中单元格的值以及修改的单元格的同一行（我知道如何使用e.source， getRange 和 getRow ）

When deadlines are reached, the function would return "late" and a mail would be sent to warn me. The body mail would have the value of the cell in the B, D and E column and in the same row of the cell modified (I know how to do this using e.source, getRange and getRow)

到目前为止，我已经尝试过这个，但它不工作

So far, i've tried this, but it's not working

function sendNotification(e) {

if("F" == e.range.getA1Notation().charAt(0)) {

if(e.value == "Late") {

//Define Notification Details
var recipients = "[e-mail]";
var subject = "Deadlines" ;
var body = "deadline reached";

//Send the Email
MailApp.sendEmail(recipients, subject, body);
}
}
}

如何设置邮件F列中的单元格具有晚值（晚是函数的结果）的通知）？

How can I set up mail notifications when cells in F column have the "late" value (with "late" being the result of a function) ?

推荐答案

您可以使用在定时器触发器上运行的简单脚本，并检查工作表中特定列中的任何修改。

You can use a simple script that runs on a timer trigger and checks for any modification in a specific column in your sheet.

我使用这样的脚本进行许多任务，包括日历和工作表监控。

I use script like that for a lot of tasks, including calendar and sheets monitoring.

下面是一个测试代码适用于F列，您必须手动运行它以创建用于检测更改的scriptProperties值。

Below is a test code that works on column F, you have to run it once manually to create the scriptProperties value that I use to detect changes.

然后创建一个时间触发器来运行它每个小时或任何其他计时器值你觉得有用。

Then create a time trigger to run it every hour or any other timer value you find useful.

唯一的问题是如果你有一个很长的工作表，你可以达到属性的长度限制.. （现在我不记得最大长度，必须检查; - ）

The only issue would be if you have a very long sheet, you could reach the length limit of the properties... (right now I don't remember the max length, will have to check ;-)

function checkColumnF() {
  var sh = SpreadsheetApp.getActiveSheet();
  var values = sh.getRange('F1:F').getValues().join('-');
  if(PropertiesService.getScriptProperties().getKeys().length==0){ // first time you run the script
    PropertiesService.getScriptProperties().setProperty('oldValues', values);
    return;
  }
  var oldValues = PropertiesService.getScriptProperties().getProperty('oldValues').split('-');
  var valuesArray = values.split('-');
  while (valuesArray.length>oldValues.length){
    oldValues.push('x'); // if you append some rows since last exec
  }
  Logger.log('oldValues = '+oldValues)
  Logger.log('current values = '+valuesArray)
  for(var n=0;n<valuesArray.length;n++){
    if(oldValues[n] != valuesArray[n]){ // check for any difference
      sendMail(n+1,valuesArray[n]);
    }
  }  
  PropertiesService.getScriptProperties().setProperty('oldValues', values);
}

function sendMail(row,val){
  Logger.log('value changed on row '+row+' value = '+val+' ,  mail sent');
  // uncomment below when you are sure everything runs fine to avoid sending dozens of emails while you test !
  //MailApp.sendEmail(Session.getActiveUser().getEmail(),'value changed in your sheet','Row '+row+' is now '+val);
}

这篇关于当单元格值被功能修改时，如何触发电子邮件通知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！