基于另一工作表的Google Apps Script下拉菜单及关联数据获取需求

核心需求 #

• 在「Expenses」工作表的下拉菜单选择名称后，相邻列自动填充「SharedList_Hidden」中对应的地址；理想状态下拉菜单同时显示名称+地址以区分重复名称
• 通过复选框控制下拉菜单的显示/隐藏，隐藏时允许输入列表外的名称和地址
• 确定处理下拉选择事件时使用OnEdit还是OnSelectionChange

现有代码问题分析 #

1. deleteDropdown函数中getrange拼写错误（应为getRange），且仅处理单个单元格B12，未覆盖目标列
2. createDropdown_FromRange函数错误操作了「SharedList_Hidden」工作表的单元格，而非「Expenses」的目标列，数据范围引用逻辑混乱
3. onEdit函数未判断触发事件的单元格是否为复选框所在位置，导致任何单元格编辑都会触发逻辑

完整解决方案代码 #

1. 初始化带名称+地址的下拉菜单 #

function initDropdown() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 源数据工作表
  const sourceSheet = ss.getSheetByName("SharedList_Hidden");
  // 目标工作表
  const targetSheet = ss.getSheetByName("Expenses");
  // 获取源数据（A列名称，B列地址），移除表头
  const sourceData = sourceSheet.getDataRange().getValues().slice(1);
  
  // 生成下拉选项：格式为「名称 - 地址」，同时构建名称-地址映射表
  const dropdownOptions = [];
  const nameAddressMap = new Map();
  sourceData.forEach(row => {
    const name = row[0];
    const address = row[1];
    if (name && address) {
      dropdownOptions.push(`${name} - ${address}`);
      nameAddressMap.set(name, address);
    }
  });

  // 创建数据验证规则
  const validationRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(dropdownOptions, false) // false表示严格限制为列表内选项
    .build();

  // 应用到Expenses的目标列（假设是C列从第18行开始）
  targetSheet.getRange("C18:C").setDataValidation(validationRule);

  // 存储名称-地址映射到脚本属性，供onEdit使用
  PropertiesService.getScriptProperties().setProperty("nameAddressMap", JSON.stringify(Array.from(nameAddressMap.entries())));
}

2. 复选框控制与联动填充逻辑 #

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

  // 假设复选框位于Expenses工作表的D12单元格，可根据实际修改
  const checkboxCell = "D12";
  if (activeSheet.getName() === "Expenses" && activeRange.getA1Notation() === checkboxCell) {
    const targetRange = activeSheet.getRange("C18:C");
    if (e.value === "TRUE") {
      // 移除数据验证，允许自由输入
      targetRange.clearDataValidations();
    } else {
      // 重新初始化下拉菜单
      initDropdown();
    }
  }

  // 处理下拉选择后的地址自动填充
  if (activeSheet.getName() === "Expenses" && activeRange.getColumn() === 3 && activeRange.getRow() >= 18) {
    const selectedValue = activeRange.getValue();
    if (selectedValue.includes(" - ")) {
      // 拆分出名称
      const name = selectedValue.split(" - ")[0];
      // 从脚本属性获取映射表
      const mapEntries = JSON.parse(PropertiesService.getScriptProperties().getProperty("nameAddressMap"));
      const nameAddressMap = new Map(mapEntries);
      // 填充到相邻列（假设是B列）
      activeSheet.getRange(activeRange.getRow(), 2).setValue(nameAddressMap.get(name) || "");
    }
  }
}

3. 辅助函数：手动清除下拉菜单 #

function clearDropdown() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName("Expenses");
  targetSheet.getRange("C18:C").clearDataValidations();
}

事件选择说明 #

使用onEdit而非OnSelectionChange：

• onEdit仅在单元格内容被修改时触发（包括下拉菜单选择），精准匹配需求场景，避免不必要的逻辑执行
• OnSelectionChange只要单元格被选中就触发，会导致频繁触发无意义的逻辑，且无法准确捕捉下拉选择的动作

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