onChange触发器不按预期工作 [英] onChange Trigger not working as expected

问题描述

我有一个基于定时触发器的自动刷新IMPORTXML函数的Google表单，并试图编写一个脚本，当它用HELP消息类型检索行时，它将自动通过电子邮件发送通知警报。 / p>

我根据我找到的一些示例编译了该脚本。我已经测试过它，它可以正常使用onEdit可安装触发器手动输入数据，但是从我的测试（并基于研究）我需要使用onChange触发器让IMPORTXML触发脚本。但是，当我将脚本设置为onChange可安装触发器时，脚本似乎不会自动使用IMPORTXML加载数据执行，也不会自动执行表格。

我是否碰到Good Apps Script的某些限制？这是我第一次使用它（和JavaScript）

  function helpAlertEmail（e）
 {
 if （e.range.columnStart！== 5 || e.value！=='HELP'&& e.value！=='HELP-CANCEL'）return; 
 var ss = e.source.getActiveSheet（）
 var details = ss.getRange（e.range.rowStart，1,1.11）.getValues（）[0]; 
 var headers = ss.getRange（1，1，1，11）.getValues（）[0]; 
 var subject =SPOT BEACON ALERT：+ details [2] +发送一个+ details [4] +Message at+ details [9]; 
 var body =SPOT Beacon+ details [2] +（+ details [1] +）在+ details [9] +\上发送一个+ details [4] + n\\\
; 
 var email =[e-mail]; 
 var cols = [0，4，5，6，9，10]; （var i = 0; i< details.length; i ++）
 
 if（cols.indexOf（i）=== -1） 
 body + = headers [i] +：+ details [i] +\\\

} 
 
 body + =\\\
\\\
\\ \\ n请不要回复此电子邮件，因为它是由未经检查的帐户自动生成的。; 
 MailApp.sendEmail（email，subject，body，{noReply：true}）; 
} 
  

解决方案

关于用例的解释，我希望你知道这一点：

$ b <1> onEdit - 指定一个触发器，当电子表格是编辑。
$ b 2） onChange - 指定当电子表格的内容或结构发生更改时触发的触发器。

这些是Google规定的条件。

现在它没有告诉我们：

1）编辑必须是手动的

现在你说你也尝试过手动但触发器没有启动。可能的原因是您可能从某处复制了数据，然后将其粘贴到您想要的位置。所以，你不能指望在这种情况下触发器触发。

2）编辑不能是任何形式的自动化（它不能完成使用电子表格公式或使用任何脚本）。在这种情况下，触发器也不会触发。

因此，总而言之，您所面对的是不是任何限制的结果 strong>由Google声明。不幸的是，事情就是这样。

你需要做的是，想想另一种方式去实现你想要达到的目标，也就是使用某些东西除 onChange 和 onEdit 触发外，其他情况除外。

I have a google sheet that has an auto refreshing IMPORTXML function on it based on a timed trigger and am trying to write a script that will auto email a notification alert when it retrieves a row with a "HELP" message type.

I compiled this script based on some examples I found. I have tested it and it works fine with manually entered data with an onEdit installable trigger but from my testing (and based on research) I need to use the onChange trigger to have the IMPORTXML trigger the script.

However, when I set the script to a onChange installable trigger, the script doesn't seem to execute either automatically with the IMPORTXML loading data or by me manually entering it on the sheet.

Am I hitting some limitation of Good Apps Script? This is my first time using it (and JavaScript)

function helpAlertEmail(e) 
{
  if (e.range.columnStart !== 5 || e.value !== 'HELP' && e.value !== 'HELP-CANCEL') return;
  var ss = e.source.getActiveSheet()
  var details = ss.getRange(e.range.rowStart, 1, 1,11).getValues()[0];
  var headers = ss.getRange(1, 1, 1, 11).getValues()[0];
  var subject = "SPOT BEACON ALERT:  " + details[2] + " Sent a " + details[4] + " Message at " + details[9];
  var body = "SPOT Beacon " + details[2] + " (" + details[1] + ")  Sent a " + details[4] + " Message at " + details[9] +  "\n\n";
  var email = "[e-mail]";
  var cols = [0, 4, 5, 6, 9, 10];

  for (var i = 0; i < details.length; i++) 
  {
    if (cols.indexOf(i) === -1) continue;
    body += headers[i] + ": " + details[i] + "\n"
  }

  body += "\n\n\n Please do not respond to this email as it is automatically generated by an account that is not checked.";
  MailApp.sendEmail(email, subject, body, {noReply:true});
}

解决方案

By what I understand from your explanation about the use case, I would like you to know this:

1) onEdit - Specifies a trigger that will fire when the spreadsheet is edited.

2) onChange - Specifies a trigger that will fire when the spreadsheet's content or structure is changed.

These are the conditions stated by Google.

Now what it does not tell us is:

1) The edit has to be manual

Now you said you tried manually as well but the trigger did not fire. A possible reason for that is that you might have copied the data from somewhere and then pasted it where you want it. So, you cannot expect the trigger to fire in that case.

2) The edit cannot be any form of automation (it cannot be done using a spreadsheet formula or using any script). In this case as well, the trigger would not fire.

So, in all, what you are facing is not a consequence of any of the limitations stated by Google. Sadly, It is what it is.

What you will have to do is, think of another way to go about what you are trying to achieve, which is, use something else except the onChange and the onEdit triggers.

这篇关于onChange触发器不按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！