错误的电子表格中生成可安装的触发器 [英] Installable Triggers Being Generated in Wrong Spreadsheet
问题描述
我的脚本当前从源模板工作表自动生成电子表格(项目列表)，并且到新工作表的超链接放置在主列表电子表格(作业列表)上.最终目标是使详细列表在单独的库存电子表格中编辑数量值.
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).
我想出了如何自动创建新的可安装触发器感谢Cooper .我在主电子表格脚本中添加了一个函数，该脚本会在ID已传递给该函数的工作表上自动生成可安装的触发器.
I figured out how to automatically create new installable triggers thanks to Cooper. I added a function to the master spreadsheet script that automatically generates an installable trigger on a sheet whose ID is passed into the function.
正确生成了新的电子表格，并且正确生成了新的可安装触发器. 但是，新触发器是在原始MASTER LIST电子表格上创建的，而不是在NEW电子表格上创建的.执行记录尤其使我感到困惑，因为它说它是在正确的电子表格上创建触发器.但是，该触发器确实未出现在新的电子表格中，并且在Google我的所有触发器"页面上，该触发器具有分配给主列表"的新触发器.
The new spreadsheet is generated correctly, and the new installable trigger is generated correctly. However, the new trigger is being created on the original, MASTER LIST spreadsheet, not the NEW spreadsheet. The execution transcript in particular is confusing me, because it says it's creating the trigger on the correct spreadsheet. However, the trigger indeed does not appear on the new spreadsheet and on the Google 'All my Triggers' page it has a new trigger assigned to the MASTER LIST.
[19-06-07 16:32:45:190 PDT] [19-06-07 22:43:51:832 EDT] TriggerBuilder.forSpreadsheet([这是新工作表的ID-正确的ID ，但实际上是针对当前电子表格执行此操作]] [0秒]
[19-06-07 16:32:45:190 PDT] [19-06-07 22:43:51:832 EDT] TriggerBuilder.forSpreadsheet([This is the ID of the new sheet- the correct ID, but it's actually doing this for the current Spreadsheet instead]) [0 seconds]
代码(请了解所有文件/文件夹ID已删除):
The Code (please understand all file/folder ID's have been removed):
function main(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 22:43:46:036 EDT] Starting execution
[19-06-07 22:43:46:041 EDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[19-06-07 22:43:46:042 EDT] Spreadsheet.getActiveSheet() [0 seconds]
[19-06-07 22:43:46:043 EDT] SpreadsheetApp.getActiveRange() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getRow() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getLastRow() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getLastColumn() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:043 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:044 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:044 EDT] Range.offset([0, -1]) [0 seconds]
[19-06-07 22:43:46:194 EDT] Range.isBlank() [0.149 seconds]
[19-06-07 22:43:46:194 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:194 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:195 EDT] Range.offset([0, 6]) [0 seconds]
[19-06-07 22:43:46:195 EDT] Range.getValue() [0 seconds]
[19-06-07 22:43:46:195 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:195 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:46:196 EDT] Range.offset([0, 2]) [0 seconds]
[19-06-07 22:43:46:196 EDT] Range.isBlank() [0 seconds]
[19-06-07 22:43:46:196 EDT] Sheet.getRange([L1]) [0 seconds]
[19-06-07 22:43:46:335 EDT] Range.getValue() [0.138 seconds]
[19-06-07 22:43:46:452 EDT] SpreadsheetApp.openById([ID]) [0.117 seconds]
[19-06-07 22:43:49:422 EDT] Spreadsheet.copy([Untitled Job]) [2.969 seconds]
[19-06-07 22:43:49:581 EDT] DriveApp.getFolderById([ID]) [0.157 seconds]
[19-06-07 22:43:49:581 EDT] Spreadsheet.getId() [0 seconds]
[19-06-07 22:43:49:769 EDT] DriveApp.getFileById([ID]) [0.188 seconds]
[19-06-07 22:43:49:770 EDT] File.getId() [0 seconds]
[19-06-07 22:43:51:126 EDT] Folder.addFile([Untitled Job]) [1.356 seconds]
[19-06-07 22:43:51:275 EDT] DriveApp.getRootFolder() [0.148 seconds]
[19-06-07 22:43:51:275 EDT] File.getId() [0 seconds]
[19-06-07 22:43:51:827 EDT] Folder.removeFile([Untitled Job]) [0.551 seconds]
[19-06-07 22:43:51:827 EDT] Spreadsheet.getId() [0 seconds]
[19-06-07 22:43:51:829 EDT] Logger.log([1, []]) [0 seconds]
[19-06-07 22:43:51:829 EDT] Logger.log([2, []]) [0 seconds]
[19-06-07 22:43:51:831 EDT] ScriptApp.newTrigger([IncrementDecrement]) [0 seconds]
[19-06-07 22:43:51:832 EDT] TriggerBuilder.forSpreadsheet([This is the ID of the new sheet- the correct ID]) [0 seconds]
[19-06-07 22:43:51:832 EDT] SpreadsheetTriggerBuilder.onEdit() [0 seconds]
[19-06-07 22:43:52:356 EDT] SpreadsheetTriggerBuilder.create() [0.523 seconds]
[19-06-07 22:43:52:356 EDT] Spreadsheet.getId() [0 seconds]
[19-06-07 22:43:52:357 EDT] Sheet.getRange([L1]) [0 seconds]
[19-06-07 22:43:52:357 EDT] Range.getValue() [0 seconds]
[19-06-07 22:43:52:470 EDT] SpreadsheetApp.openById([ID]) [0.112 seconds]
[19-06-07 22:43:52:780 EDT] Spreadsheet.rename([Name]) [0.31 seconds]
[19-06-07 22:43:52:781 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:52:781 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:52:782 EDT] SpreadsheetApp.openById([ID]) [0 seconds]
[19-06-07 22:43:52:783 EDT] Spreadsheet.getSheets() [0 seconds]
[19-06-07 22:43:52:783 EDT] Sheet.getRange([B1]) [0 seconds]
[19-06-07 22:43:52:784 EDT] Range.offset([0, -1]) [0 seconds]
[19-06-07 22:43:52:784 EDT] Range.getValue() [0 seconds]
[19-06-07 22:43:52:785 EDT] Range.setValue([Name]) [0 seconds]
[19-06-07 22:43:52:785 EDT] SpreadsheetApp.openById([ID]) [0 seconds]
[19-06-07 22:43:52:786 EDT] Spreadsheet.getSheets() [0 seconds]
[19-06-07 22:43:52:786 EDT] Sheet.getRange([B2]) [0 seconds]
[19-06-07 22:43:52:787 EDT] Range.getValue() [0 seconds]
[19-06-07 22:43:52:787 EDT] Range.setValue([2/30/19]) [0 seconds]
[19-06-07 22:43:52:787 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:52:787 EDT] Range.getColumn() [0 seconds]
[19-06-07 22:43:52:788 EDT] Range.offset([0, 2]) [0 seconds]
[19-06-07 22:43:52:788 EDT] SpreadsheetApp.openById([ID]) [0 seconds]
[19-06-07 22:43:52:788 EDT] Spreadsheet.getUrl() [0 seconds]
[19-06-07 22:43:52:905 EDT] Range.setFormula([=HYPERLINK("Link","Click here for itemization")]) [0.116 seconds]
[19-06-07 22:43:52:906 EDT] Sheet.getRange([A3:D1000]) [0 seconds]
[19-06-07 22:43:52:908 EDT] Range.sort([{column=2.0, ascending=false}]) [0 seconds]
[19-06-07 22:43:53:266 EDT] Execution succeeded [6.868 seconds total runtime]
推荐答案
更新:看来这是:
-一个错误，在这种情况下，我发布了错误报告
-A bug, in which case I've posted a bug report
-文档不明确的问题:ScriptApp.newTrigger.forSpreadsheet(ID)的Google文档指出，此创建并返回绑定到具有给定ID的电子表格的SpreadsheetTriggerBuilder".如果Google的意图是让触发器通过使用给定ID的电子表格上的编辑来激活，则应在文档中反映出来.相反，它是模棱两可的，我在阅读时的理解是，新触发器将以给定ID添加到电子表格的触发器列表中.
-An issue with unclear documentation: the Google documentation for ScriptApp.newTrigger.forSpreadsheet(ID) states that this "Creates and returns a SpreadsheetTriggerBuilder tied to the spreadsheet with the given ID." If Google's intent was for the trigger to activate by edits on the spreadsheet with the given ID, this should be reflected in the documentation. Instead, it's ambiguous and my understanding when I read it was that the new trigger would be added to the triggers list of the spreadsheet with the given ID.
如果还有其他建议或建议，请随时发布.谢谢.
If there are any further suggestions or takes on this, feel free to post. Thanks.
这篇关于错误的电子表格中生成可安装的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
