有什么方法可以自动创建可安装的触发器

有什么方法可以自动创建可安装的触发器

关注
发信
关注(28)粉丝(399)

有什么方法可以自动创建可安装的触发器?

本文介绍了有什么方法可以自动创建可安装的触发器?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的脚本当前从源模板工作表自动生成电子表格(项目列表)，并且到新工作表的超链接放置在主列表电子表格(作业列表)上.最终目标是使详细列表在单独的库存电子表格中编辑数量值.

My script currently auto-generates spreadsheets (itemization lists) from a source template sheet, and a hyperlink to the new sheet is placed on a master list spreadsheet (list of jobs). The end goal is for the itemization lists to edit quantity values on a separate inventory spreadsheet.

由于Google身份验证的限制，我最初遇到了使用简单触发器(onEdit(e))在脚本中打开其他电子表格的问题，但已通过将其更改为可安装的触发器().

I had originally encountered a problem with opening other spreadsheets in the script using a simple trigger (onEdit(e)) due to a Google authentication limitation, but this was fixed by changing it to an installable trigger (thanks to ross for the solution).

我的新问题是，虽然脚本将与模板的其余部分一起复制，但可安装的触发器却没有.因此，尽管模板电子表格具有正确的可安装触发器，但我需要修改模板电子表格，但自动生成的模板副本都没有该模板.

My new problem is that while a script will be copied along with the rest of the template, the installable triggers aren't. So, while the template spreadsheet has the correct installable trigger I need in order to modify the inventory spreadsheet, none of the auto-generated copies of the template have this.

我需要找到以下两者之一:-一种在生成新电子表格时自动创建可安装触发器的方法-一些替代方法，可让脚本修改其他电子表格，而不会遇到像简单触发器那样的身份验证问题

I need to find either:-A way to automatically create the installable triggers when a new spreadsheet is generated-Some alternative way that lets the script modify other spreadsheets without encountering authentication issues like a simple trigger would

建议和赞赏.谢谢！

为了澄清:onEdit(e)是由可安装触发器触发的函数的名称，并且所有文件/文件夹ID均已删除.代码:

To clarify: onEdit(e) is the name of a function triggered by an installable trigger, and all file/folder ID's have been removed.The code:

  function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = e.range;
  var newId;
  var newName;
  var hyperlinkString;

  //Check if edit occurred in relevant range
  if((range.getColumn() !== 1) && (range.getColumn() !== 2)) return;

  //Check if both columns were filled after edit
  if((range.getColumn() == 1) && (range.offset(0,1).isBlank() == true)) return;
  if((range.getColumn() == 2) && (range.offset(0,-1).isBlank() == true)) return;

  //Get new document name from concatenate formula in column H
  if(range.getColumn() == 1) newName = range.offset(0,7).getValue();
  if(range.getColumn() == 2) newName = range.offset(0,6).getValue();

  //Check whether the edits occurred on the jobs list or receptions list (indicated by '2' or '3' in L1)
  //Calls function to create new job sheet or reception sheet from template, gets ID of new spreadsheet
  if(((range.getColumn() == 1) && (range.offset(0,3).isBlank() == true)) || ((range.getColumn() == 2) && (range.offset(0,2).isBlank() == true))) {
    if(sheet.getRange('L1').getValue() == 2) newId = newJob();
    if(sheet.getRange('L1').getValue() == 3) newId = newReception();
  }
  else {
    if(range.getColumn() == 1) {
      hyperlinkString = range.offset(0,3).getFormula();
      newId = hyperlinkString.substring(77,121);
    }
    if(range.getColumn() == 2) {
      hyperlinkString = range.offset(0,2).getFormula();
      newId = hyperlinkString.substring(77,121);
    }
  }

  //Set the name of the new spreadsheet
  SpreadsheetApp.openById(newId).rename(newName);

  //Enter name and date information onto new spreadsheet
  if(range.getColumn() == 1) {
    SpreadsheetApp.openById(newId).getSheets()[0].getRange('B1').setValue(range.getValue());
    SpreadsheetApp.openById(newId).getSheets()[0].getRange('B2').setValue(range.offset(0,1).getValue());
  }
  if(range.getColumn() == 2) {
    SpreadsheetApp.openById(newId).getSheets()[0].getRange('B1').setValue(range.offset(0,-1).getValue());
    SpreadsheetApp.openById(newId).getSheets()[0].getRange('B2').setValue(range.getValue());
  }


  //Creates hyperlink to new spreadsheet
  if (range.getColumn() == 1) range.offset(0,3).setFormula("=HYPERLINK(\"" + SpreadsheetApp.openById(newId).getUrl() +"\",\"Click here for itemization\")");
  if (range.getColumn() == 2) range.offset(0,2).setFormula("=HYPERLINK(\"" + SpreadsheetApp.openById(newId).getUrl() +"\",\"Click here for itemization\")");

  //Sort list descending from most recent date
  sheet.getRange("A3:D1000").sort({column: 2, ascending: false});
}

