如何在Google Apps Script中基于另一工作表特定行构建动态数据验证

问题分析 #

你的代码当前将Menu表的B1:B11范围统一设置为Parts表某一行的验证规则，导致所有单元格下拉选项完全一致。要实现Bn对应Parts表第n行的需求，需要为每个单元格单独匹配对应行的数据源。

修正后的代码 #

function setRowSpecificValidation() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();  
  const menuSheet = ss.getSheetByName("Menu");
  const partSheet = ss.getSheetByName("Parts");
  
  // 定义需要处理的行数：对应Menu表B1到B11，可根据实际需求调整
  const startRow = 1;
  const endRow = 11;

  // 逐行循环设置数据验证
  for (let line = startRow; line <= endRow; line++) {
    // 获取Menu表当前行的B列单元格
    const menuCell = menuSheet.getRange(`B${line}`);
    // 获取Parts表对应行的整行有效数据范围
    const partRange = partSheet.getRange(line, 1, 1, partSheet.getLastColumn());
    // 构建数据验证规则
    const partRule = SpreadsheetApp.newDataValidation()
      .requireValueInRange(partRange)
      .setAllowInvalid(false) // 可选：禁止输入下拉列表外的值，不需要可删除此行
      .build();
    // 为当前单元格设置验证规则
    menuCell.setDataValidation(partRule);
  }
}

关键说明 #

• 逐行单独处理：通过for循环遍历目标行范围，确保每个Menu!Bn单元格对应Parts表的第n行数据
• 动态适配列数：使用partSheet.getLastColumn()自动获取Parts表的有效列数，避免硬编码列范围（比如固定A到J列）
• 灵活配置验证规则：setAllowInvalid(false)用于强制用户只能选择下拉选项，若需要允许自定义输入可直接删除该行

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