求助编写Google Apps Script实现Google Sheets姓氏-名字联动数据验证

核心需求 #

在CASTING工作表中实现依赖型下拉验证：当B列（B3:B1000）选择某一姓氏时，同一行的C列仅显示该姓氏对应的所有名字，数据来源为APPOGGIO DATABASE表的D列（姓氏）和E列（名字）。

原代码问题分析 #

你的现有代码存在几个关键问题，导致逻辑无法正常运行：

• 未绑定编辑触发事件，无法在用户修改B列时自动更新C列验证
• 获取当前单元格值的方式错误：getActiveRange().getValues()返回二维数组，直接与filtro[0]比较会失效
• 数据范围未指定工作表：ss.getRange("D2:E")默认取当前活动表，而非APPOGGIO DATABASE

修正后的完整代码 #

// 一次性运行：设置B列的姓氏数据验证
function setupLastNameValidation() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const validationSheet = ss.getSheetByName("APPOGGIO DATABASE");
  const dataSheet = ss.getSheetByName("CASTING");
  
  const lastNameRange = dataSheet.getRange("B3:B1000");
  // 从APPOGGIO DATABASE的D2:D获取唯一姓氏列表
  const lastNameRule = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false)
    .requireValueInRange(validationSheet.getRange("D2:D"), true)
    .build();
  
  lastNameRange.clearDataValidations();
  lastNameRange.setDataValidation(lastNameRule);
}

// 绑定onEdit触发器：当编辑B列时自动更新对应C列的名字验证
function onEdit(e) {
  const ss = e.source;
  const activeSheet = ss.getActiveSheet();
  const editedCell = e.range;
  
  // 仅处理CASTING表中B3:B1000的单元格编辑
  if (activeSheet.getName() !== "CASTING" || editedCell.getColumn() !== 2 || editedCell.getRow() < 3) {
    return;
  }
  
  const validationSheet = ss.getSheetByName("APPOGGIO DATABASE");
  // 获取APPOGGIO DATABASE中D2:E的所有数据，过滤空行
  const allData = validationSheet.getRange("D2:E").getValues().filter(row => row[0] !== "");
  
  const selectedLastName = editedCell.getValue();
  // 筛选出当前姓氏对应的所有名字，去重并过滤空值
  const matchedFirstNames = allData
    .filter(row => row[0] === selectedLastName)
    .map(row => row[1])
    .filter((name, index, self) => name !== "" && self.indexOf(name) === index);
  
  const targetCell = activeSheet.getRange(editedCell.getRow(), 3); // 同一行的C列
  targetCell.clearDataValidations();
  
  if (matchedFirstNames.length > 0) {
    // 生成名字的验证规则
    const firstNameRule = SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .requireValueInList(matchedFirstNames, true)
      .build();
    targetCell.setDataValidation(firstNameRule);
  }
}

关键逻辑说明 #

1. setupLastNameValidation函数：

   • 一次性运行即可，给CASTING表的B3:B1000设置姓氏下拉验证，数据源是APPOGGIO DATABASE的D列。

2. onEdit函数：

   • 监听工作表的编辑事件，仅对CASTING表B列（第2列）第3行及以下的单元格生效
   • 获取用户选择的姓氏，从APPOGGIO DATABASE的D2:E数据中筛选对应名字，自动去重并过滤空值
   • 将筛选后的名字列表设置为同一行C列的下拉选项；如果没有匹配的名字，则清空C列的验证规则

使用步骤 #

1. 打开你的Google表格，点击「扩展程序」→「Apps脚本」，替换原代码为上述代码
2. 先运行setupLastNameValidation函数（首次运行需要授权）
3. 回到CASTING表，在B列选择姓氏，对应的C列会自动生成仅包含该姓氏对应名字的下拉列表

内容的提问来源于stack exchange，提问作者diblax