function newJob() {
  //Open template
  var jobTemplateSS = SpreadsheetApp.openById("ID");

  //Create new spreadsheet from copy of template spreadsheet
  var newSS = jobTemplateSS.copy("Untitled Job");

  //Get folder
  var jobFolder = DriveApp.getFolderById("ID");

  //Get ID of new file
  var newSSFile = DriveApp.getFileById(newSS.getId());

  //Copy file to the correct directory and delete the instance created in root
  jobFolder.addFile(newSSFile);
  DriveApp.getRootFolder().removeFile(newSSFile);

  createInstallableTrigger('IncrementDecrement',newSS.getId());

  //Pass ID of new spreadsheet back to calling function
  return(newSS.getId());
}

function newReception() {
  //Open template
  var receptionTemplateSS = SpreadsheetApp.openById("ID");

  //Create new spreadsheet from copy of template spreadsheet
  var newSS = receptionTemplateSS.copy("Untitled Reception");

  //Get folder
  var receptionFolder = DriveApp.getFolderById("ID");

  //Get ID of new file
  var newSSFile = DriveApp.getFileById(newSS.getId());

  //Copy file to the correct directory and delete the instance created in root
  receptionFolder.addFile(newSSFile);
  DriveApp.getRootFolder().removeFile(newSSFile);

  createInstallableTrigger('IncrementDecrement',newSS.getId());

  //Pass ID of new spreadsheet back to calling function
  return(newSS.getId());
}

function createInstallableTrigger(funcName,ssId) {
  Logger.log("1");
  if(!isTrigger()) {
    ScriptApp.newTrigger(funcName).forSpreadsheet(ssId).onEdit().create();
  }
}

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

执行记录:

