google-sheets - 在具有多行的谷歌工作表上过滤

Question

我目前正在研究允许注册人员的工作表文件。每个人对应一条线，有必要分配不同的数据（彼此相关）。为了方便输入这些信息，我选择使用下拉菜单。

例如，如果您选择某个值作为第一个数据，我希望在第二个下拉菜单中只看到与该选择对应的值。所以我有一个保存记录的文件和另一个写入要分配的数据的文件（文件链接）。

为了创建两个下拉菜单之间的链接，我使用了过滤器功能（在第三列中）只显示与第一个下拉菜单中所做的选择相对应的数据。

对于第一张唱片，一切都完美无缺。这是相应的宏：“=FILTER(B2:B;A2:A=Sheet!B2)” 但是，当我转到第 3 行时，它不再起作用，因为我在公式中填写了 B2。我不认为自己会在 X 行上复制这种通信，因为我不知道应该记录多少人。

我确信有另一种方法可以解决这个问题，但我在互联网上找不到。请你帮助我好吗？提前感谢您的回答（对不起我的英语，这不是我的专长）

标签： google-sheets

Answer 1

您可以使用 Apps 脚本根据在 B 列中选择的选项在 C 列中创建下拉数据验证。

示例代码：

function onEdit(e) {
  Logger.log(JSON.stringify(e));
  var ss = e.source;
  var activeSheet = ss.getActiveSheet();
  var cell = e.range;

  if (activeSheet.getName() == "Sheet" && cell.getColumn() == 2 && cell.getRow() > 1) {
    // Get selected choice 1
    var choice1 = cell.getValue();
    Logger.log(choice1);

    if (choice1 == ""){
      // Remove data validation in the adjacent column (column C)
      cell.offset(0,1).clearDataValidations()
      cell.offset(0,1).setValue("");
    }else{
      // Create a dropdown list
      // Read database
      var dataBase = ss.getSheetByName("DataBase").getRange("A2:B").getValues();
      // Filter data
      var choices = [];
      dataBase.forEach(row => {
        if(row[0]==choice1){
          choices.push(row[1]);
        }
      });
      Logger.log(choices);

      // Create data validation
      var rule = SpreadsheetApp.newDataValidation()
        .requireValueInList(choices, true)
        .setAllowInvalid(false)
        .build();
      // Add data validation to the adjacent column (column C) and reset cell value
      cell.offset(0,1).setValue("");
      cell.offset(0,1).setDataValidation(rule);
    }
    
  }
}

它能做什么？

• 使用onEdit 简单触发器，每当有值时创建数据验证
• 检查修改后的单元格是否在“工作表”选项卡中，在 B 列（索引 2）中，修改的行是否 > 第 1 行
• 获取B列中选择的choice1的值
• 获取“数据库”选项卡中的值并过滤数据。根据所选的choice1 获取Choice 2 的值
• 根据过滤后的数据创建一个新的数据验证下拉列表到相邻单元格（C 列）。将值重置为空单元格。
• 如果选择的选项为空（已删除单元格），则删除相邻单元格上的数据验证并将值设置为空单元格

输出：