基于单元格输入的Apps Script条件依赖下拉菜单开发求助

修正后的可用代码 #

function onEdit(e) {
  const activeSheet = e.source.getActiveSheet();
  const editedCell = e.range;

  // 只处理你的主工作表，把"价格追踪表"改成你实际的主表名称
  if (activeSheet.getName() !== "价格追踪表") return;

  // 监听D13（breed type选择）的变化
  if (editedCell.getA1Notation() === "D13") {
    const breedType = editedCell.getValue();
    let geneSheet;

    // 根据选择切换基因列表工作表
    if (breedType === "Modern") {
      geneSheet = e.source.getSheetByName("Modern Gene List");
    } else if (breedType === "Ancient") {
      geneSheet = e.source.getSheetByName("Ancient Gene List");
    } else {
      // 选了其他值就清空下拉菜单
      clearDropdowns(["B15", "B16", "B17"], activeSheet);
      return;
    }

    // 读取基因列表（A列从第2行开始到最后一行有数据的地方）
    const geneRange = geneSheet.getRange(`A2:A${geneSheet.getLastRow()}`);
    const geneList = geneRange.getValues().flat().filter(val => val); // 转成一维数组并过滤空值

    // 创建下拉菜单规则
    const validationRule = SpreadsheetApp.newDataValidation()
      .requireValueInList(geneList)
      .setAllowInvalid(false)
      .build();

    // 给primary(B15)、secondary(B16)、tertiary(B17)设置下拉
    ["B15", "B16", "B17"].forEach(cell => {
      activeSheet.getRange(cell).setDataValidation(validationRule);
    });
  }

  // Ancient类型下，监听breed选择（假设breed在D14，根据你的表格调整）
  const currentBreedType = activeSheet.getRange("D13").getValue();
  if (currentBreedType === "Ancient" && editedCell.getA1Notation() === "D14") {
    const selectedBreed = editedCell.getValue();
    const ancientSheet = e.source.getSheetByName("Ancient Gene List");

    // 读取Ancient表的breed-基因对应数据（假设A列breed，B列基因）
    const allData = ancientSheet.getRange(`A2:B${ancientSheet.getLastRow()}`).getValues();
    // 筛选出当前breed对应的基因
    const filteredGenes = allData
      .filter(row => row[0] === selectedBreed)
      .map(row => row[1])
      .filter(val => val);

    const validationRule = SpreadsheetApp.newDataValidation()
      .requireValueInList(filteredGenes)
      .setAllowInvalid(false)
      .build();

    ["B15", "B16", "B17"].forEach(cell => {
      activeSheet.getRange(cell).setDataValidation(validationRule);
    });
  }
}

// 辅助函数：清空指定单元格的下拉菜单
function clearDropdowns(cellList, sheet) {
  cellList.forEach(cell => {
    sheet.getRange(cell).setDataValidation(null);
  });
}

关键调整提示 #

• 工作表名称：代码里的「价格追踪表」是假设的主表名，一定要改成你实际用的主工作表名称；「Modern Gene List」和「Ancient Gene List」如果和你的表名不一致也要同步修改。
• 单元格位置：如果breed选择不在D14，或者三个基因单元格不是B15-B17，直接修改对应的A1标记（比如把"B15"改成你的primary单元格位置）。
• 语法错误修复：你之前遇到的「unexpected identifier」是因为混用了Python语法，比如没加const/let声明变量、用了Python的字符串格式，上面的代码完全用标准JavaScript写法，不会有这类问题。

测试步骤 #

1. 打开你的表格，点「扩展程序」→「Apps Script」，替换原有代码，保存。
2. 返回表格：
   • 选D13的Modern，B15-B17会出现Modern基因的下拉；
   • 选D13的Ancient，先选D14的breed，B15-B17会切换成对应breed的基因下拉；
   • 选D13的其他值，下拉菜单会清空。

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