如何创建一个二级依赖下拉列表，可以使用谷歌应用程序脚本将值设置为多列

Question

[我的目标]使用谷歌应用程序脚本和谷歌表单，我想创建一个二级依赖下拉列表，如果我从第一个下拉列表中选择一个值，它将允许我从下一个下拉列表选择另一个值。选择第二个下拉列表后，我希望能够在每列中设置6个值。例如，在"；营养计划表"；屏幕截图，如果我选择"；蛋白质"；对于B列，C列下将提供另一个下拉列表来选择成分。在这种情况下；鸡胸肉"现在，我还想做的是，当我为列C选择一个值时，使列D-H自动填充。注意：配料来自一张名为"；食品数据库；我还附上了一张截图。

[问题]当在"；营养计划"；床单

[到目前为止我所能做的]我能够在YouTube视频的帮助下设置代码(链接：https://www.youtube.com/watch?v=s-I8Z4nTDak&list=PL32yvu15_0Tha8QZBTyAF0NCAreowqOFJ(，但我不知道如何为每个列设置值。

【屏幕截图】

• 营养计划表屏幕截图：在此处输入图像描述
• 食品数据库表屏幕截图：在此处输入图像描述

[代码]

var wsNameNutritionPlan = "Nutrition Plan";
var wsNameFoodDatabase = "Food Database";
var firstLevelColumn = 2;
var secondLevelColumn = 3;
var thirdLevelColumn = 4;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsNameNutritionPlan);
var wsFoodDatabase = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsNameFoodDatabase);
var foodDatabase = wsFoodDatabase.getRange(3, 2, wsFoodDatabase.getLastRow()-2, 7).getValues();
function onEdit(e) {
var activeCell = e.range;
var val = activeCell.getValue();
var r = activeCell.getRow();
var c = activeCell.getColumn();
var wsName = activeCell.getSheet().getName();

if(wsName === wsNameNutritionPlan && c === firstLevelColumn && r > 6) {
applyFirstLevelValidation(val, r);
} else if(wsName === wsNameNutritionPlan && c === secondLevelColumn && r > 6) {
applySecondLevelValidation(val, r);
}
}
function applyFirstLevelValidation(val, r){
if(val === "") {
ws.getRange(r, secondLevelColumn).clearContent();
ws.getRange(r, secondLevelColumn).clearDataValidations();
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
} else {
ws.getRange(r, secondLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
var filteredFoodDatabase = foodDatabase.filter(function(f) {return f[0] === val});
var listToApply = filteredFoodDatabase.map(function(f) {return f[1]});
var cell = ws.getRange(r, secondLevelColumn);
}
}
function applySecondLevelValidation(val, r){
if(val === "") {
ws.getRange(r, thirdLevelColumn).clearContent();
ws.getRange(r, thirdLevelColumn).clearDataValidations();
} else {
ws.getRange(r, thirdLevelColumn).clearContent();
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var filteredFoodDatabase = foodDatabase.filter(function(f) {return f[0] === firstLevelColValue && f[1] === val});
var listToApply = filteredFoodDatabase.map(function(f) {return f[2]});
var cell = ws.getRange(r, thirdLevelColumn);
applyValidationToCell(listToApply, cell);
}
}
function applyValidationToCell(listToApply, cell) {
var rule = SpreadsheetApp.newDataValidation().requireValueInList(listToApply).setAllowInvalid(false).build();
cell.setDataValidation(rule);
}

Answer 1

我相信你的目标如下。

• 当列的下拉列表的值为"0"时；B"；在"；营养计划"；则将下拉列表添加到列"；D"；在同一行。在这种情况下，当列"的下拉列表的值为0时；C"；在"；营养计划"；如果发生更改，则要将"；食品数据库"；到列"；D"；至"；H〃；。
  • 在这种情况下，从"；食品数据库"；。该行与列"的值相同；B"；以及"；C"；的下拉列表

修改点：

• 在您的脚本中，我认为当运行applyFirstLevelValidation(val, r)的函数时，不会添加任何数据验证规则。因为applyValidationToCell(listToApply, cell)未用于此函数
• 当你想把列的值"；D"；至"；H〃；当列"的下拉列表显示时；C"；则需要修改applySecondLevelValidation(val, r)的功能。
  • 我认为可以使用filteredFoodDatabase的值
• 我认为重复的剧本可以总结一下
• 在您的情况下，我认为当更改下拉列表时，可能还需要清除列"中的单元格值；D"；至"；H〃

当以上几点反映到您的脚本中时，它变成如下。

修改的脚本：

在applyFirstLevelValidation(val, r)的功能中，请修改如下。

function applyFirstLevelValidation(val, r){
ws.getRange(r, secondLevelColumn, 1, 6).clearContent();
ws.getRange(r, secondLevelColumn).clearDataValidations();
if(val != "") {
var filteredFoodDatabase = foodDatabase.filter(function(f) {return f[0] === val});
var listToApply = filteredFoodDatabase.map(function(f) {return f[1]});
var cell = ws.getRange(r, secondLevelColumn);
applyValidationToCell(listToApply, cell);
}
}

在applySecondLevelValidation(val, r)的功能中，请修改如下。

function applySecondLevelValidation(val, r){
ws.getRange(r, thirdLevelColumn, 1, 5).clearContent();
if(val != "") {
var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
var filteredFoodDatabase = foodDatabase.filter(function(f) {return f[0] === firstLevelColValue && f[1] === val});
if (filteredFoodDatabase.length > 0) {
var [,,...values] = filteredFoodDatabase[0];
ws.getRange(r, thirdLevelColumn, 1, values.length).setValues([values]);
}
}
}

• 在这种情况下，将值放入单元格，而不是数据验证

注意：

• 请在V8运行时使用该脚本
• 在这个修改中，它假设从您的样本图像中，列的组合"；A"；至"；B"；在"；食品数据库"；是每行中唯一的