Google Sheets动态更新下拉列表：避免重复值（Apps Script实现）

核心思路 #

每次编辑C列的下拉单元格后，重新生成每个单元格的下拉数据源：原始选项（F10:F24）排除其他单元格已选中的值，同时保留当前单元格自身的已选值，彻底避免"Invalid: Input must fall within specified range"错误。

完整代码 #

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  // 替换成你的工作表名称，比如"表单1"
  if (sheet.getName() !== "Sheet1") return;

  const targetRange = e.range;
  // 仅处理C列第10到24行的编辑事件
  if (targetRange.getColumn() !== 3 || targetRange.getRow() < 10 || targetRange.getRow() > 24) return;

  // 获取F10:F24的原始选项，过滤空值
  const originalOptions = sheet.getRange("F10:F24").getValues().flat().filter(val => val !== "");
  // 获取C10:C24所有已填写的值
  const selectedValues = sheet.getRange("C10:C24").getValues().flat();

  // 遍历C10到C24，逐个更新下拉规则
  for (let row = 10; row <= 24; row++) {
    const currentCell = sheet.getRange(row, 3);
    const currentValue = currentCell.getValue();
    
    // 生成可用选项：保留当前单元格值，排除其他已选值
    const availableOptions = originalOptions.filter(opt => {
      return selectedValues.indexOf(opt) === -1 || opt === currentValue;
    });

    // 构建数据验证规则
    let dataValidation;
    if (availableOptions.length > 0) {
      dataValidation = SpreadsheetApp.newDataValidation()
        .requireValueInList(availableOptions, true) // true显示下拉菜单
        .setAllowInvalid(false) // 禁止输入列表外的值
        .build();
    } else {
      // 所有选项已被选完时的处理逻辑
      dataValidation = SpreadsheetApp.newDataValidation()
        .setAllowInvalid(true)
        .setHelpText("所有选项已被选择")
        .build();
    }

    currentCell.setDataValidation(dataValidation);
  }
}

使用步骤 #

1. 打开你的Google Sheets，点击顶部菜单栏「扩展程序」→「Apps 脚本」，粘贴上述代码。
2. 将代码中的"Sheet1"替换成你的实际工作表名称。
3. 保存脚本后回到表格测试：在C10选择一个值，C11的下拉会自动移除该值；修改C10的内容，后续单元格的下拉会同步更新。

关键细节 #

• 每个单元格的下拉列表会保留自身已选值，避免因数据源更新导致已选内容触发报错。
• 自动过滤F列原始选项中的空值，防止下拉出现无效空选项。
• 所有选项被选完时会弹出提示，可根据需求调整这部分逻辑。

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