Google脚本 - OnEdit问题 [英] Google Script - OnEdit problems

问题描述

我一直在使用 OnEdit 并且它的工作很好 - 但是现在我发现了一个问题 - 基本上当列6（f）被编辑时希望它检查值 - 如果它是进行中我想它然后运行一个函数发送一封电子邮件，当我调试它 - 它的工作原理正如我想要的那样，但是当我没有调试它似乎从来没有
$ b

  var summary = ss.getSheetByName（Summary）
 var data = summary.getRange（4 ，1，summary.getLastRow（），summary.getLastColumn（））; 
 var learnerObjects = getRowsData（summary，data）; 
 var lastrow = summary.getLastRow（）; 
 
 function onEdit（e）{
 var sheet = e.source.getActiveSheet（）; 
 var CellRow = sheet.getActiveRange（）。getRow（）; 
 var CellColumn = sheet.getActiveRange（）。getColumn（）; 
 
 if（sheet.getSheetName（）==Summary）{
 if（CellColumn == 6）{
 var learner = learnerObjects [CellRow-4]; 
 var status = learner.status; 
 if（status ==In Progress）{
 var enrolmentdate = learner.enrolmentDateDdmmyyyy; 
 var surname = learner.surname; 
 var firstname = learner.firstName; 
 var qualification = learner.qualification; 
 var company = learner.company; 
 messagePT3 = firstname ++ surname +from +company +doing+ qualification +已添加到您的跟踪器中。; 
 sendTrackerEmail（messagePT3）; 
 SpreadsheetApp.getActiveSpreadsheet（）。toast（'电子邮件已发送给评估者关于新学习者'，'评估者通知'，3）; 
} 
} 
} 
} 
  

是它的主要代码，当调试，因为我说它工作正常，但是当我没有调试它似乎从来没有被触发，是否有任何其他方式来做到这一点？

我在列F上有数据验证，但是这会将这些单元限制为已完成，已撤消，已暂停或正在进行 - 这应该会有效果吗？

我知道 onEdit 正在被触发，因为我使用它来监控电子表格某些部分的更改。

<解决方案 onEdit（）触发器是一个所谓的简单触发器，它具有有限的操作域。
由于它在活动用户的权限下自动运行，所以无法执行任何需要授权的操作，例如无法发送电子邮件！

解决方法很简单：将你的函数名称改为其他名称（例如myOnEdit），并设置一个可安装的onEdit触发器来运行该函数（菜单ressource /当前脚本触发器/在脚本编辑器中创建新触发器）。

这个时候，当任何人编辑电子表格时，它都会以您的身份执行（您将发送电子邮件）。

文档关于该主题的此处提供。

I've been using OnEdit for some time and its been working great - however just now ive came accross a problem - basically when column 6 (f) is edited I want it to check the value - if it is "In Progress" I want it to then run a function to send an email, when I debug this - it works great exactly as I want it to, however when im not debugging it never seems to run.

var summary = ss.getSheetByName("Summary")
var data = summary.getRange(4, 1, summary.getLastRow(), summary.getLastColumn());
var learnerObjects = getRowsData(summary, data);
var lastrow = summary.getLastRow();

function onEdit(e){
  var sheet = e.source.getActiveSheet();
  var CellRow = sheet.getActiveRange().getRow();
  var CellColumn = sheet.getActiveRange().getColumn();

  if (sheet.getSheetName() == "Summary"){
    if (CellColumn == 6){
      var learner = learnerObjects[CellRow-4];
      var status = learner.status;
      if (status == "In Progress"){
        var enrolmentdate = learner.enrolmentDateDdmmyyyy;
        var surname = learner.surname;
        var firstname = learner.firstName;
        var qualification = learner.qualification;
        var company = learner.company;
        messagePT3 = firstname + " " + surname + " from " + company + " doing " + qualification + " has been added to your tracker.";
        sendTrackerEmail(messagePT3);
        SpreadsheetApp.getActiveSpreadsheet().toast('Email has been sent to Assessor regarding new learner', 'Assessor Notified', 3);
      }
    }
  }
}

This is the main code for it, when debugging as I say it works fine, however when im not debugging it never seems to be triggered, is there any other ways for me to do this?

I have data validation on column F however this limits the cells to "Completed", "Withdrawn", "Suspended" or "In Progress" - this shouldnt have an effect should it?

I know that onEdit is being triggered as I use it for monitoring changes to certain parts of the spreadsheet.

解决方案

The onEdit() trigger is a so called "simple trigger" that has a limited field of action. Since it runs automatically and silently under the authority of the active user it can't do anything that requires authorization, for example it can't send emails !

The solution is simple : change your function name to something else (myOnEdit for example) and set an installable onEdit trigger to run that function.(menu ressource/current script trigger/create new trigger in the script editor.)

This time it will be executed as you (you'll be sending the emails) when anyone edits the spreadsheet.

Documentation on that subject is available here.

这篇关于Google脚本 - OnEdit问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！