我有一個谷歌應用程式腳本，它作為谷歌表格插件發布。該插件的一個特點是它接受一個單元格的值，查詢一個 Google Big Query 表，并用下拉選項填充下一個單元格。一旦用戶選擇了一個選項，另一個查詢就會發送到 Big Query，依此類推。
因為當用戶編輯單元格時會發生此活動，所以我認為我需要使用 onEdit() 型別功能。但是，檔案明確指出我不能onEdit()用來發出需要授權的此類請求（我使用服務帳戶），因此我需要使用觸發器，并且因為它是附加的，所以我需要使用可安裝觸發器。
我按照檔案以編程方式執行此操作
/**
* Creates a trigger for when a spreadsheet opens.
*/
function createSpreadsheetOpenTrigger() {
var ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger('stu')
.forSpreadsheet(ss)
.onEdit() //the docs actually is onOpen()
.create();
}
反過來，函式stu從單元格獲取值并呼叫另一個查詢 Big Query 的函式：
function stu(e) {
var activeCell = e.range;
var cellValue = e.value;
....
....
if (wsName === "Targets" && c < 7 && r > 1) {
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Targets");
var rowValues = ws.getRange(r, 1, 1, 6).getValues();
runQuery(cellValue, r, nextCol, rowValues); // runQuery executes the BigQuery query
}
}
我遇到了幾個問題：
當用戶安裝插件時，似乎不可能安裝可安裝的觸發器。為了解決這個問題，我遵循了 https://stackoverflow.com/questions/61314827/install-trigger-for-google-app-script-in-custom-addon 上的建議，并添加了一個選單項以允許用戶安裝扳機。
我已經開始收到錯誤，例如
Exception: This add-on has created too many edit triggers in this document for this Google user account.
任何人都可以建議我在創建可安裝觸發器 onEdit 時是否正確
ScriptApp.newTrigger('stu')
.forSpreadsheet(ss)
.onEdit()
.create();
也許我應該使用
ScriptApp.newTrigger('stu')
.forSpreadsheet(ss)
.onOpen()
.create();
然后在函式中將功能stu包裝在一個onEdit()：
function stu(e) {
onEdit(e) {
var activeCell = e.range;
var cellValue = e.value;
....
....
if (wsName === "Targets" && c < 7 && r > 1) {
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Targets");
var rowValues = ws.getRange(r, 1, 1, 6).getValues();
runQuery(cellValue, r, nextCol, rowValues); // runQuery executes the BigQuery
query
}
}
}
If I am correct in using an Installable Trigger should I be deleting previous instances in order to avoid the Exception: This add-on has created too many edit triggers in this document for this Google user account. error? I can see that there is a way to check for the existence of triggers and delete as appropriate (also on https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually).
I suspect that actually I am fundamentally understanding how I should be using installable triggers. I've read through the docs, but any advice much appreciated.
uj5u.com熱心網友回復：
這只會產生一個：
function creatonedittrigger(funcname) {
if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == funcname).length == 0) {
ScriptApp.newTrigger(funcname).forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/381021.html