如何更新Google Sheets下拉列表选中值？兼容IMPORTRANGE跨工作簿

一、原生功能实现：用Google Apps Script #

原生数据验证的下拉列表是静态绑定的，源数据修改后不会自动同步，得靠脚本监听变化来批量更新目标单元格。

1. 单工作簿场景脚本 #

操作步骤：

• 打开你的Google Sheets，点顶部「扩展程序」>「Apps Script」
• 清空默认代码，粘贴下面这段脚本：

function onEdit(e) {
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // 改成你的源数据所在工作表名
  const targetRange = sourceSheet.getRange("C:C"); // 改成下拉列表所在列
  const sourceRange = sourceSheet.getRange("A:A"); // 改成源数据所在列

  // 只处理源数据列的编辑操作
  if (e.range.getSheet().getName() !== sourceSheet.getName() || e.range.getColumn() !== sourceRange.getColumn()) return;

  const oldValue = e.oldValue;
  const newValue = e.value;

  if (!oldValue || !newValue) return;

  // 遍历目标列，把所有旧值替换成新值
  const targetValues = targetRange.getValues();
  for (let i = 0; i < targetValues.length; i++) {
    if (targetValues[i][0] === oldValue) {
      targetValues[i][0] = newValue;
    }
  }
  targetRange.setValues(targetValues);
}

• 保存脚本（随便起个名字，比如「SyncDropdown」），关掉脚本编辑器
• 测试：修改A列的源值，C列里之前选中的旧值会自动改成新值，不会弹出警告

2. 兼容IMPORTRANGE跨工作簿场景 #

如果源数据是用IMPORTRANGE从别的工作簿导过来的，onEdit触发器不会触发（因为外部数据更新不属于手动编辑），得换用下面两种触发器：

方法1：用onChange触发器

把脚本改成监听工作簿的所有变更事件，包括外部数据更新：

function onChange(e) {
  // 只捕获编辑或外部数据更新事件
  if (e.changeType !== "EDIT" && e.changeType !== "OTHER") return;

  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const targetRange = sourceSheet.getRange("C:C");
  const sourceRange = sourceSheet.getRange("A:A");

  // 获取版本历史来对比新旧源数据（得先启用版本历史功能）
  const versionHistory = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getRevisions();
  if (versionHistory.length < 2) return;

  const prevVersion = versionHistory[versionHistory.length - 2].getAs("text/csv").getDataAsString();
  const currSourceValues = sourceRange.getValues().map(row => row[0]);

  // 解析旧版本的A列数据
  const prevSourceValues = prevVersion.split("\n").map(row => row.split(",")[0]);

  // 找出所有被修改的源值对
  const valueMap = {};
  for (let i = 0; i < currSourceValues.length; i++) {
    if (prevSourceValues[i] && currSourceValues[i] && prevSourceValues[i] !== currSourceValues[i]) {
      valueMap[prevSourceValues[i]] = currSourceValues[i];
    }
  }

  // 更新目标列的对应值
  if (Object.keys(valueMap).length > 0) {
    const targetValues = targetRange.getValues();
    for (let i = 0; i < targetValues.length; i++) {
      const oldVal = targetValues[i][0];
      if (valueMap[oldVal]) {
        targetValues[i][0] = valueMap[oldVal];
      }
    }
    targetRange.setValues(targetValues);
  }
}

• 设置触发器：在Apps Script编辑器左侧点「触发器」>「添加触发器」，选择函数onChange，事件类型选「从电子表格」>「变更」，保存并完成授权

方法2：时间驱动触发器（备选）

如果onChange触发不稳定，就设置定时检查源数据变化：

function syncDropdownValues() {
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const targetRange = sourceSheet.getRange("C:C");
  const sourceRange = sourceSheet.getRange("A:A");

  // 读取上次保存的源数据状态
  const scriptProps = PropertiesService.getScriptProperties();
  const lastSourceState = scriptProps.getProperty("lastSourceState");
  const currSourceState = JSON.stringify(sourceRange.getValues());

  // 源数据没变化就直接退出
  if (lastSourceState === currSourceState) return;

  // 解析新旧源数据，找出修改的键值对
  const lastValues = JSON.parse(lastSourceState);
  const currValues = JSON.parse(currSourceState);
  const valueMap = {};

  for (let i = 0; i < currValues.length; i++) {
    if (lastValues[i] && currValues[i] && lastValues[i][0] !== currValues[i][0]) {
      valueMap[lastValues[i][0]] = currValues[i][0];
    }
  }

  // 更新目标列
  if (Object.keys(valueMap).length > 0) {
    const targetValues = targetRange.getValues();
    for (let i = 0; i < targetValues.length; i++) {
      const oldVal = targetValues[i][0];
      if (valueMap[oldVal]) {
        targetValues[i][0] = valueMap[oldVal];
      }
    }
    targetRange.setValues(targetValues);
  }

  // 保存当前源数据状态，下次对比用
  scriptProps.setProperty("lastSourceState", currSourceState);
}

• 设置触发器：添加时间驱动触发器，比如每5分钟执行一次syncDropdownValues，适合数据更新频率不高的场景

二、第三方插件（不用写脚本） #

如果不想折腾代码，可以试试Google Workspace Marketplace里的这些工具：

• Power Tools: 自带批量查找替换的监控功能，能设置监听特定单元格变更，自动替换目标区域的旧值
• Sheetgo: 不仅支持跨工作簿数据同步，还能配置数据更新后的自动映射规则，把源数据的修改同步到下拉列表选中值里

注意：用第三方插件时，要确认它支持监听IMPORTRANGE导入数据的变更，授权时留意权限范围，别给不必要的权限。

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