Google Sheets中修改命名区域单元格值后，如何自动同步下拉列表？

当你在Google Sheets中使用命名区域作为下拉列表的数据源时，修改命名区域内的单元格值后，已选中该值的下拉单元格不会自动同步更新，只会显示无效值警告。目前没有原生配置选项能直接实现自动同步，需要借助Google Apps Script来解决这个问题。

实现步骤 #

1. 打开目标Google Sheet，点击顶部菜单栏的「扩展程序」→「Apps Script」，进入脚本编辑器。
2. 替换默认代码为以下脚本：

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const value = e.value;
  const oldValue = e.oldValue;
  
  if (!oldValue || value === oldValue) return;
  
  const namedRanges = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges();
  
  for (const namedRange of namedRanges) {
    const rangeList = namedRange.getRangeList().getRanges();
    for (const nrRange of rangeList) {
      if (nrRange.getSheet().getName() === sheet.getName() && nrRange.contains(range)) {
        const dataValidationRules = findDataValidationByNamedRange(namedRange.getName());
        dataValidationRules.forEach(ruleRange => {
          const cellValues = ruleRange.getValues();
          const updatedValues = cellValues.map(row => 
            row.map(cell => cell === oldValue ? value : cell)
          );
          ruleRange.setValues(updatedValues);
        });
        return;
      }
    }
  }
}

function findDataValidationByNamedRange(namedRangeName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = ss.getSheets();
  const resultRanges = [];
  
  for (const sheet of allSheets) {
    const dataValidationRanges = sheet.getDataValidations();
    if (!dataValidationRanges) continue;
    
    for (let row = 0; row < dataValidationRanges.length; row++) {
      for (let col = 0; col < dataValidationRanges[row].length; col++) {
        const rule = dataValidationRanges[row][col];
        if (rule && rule.getCriteriaType() === SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
          const criteriaRange = rule.getCriteriaValues()[0];
          if (criteriaRange.getNamedRange()?.getName() === namedRangeName) {
            resultRanges.push(sheet.getRange(row + 1, col + 1));
          }
        }
      }
    }
  }
  return resultRanges;
}

3. 保存脚本，可命名为「SyncDropdownWithNamedRange」。
4. 关闭脚本编辑器，回到Sheet测试：修改命名区域内的某个值（比如把Sheet1中的“A”改成“AA”），所有引用该命名区域的下拉单元格中原来的“A”会自动同步为“AA”。

脚本说明 #

• onEdit是Google Sheets的简单触发器，用户编辑单元格时自动触发。
• 脚本会检测编辑的单元格是否属于某个命名区域，若是则查找所有引用该区域的下拉验证单元格，将旧值批量替换为新值。
• 注意：简单触发器有30秒执行时间限制，若Sheet数据量极大，可改用可安装触发器并优化代码，普通场景下简单触发器足够使用。

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