通过脚本安装触发器会不断向模板表中添加触发器 [英] installing a trigger by script keeps adding triggers to template sheet

问题描述

我一直在研究一个模板问题.我在共享驱动器上的文件夹中有一个模板文件，该文件具有一个包含创建功能和关闭功能的脚本，此脚本由下拉菜单中的编辑安装触发.

I have ran into a problem with a template I have been working on. I have a template file in a folder on a shared drive that has a script containing a create function and close function, triggered by this installed on edit ran from a drop down.

function onMyEdit(e) {

  if(sh.getName()=="Sheet2" && e.range.columnStart==6 && e.range.rowStart==3 && e.value) {
  switch(e.value) {
    case 'Create':
      Create();
      break;
    case 'Close':
      Close();
}
e.range.setValue('');
}

}

create函数在共享驱动器上的文件夹中创建了模板的重命名副本，添加了日历事件，并将某些数据写入驱动器中另一个充当数据库的电子表格.我在模板文件中为onMyEdit函数手动安装了一个触发器，但是当我创建副本时，该触发器不起作用，因此关闭函数将不会从新创建的图纸上运行.我尝试对create函数进行编程，以在新工作表上安装触发器.使用这个

The create function created a renamed copy of the template in a folder on the shared drive, adds a calendar event, and writes certain data to another spreadsheet in the drive acting as a data base. I manually installed a trigger for the onMyEdit function in the template file, but when I create the copy the trigger does not work so the close function will not run from the newly created sheet. I tried programming the create function to install a trigger on the new sheet. Useing this

var WOssID = WOss.getId(); //get the ID of the newly created work order spreadsheet
Logger.log(WOssID)

 ScriptApp.newTrigger('onMyEdit')
    .forSpreadsheet(WOssID)
    .onEdit()
    .create()

其中WOss是我刚刚通过复制模板创建的电子表格，我检查了记录器，并且ID确实与新工作表匹配.这样做确实可以解决，所以我可以从新创建的工作表中运行关闭功能，但是似乎向模板工作表中添加了触发器，而不是向新工作表中添加了触发器，并且每次我从模板文件中运行create函数时，它都会不断添加触发器.直到它限制在20，然后一切都破裂了.我一定会误会一些东西，因为当我有新创建的工作表的ID被调用时，我不明白为什么它会向模板工作表添加触发器.也希望所有这些都可以在Ipad上运行，所以请相信这意味着我不能使用菜单上添加的内容，onopen调用或可单击的按钮.感谢您的帮助.

where WOss is the spreadsheet that I just created by copying the template, and I check the logger and the ID does match the new sheet. This does fix it so I can run the close function from the newly created sheet, but it seems to add a trigger to the template sheet not the new sheet, and it keeps adding a trigger every time I run the create function from the template file until it limits out at 20 and then it all breaks. I must be misunderstanding something because I don't understand why it is adding triggers to the template sheet when I have the ID for the newly created sheet being called. Also hoping to get this all to run on an Ipad so believe that means I can't use added on menus, onopen calls, or clickable buttons. Any help is appreciated.

更新

因此，我现在有了一个简单的onEdit触发器，该触发器在第一次编辑时会在我的工作单上创建一个已安装的onMyEdit触发器，并且不会在我的模板文件中创建多个触发器.但是，我需要找到一种在新创建的工作表中对其进行授权的方法，而无需进入脚本编辑器.如果我打开新创建的工作表并进行编辑，则新安装的触发器将显示在项目触发器中，但是直到我首先尝试从脚本编辑器运行该脚本时，它才会要求授权才能运行.有没有一种方法可以将授权与复选框相关联，当单击该复选框时，弹出窗口会显示出对已安装触发器的授权，以便它可以运行?

So I now have a simple onEdit trigger that creates a installed onMyEdit trigger to my work order sheet on first edit and doesn't create multiple triggers in my template file. However I need to figure out a way to authorize it in the newly created sheet, without going into the script editor. If I open the newly created sheet and make an edit the new installed trigger shows up in the project triggers, but it won't run until I try to run it from the script editor first at which time it asks for authorization. Is there a way to tie the authorization to a checkbox that when clicked makes the popup appear to authorize the installed trigger so it can run?

 function onEdit(){
    var WOss = SpreadsheetApp.getActiveSpreadsheet(); 
    var WOssID = WOss.getId();
    var allTriggers = 
  ScriptApp.getUserTriggers(WOss)//ScriptApp.getProjectTriggers();
  Logger.log(allTriggers.length)

  if(allTriggers.length == '0' ){

 ScriptApp.newTrigger('onMyEdit').forSpreadsheet(WOssID).onEdit().create()

 }

 else(allTriggers.length != '0') 

   {}

 }

推荐答案

以下是我用来创建触发器的函数:

Here's a function I use to create triggers:

function createOnFormSubmitTriggerForSpreadsheet() {
  const ssid=SpreadsheetApp.getActive().getId();
  const resp=SpreadsheetApp.getUi().prompt("Create On Form Submit Trigger", "Enter Function Name", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if(resp.getSelectedButton()==SpreadsheetApp.getUi().Button.OK) {
    let funcname=resp.getResponseText();

下一行阻止我为同一功能创建多个触发器

The next line prevents me from creating more than one trigger for the same function

    if(!isTrigger(funcname)) {//I use this to keep from creating more than one trigger

我建议您也这样做

      ScriptApp.newTrigger(funcname).forSpreadsheet(ssid).onFormSubmit().create();   
    }
  }
}

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for (let i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

这并不意味着残酷，但是您的代码是一团糟，您不需要描述整个项目，我们只想看看产生问题的最少代码.您说您不知道什么重要和什么不重要.好吧，当您花时间创建最小的可重现示例时，您将学到很多有关您的代码的知识，而且我经常发现我最终只能自己解决问题.

And don't mean to be cruel but you code is a mess and you dont' need to describe your whole project we just want to see the minimum code that generates the problem. You said that you didn't know what's important and what's not. Well when you spend the time to create a minimum reproducible example you will have learned a lot more about your code and I often find that I end up solving the problem on my own.

这篇关于通过脚本安装触发器会不断向模板表中添加触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！