Google Apps Script跨表匹配满足条件时自动设置复选框为true

需求说明 #

• 比对Validated、Completed两个工作表的E列关键字段
• 若匹配到相同记录，且Completed表对应行A列复选框为勾选状态，自动将Validated表对应行A列复选框设为勾选
• 工作表结构参考：

Validated工作表

Completed工作表

原代码存在的问题 #

• 错误使用getFormulas()读取E列匹配字段，E列为普通标识值并非公式，无法获取正确匹配内容
• 双层循环逻辑混乱，未设置E列值相等的判断条件，循环内随意递增行号，会跳过待校验行
• 未定位复选框所在的A列范围，没有写入勾选状态的对应操作
• 全列读取范围包含大量空行，产生无效循环拖慢运行速度

可直接运行的修正代码 #

function syncValidatedCheckbox() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 读取Completed表数据，生成匹配映射
  const completedSh = ss.getSheetByName('Completed');
  const completedLastRow = completedSh.getLastRow();
  // 从第2行开始，同时读取A列（复选框）、E列（匹配关键字）
  const completedData = completedSh.getRange(2, 1, completedLastRow - 1, 5).getValues();
  const matchMap = new Map();
  completedData.forEach(row => {
    const key = row[4]?.toString().trim();
    const checkedStatus = row[0] === true;
    if (key) matchMap.set(key, checkedStatus);
  });

  // 遍历Validated表匹配并更新复选框状态
  const validatedSh = ss.getSheetByName('Validated');
  const validatedLastRow = validatedSh.getLastRow();
  if (validatedLastRow < 2) return; // 无有效数据直接终止
  const validatedData = validatedSh.getRange(2, 1, validatedLastRow - 1, 5).getValues();
  const writeArr = [];
  validatedData.forEach(row => {
    const currentKey = row[4]?.toString().trim();
    // 匹配成功且Completed侧已勾选，则设为true，否则保留原状态
    if (currentKey && matchMap.has(currentKey) && matchMap.get(currentKey)) {
      writeArr.push([true]);
    } else {
      writeArr.push([row[0]]);
    }
  });

  // 批量写入A列复选框状态
  validatedSh.getRange(2, 1, writeArr.length, 1).setValues(writeArr);
}

使用说明 #

• 代码采用Map结构做匹配替代双层循环，千行级数据也能快速运行
• 自动过滤空行，仅处理有内容的有效数据行
• 匹配时自动去除E列值前后空格，避免多余空格导致匹配失败
• 未匹配成功、或Completed侧未勾选的行，会保留Validated表原有状态，不会误改数据
• 保存脚本后，直接运行syncValidatedCheckbox函数即可完成批量勾选

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