使用谷歌表格中的值填充谷歌从下拉列表中

Question

我浏览了所有的问题，我不太清楚我做错了什么。这是背景：

我创建了一个包含多个选项卡的电子表格。每个选项卡对应于事件类型(例如游行，节日，体育)。其中每个选项卡都用于将特定作业编号分配给特定事件。游行工作人数在2000范围内，节日3000范围，体育4000范围。

然后，我有一个不同的选项卡，它定义了一个数组，该数组收集所有作业编号和事件，并按字母顺序对它们进行排序。此选项卡称为"Active_Job_Numbers"。

我的目标是创建几个表单，这些表单为"Active_Job_Numbers"选项卡上的值调用此电子表格。我有点工作，但不是完全。每个表格都有一个目的，例如向会计提交请求以处理信用卡交易;提交支票请求的另一种表格。

所有这些请求都必须与不断变化的工作编号列表相关联。当我更新电子表格时，电子表格中的下拉列表将根据"Active_Job_Numbers"数据填充。

我使用的代码确实有效，但是如果我更新电子表格上的作业编号或事件名称，表单仍然会看到旧值。我已经为 onSubmit 和 onOpen 设置了触发器，尽管如此，我可以让表单中的下拉列表更新的唯一方法是更改其中一个"var"标签。然后保存它。然后重新打开窗体。这是一个令人震惊的节目。

这是我正在使用的代码：

function updateForm(){
// call your form and connect to the drop-down item
var creditCardRequestGoogleForm = FormApp.openById("1LqtPLGEhocGdkg75mkdldfplxSxWJmYZCp3u1bMwVQQ");
var jobNumberDropDown = creditCardRequestGoogleForm.getItemById("1632374512").asListItem();


// identify the sheet where the data resides needed to populate the drop-down
var ss = SpreadsheetApp.openById("17fgel9Jfl4Aske85mfkwsphBQpZgtGhR8Q");
SpreadsheetApp.setActiveSpreadsheet(ss);
var activeJobNumbers = ss.getSheetByName('Active_Job_Numbers');
// grab the values in the first column of the sheet - use 2 to skip header row 
var jobNumValues = activeJobNumbers.getRange(2, 1, activeJobNumbers.getMaxRows() - 1).getValues();
var jobNums = [];
// convert the array ignoring empty cells
for(var i = 0; i < jobNumValues.length; i++)    
if(jobNumValues[i][0] != "")
jobNums[i] = jobNumValues[i][0];
// populate the drop-down with the array data
jobNumberDropDown.setChoiceValues(jobNums);
}

一个单独但相关的问题...我有一些表单有两个不同的下拉元素，它们调用同一个电子表格，但调用不同的选项卡。我不确定如何对这个脚本进行分组，所以我必须像这样分开：

function updateForm(){
// call your form and connect to the drop-down item
var ccrForm = FormApp.openById("1r5n6Sr7fhtGCqht_QHy9qWR1v2ESabxdE-bMxjR-M");
var jobNumberDropDown = ccrForm.getItemById("1678955392").asListItem();


// identify the sheet where the data resides needed to populate the drop-down
var ss = SpreadsheetApp.openById("17eGAlcnBB7ejdfqp3HBQBK9wJAKWYhBQpZgtGhR8Q");
SpreadsheetApp.setActiveSpreadsheet(ss);
var currentJobNumbers = ss.getSheetByName('Active Job Numbers');
// grab the values in the first column of the sheet - use 2 to skip header row 
var jobNumValues = currentJobNumbers.getRange(2, 1, currentJobNumbers.getMaxRows() - 1).getValues();
var jobNums = [];
// convert the array ignoring empty cells
for(var i = 0; i < jobNumValues.length; i++)    
if(jobNumValues[i][0] != "")
jobNums[i] = jobNumValues[i][0];
// populate the drop-down with the array data
jobNumberDropDown.setChoiceValues(jobNums);
}
function updateForm(){
// This call your form and connect to the drop-down item
var form = FormApp.openById("1r5n6Sr7fhtGCqht_QHy9qWR1v2ESabxdE-bMxjR-M");
var poNumberDropDown = form.getItemById("57865789991").asListItem();


// This is the second dropdown which calls the same spreadsheet but different tab NEED HELP COMBINING THESE TWO MORE ELEGANTLY
var ss = SpreadsheetApp.openById("17eGAlcnBB7ejdfqp3HBQBK9wJAKWYhBQpZgtGhR8Q");
SpreadsheetApp.setActiveSpreadsheet(ss);
var currentPONumbers = ss.getSheetByName('PO_Number');
// grab the values in the first column of the sheet - use 2 to skip header row 
var poNumValues = currentPONumbers.getRange(2, 1, currentPONumbers.getMaxRows() - 1).getValues();
var poNums = [];
// convert the array ignoring empty cells
for(var i = 0; i < poNumValues.length; i++)    
if(poNumValues[i][0] != "")
poNums[i] = poNumValues[i][0];
// populate the drop-down with the array data
poNumberDropDown.setChoiceValues(poNums);
}

任何见解将不胜感激。

Answer 1

表单的 onOpen 触发器，如文档中所述：

当用户打开表单进行响应时，不会发生此事件，但 而是当编辑器打开表单进行修改时

相反，您可以使用可安装的触发器在编辑工作表时激活代码。

此外，而不是每次对电子表格进行编辑时都修改所有表单。您可以在电子表格中检查修改了哪个工作表以修改相应的表单。要确定修改了哪个工作表，可以使用事件对象。

这是示例代码，您可以修改它供您使用。

function installedEdit(evtObj){
Logger.log("Edit running")
var sheetName = evtObj.range.getSheet().getSheetName()
Logger.log(sheetName)
var itemId="None"
switch (sheetName){
case "Active Job Numbers": 
itemId = "1678955392"
updateForm(sheetName,itemId)
break;
case "PO_Number":
itemId = "57865789991"
updateForm(sheetName,itemId)
break;
}
}
function updateForm(sheetName,itemId){
// call your form and connect to the drop-down item
var form = FormApp.openById("1r5n6Sr7fhtGCqht_QHy9qWR1v2ESabxdE-bMxjR-M");
var dropDown = form.getItemById(itemId).asListItem();
// identify the sheet where the data resides needed to populate the drop-down
var ss = SpreadsheetApp.openById("17eGAlcnBB7ejdfqp3HBQBK9wJAKWYhBQpZgtGhR8Q");
SpreadsheetApp.setActiveSpreadsheet(ss);
var sheet = ss.getSheetByName(sheetName);
// grab the values in the first column of the sheet - use 2 to skip header row 
var choiceValues = sheet.getRange(2, 1, sheet.getLastRow() - 2).getValues();
var choices = [];
// convert the array ignoring empty cells
for(var i = 0; i < choiceValues.length; i++)    
if(choiceValues[i][0] != "")
choices[i] = choiceValues[i][0];
// populate the drop-down with the array data
dropDown.setChoiceValues(choices);
}

注意：请确保installedEdit()设置为在编辑电子表格并将其绑定到电子表格时运行。