[19-06-07 16:32:41:011 PDT] Starting execution
[19-06-07 16:32:41:017 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[19-06-07 16:32:41:017 PDT] Spreadsheet.getActiveSheet() [0 seconds]
[19-06-07 16:32:41:018 PDT] SpreadsheetApp.getActiveRange() [0 seconds]
[19-06-07 16:32:41:018 PDT] Range.getRow() [0 seconds]
[19-06-07 16:32:41:018 PDT] Range.getLastRow() [0 seconds]
[19-06-07 16:32:41:018 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:018 PDT] Range.getLastColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:019 PDT] Range.offset([0, -1]) [0 seconds]
[19-06-07 16:32:41:149 PDT] Range.isBlank() [0.129 seconds]
[19-06-07 16:32:41:149 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:149 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:150 PDT] Range.offset([0, 6]) [0 seconds]
[19-06-07 16:32:41:150 PDT] Range.getValue() [0 seconds]
[19-06-07 16:32:41:150 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:150 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:41:151 PDT] Range.offset([0, 2]) [0 seconds]
[19-06-07 16:32:41:151 PDT] Range.isBlank() [0 seconds]
[19-06-07 16:32:41:153 PDT] Sheet.getRange([L1]) [0.001 seconds]
[19-06-07 16:32:41:269 PDT] Range.getValue() [0.116 seconds]
[19-06-07 16:32:41:367 PDT] SpreadsheetApp.openById([ID Removed]) [0.097 seconds]
[19-06-07 16:32:43:431 PDT] Spreadsheet.copy([Untitled Job]) [2.063 seconds]
[19-06-07 16:32:43:572 PDT] DriveApp.getFolderById([ID Removed]) [0.14 seconds]
[19-06-07 16:32:43:573 PDT] Spreadsheet.getId() [0 seconds]
[19-06-07 16:32:43:770 PDT] DriveApp.getFileById([ID Removed]) [0.197 seconds]
[19-06-07 16:32:43:771 PDT] File.getId() [0 seconds]
[19-06-07 16:32:44:529 PDT] Folder.addFile([Untitled Job]) [0.758 seconds]
[19-06-07 16:32:44:695 PDT] DriveApp.getRootFolder() [0.165 seconds]
[19-06-07 16:32:44:695 PDT] File.getId() [0 seconds]
[19-06-07 16:32:45:186 PDT] Folder.removeFile([Untitled Job]) [0.49 seconds]
[19-06-07 16:32:45:186 PDT] Spreadsheet.getId() [0 seconds]
[19-06-07 16:32:45:187 PDT] Logger.log([1, []]) [0 seconds]
[19-06-07 16:32:45:188 PDT] Logger.log([2, []]) [0 seconds]
[19-06-07 16:32:45:189 PDT] ScriptApp.newTrigger([IncrementDecrement]) [0 seconds]
[19-06-07 16:32:45:190 PDT] TriggerBuilder.forSpreadsheet([This is the ID of the new sheet- the correct ID]) [0 seconds]
[19-06-07 16:32:45:190 PDT] SpreadsheetTriggerBuilder.onEdit() [0 seconds]
[19-06-07 16:32:45:709 PDT] SpreadsheetTriggerBuilder.create() [0.518 seconds]
[19-06-07 16:32:45:709 PDT] Spreadsheet.getId() [0 seconds]
[19-06-07 16:32:45:710 PDT] Sheet.getRange([L1]) [0 seconds]
[19-06-07 16:32:45:710 PDT] Range.getValue() [0 seconds]
[19-06-07 16:32:45:794 PDT] SpreadsheetApp.openById([ID Removed]) [0.083 seconds]
[19-06-07 16:32:46:079 PDT] Spreadsheet.rename([Name Removed]) [0.284 seconds]
[19-06-07 16:32:46:079 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:46:079 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:46:080 PDT] SpreadsheetApp.openById([ID Removed]) [0 seconds]
[19-06-07 16:32:46:080 PDT] Spreadsheet.getSheets() [0 seconds]
[19-06-07 16:32:46:081 PDT] Sheet.getRange([B1]) [0 seconds]
[19-06-07 16:32:46:081 PDT] Range.offset([0, -1]) [0 seconds]
[19-06-07 16:32:46:082 PDT] Range.getValue() [0 seconds]
[19-06-07 16:32:46:082 PDT] Range.setValue([Name Removed]) [0 seconds]
[19-06-07 16:32:46:083 PDT] SpreadsheetApp.openById([ID Removed]) [0 seconds]
[19-06-07 16:32:46:083 PDT] Spreadsheet.getSheets() [0 seconds]
[19-06-07 16:32:46:084 PDT] Sheet.getRange([B2]) [0 seconds]
[19-06-07 16:32:46:084 PDT] Range.getValue() [0 seconds]
[19-06-07 16:32:46:084 PDT] Range.setValue([2/24/19]) [0 seconds]
[19-06-07 16:32:46:084 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:46:085 PDT] Range.getColumn() [0 seconds]
[19-06-07 16:32:46:085 PDT] Range.offset([0, 2]) [0 seconds]
[19-06-07 16:32:46:086 PDT] SpreadsheetApp.openById([ID Removed]) [0 seconds]
[19-06-07 16:32:46:086 PDT] Spreadsheet.getUrl() [0 seconds]
[19-06-07 16:32:46:168 PDT] Range.setFormula([=HYPERLINK("Link removed","Click here for itemization")]) [0.081 seconds]
[19-06-07 16:32:46:169 PDT] Sheet.getRange([A3:D1000]) [0 seconds]
[19-06-07 16:32:46:170 PDT] Range.sort([{column=2.0, ascending=false}]) [0 seconds]
[19-06-07 16:32:46:488 PDT] Execution succeeded [5.155 seconds total runtime]

推荐答案

您可以执行以下操作:

function createInstallableTrigger(funcname) {
  if(!isTrigger(funcname)) {
    ScriptApp.newTrigger(funcname).forSpreadsheet('SpreadsheetId').onEdit().create();
  }
}

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

isTrigger函数只会阻止您为同一函数安装多个触发器.

The isTrigger function just keeps you from installing more than one trigger for the same function.

这篇关于有什么方法可以自动创建可安装的触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-24 01:08
Powered by LMLPHP ©2024 有什么方法可以自动创建可安装的触发器 0.037615