Google Sheets按排名筛选可用礼品的动态下拉列表实现咨询

步骤1：确认基础数据配置 #

• 「Choice」工作表结构固定：A列为优先级Rank（1开始的连续正整数，数值越小优先级越高）、B列为领取人姓名、C列为已选礼品（后续配置下拉的目标列），默认表头在第1行，如有调整可同步修改后续脚本参数
• 「Gifts」工作表A列为所有不重复礼品，总数和领取人总数完全一致

步骤2：进入Google Apps Script编辑器 #

打开当前表格，点击顶部菜单栏「扩展程序」>「Apps Script」，删除编辑器内默认的myFunction示例代码

步骤3：粘贴功能脚本 #

将以下代码粘贴到编辑器中，表头行、列位置有调整的可修改代码中对应参数：

function onEdit(e) {
  const ss = e.source;
  const choiceSheet = ss.getSheetByName("Choice");
  const giftsSheet = ss.getSheetByName("Gifts");
  // 读取全量礼品列表
  const allGifts = giftsSheet.getRange(1,1,giftsSheet.getLastRow(),1).getValues().flat().filter(item => item);
  // 仅响应Choice表C列的操作，跳过表头行
  if(e.range.getSheet().getName() !== "Choice" || e.range.columnStart !== 3 || e.range.rowStart === 1) return;
  const currentRow = e.range.rowStart;
  // 读取当前行用户的优先级
  const currentRank = choiceSheet.getRange(currentRow, 1).getValue();
  // 读取优先级更高的用户已选礼品列表
  const selectedGifts = choiceSheet.getRange(2,3,currentRow - 2,1).getValues().flat().filter(item => item);
  // 过滤出当前用户可选择的剩余礼品
  const availableGifts = allGifts.filter(gift => !selectedGifts.includes(gift));
  // 给当前单元格生成下拉列表
  const rule = SpreadsheetApp.newDataValidation().requireValueInList(availableGifts).build();
  e.range.setDataValidation(rule);
}

步骤4：启用脚本并测试 #

• 点击编辑器顶部保存按钮，自定义项目名称（例如「礼品选择工具」），按照页面提示完成脚本访问表格的权限授权
• 回到「Choice」工作表，点击优先级最高（Rank=1）用户对应的C列单元格，下拉列表会展示所有礼品；选完后点击下一级优先级用户的C列单元格，下拉列表会自动过滤掉已经被高优先级用户选走的礼品，符合需求规则

注意事项 #

• 如果需要修改高优先级用户的已选礼品，直接修改对应单元格内容即可，低优先级用户的下拉列表下次点击时会自动更新可选范围
• 礼品列表有调整时直接修改「Gifts」工作表A列内容即可，脚本会自动读取最新的礼品数据

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