如何用Google Apps Script实现onEdit触发跨表匹配并回填单元格地址

完整实现代码 #

function onEdit(e) {
  // 触发条件校验：仅编辑Client表第2列（颜色列、跳过表头）时执行
  const editSheet = e.source.getActiveSheet();
  const editRange = e.range;
  if (editSheet.getName() !== 'Client' || editRange.columnStart !== 2 || editRange.rowStart === 1) return;

  // 初始化两个工作表对象
  const clientSheet = e.source.getSheetByName('Client');
  const colorSheet = e.source.getSheetByName('Color Description');

  // 批量读取Color Description表第1列颜色数据（跳过表头）
  const colorValues = colorSheet.getRange(2, 1, colorSheet.getLastRow() - 1, 1).getValues();
  // 构建颜色-地址映射字典，统一转小写适配大小写不一致的场景
  const colorMap = new Map();
  colorValues.forEach((row, index) => {
    const color = row[0].toString().trim().toLowerCase();
    const cellAddr = `A${index + 2}`;
    colorMap.set(color, cellAddr);
  });

  // 获取当前编辑行的颜色值并匹配
  const currentColor = editRange.getValue().toString().trim().toLowerCase();
  if (colorMap.has(currentColor)) {
    const fillValue = `Color Description ${colorMap.get(currentColor)}`;
    // 回填到当前行第3列
    clientSheet.getRange(editRange.rowStart, 3).setValue(fillValue);
  } else {
    // 无匹配时清空地址列
    clientSheet.getRange(editRange.rowStart, 3).clearContent();
  }
}

功能说明 #

• 触发过滤：通过前置条件判断，排除所有非目标区域编辑操作触发的无效执行，适配表格多操作都会触发事件对象的场景
• 大小写兼容：匹配前统一将颜色值转为小写，避免red和Red这类大小写差异导致的匹配失败问题
• 高效匹配：一次性读取所有颜色值构建映射字典，匹配效率远高于逐行遍历查询，适合数据量较大的场景
• 格式适配：回填的地址格式完全符合要求，示例输出为Color Description A2

使用方法 #

1. 打开目标Google Sheets表格，点击顶部菜单栏「扩展程序」→「Apps Script」
2. 删除编辑器默认的空白代码，粘贴上述代码后点击保存，命名项目即可
3. 无需额外配置触发器，onEdit是Google Sheets内置的简单触发函数，保存后直接生效

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