Google Sheets Apps Script批量处理客户TDL与对应工作表的条件格式需求

原代码无效原因 #

1. forEach() 无返回值，导致clientsheetname为undefined，无法正确匹配对应客户工作表
2. 错误的嵌套循环：所有TDL表与所有客户表两两配对，而非一一对应
3. 未处理findNext()返回null的情况（当找不到匹配文本时会直接报错）
4. 重复调用getRange()，脚本执行效率低

修正后的脚本 #

function updateClientTaskBackgrounds() {
  // 获取当前电子表格（避免使用getActiveSheet，符合要求）
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 筛选所有名称包含"TDL"的工作表
  const tdlSheets = ss.getSheets().filter(sheet => sheet.getSheetName().includes('TDL'));

  // 遍历每个TDL工作表
  tdlSheets.forEach(tdlSheet => {
    // 提取对应客户表的名称：去掉TDL后缀（处理"客户名称 TDL"格式）
    const clientSheetName = tdlSheet.getSheetName().replace(/\s*TDL$/, '').trim();
    // 获取对应的客户工作表，不存在则跳过
    const clientSheet = ss.getSheetByName(clientSheetName);
    if (!clientSheet) return;

    // 一次性获取TDL表中所有任务区域的数据（A2:O11，对应5组3列数据）
    const allTasks = tdlSheet.getRange("A2:O11").getValues();

    // 遍历每一行数据
    allTasks.forEach(row => {
      // 处理5组任务（每组3列：[0,1,2], [3,4,5], [6,7,8], [9,10,11], [12,13,14]）
      for (let group = 0; group < 5; group++) {
        const startCol = group * 3;
        const isCompleted = row[startCol];
        const taskName = row[startCol + 2];

        // 仅当标记为'Y'且任务名称不为空时处理
        if (isCompleted === 'Y' && taskName) {
          // 在客户表中查找任务名称
          const foundCell = clientSheet.createTextFinder(taskName).findNext();
          if (foundCell) {
            // 设置找到单元格右侧一列的背景色为绿色
            foundCell.offset(0, 1).setBackground('#d9ead3');
          }
        }
      }
    });
  });
}

使用说明 #

1. 打开你的Google表格，点击「扩展程序」→「Apps脚本」
2. 替换原有代码为上述脚本
3. 保存脚本（命名为任意名称，比如ClientTaskUpdater）
4. 返回表格，插入一个绘图或按钮，将其分配给updateClientTaskBackgrounds函数
5. 点击按钮即可触发执行

关键优化点 #

• 严格一一配对TDL表与客户表，通过去除"TDL"后缀精准匹配
• 一次性获取所有任务数据，减少API调用次数，提升执行速度
• 增加空值与不存在工作表的判断，避免脚本报错
• 统一处理5组任务区域，无需重复编写循环逻辑

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