Google Apps Script：Sheet A非连续动态值匹配Sheet B的格式设置问题

原代码的核心问题 #

你的代码当前是按索引位置逐一比对两个范围的值，这种逻辑仅适用于两个范围行严格对应的连续场景，完全无法满足非连续值的检测需求——你需要的是判断HomeBox中的值是否存在于To-Do List的指定格式行中，而不是同一位置的值是否匹配。

修正后的代码 #

function homeBoxlianTasks() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const todoSheet = ss.getSheetByName("To-Do List");
  const homeBoxSheet = ss.getSheetByName("HomeBox");

  // 收集To-Do List中背景为#d9ead3的任务值，存入Set提升查找效率
  const todoRange = todoSheet.getRange("B6:B").getValues();
  const todoBackgrounds = todoSheet.getRange("B6:B").getBackgrounds();
  const completedTodos = new Set();
  
  for (let i = 0; i < todoRange.length; i++) {
    const value = todoRange[i][0];
    // 仅收集非空且背景符合条件的任务值
    if (value && todoBackgrounds[i][0] === '#d9ead3') {
      completedTodos.add(value.toString()); // 统一转字符串避免类型匹配问题
    }
  }

  // 处理HomeBox中的非连续目标范围（使用命名范围homeboxtodotasks）
  const homeBoxTargetRange = homeBoxSheet.getRange("homeboxtodotasks");
  const homeBoxValues = homeBoxTargetRange.getValues();
  const rowCount = homeBoxTargetRange.getNumRows();
  const colCount = homeBoxTargetRange.getNumCols();
  
  const backgrounds = [];
  const fontLines = [];

  // 遍历目标范围的每个单元格
  for (let i = 0; i < rowCount; i++) {
    const bgRow = [];
    const fontRow = [];
    for (let j = 0; j < colCount; j++) {
      const cellValue = homeBoxValues[i][j];
      if (cellValue && completedTodos.has(cellValue.toString())) {
        bgRow.push("#d9ead3");
        fontRow.push("line-through");
      } else {
        bgRow.push(null); // 保留原有背景
        fontRow.push(null); // 保留原有字体格式
      }
    }
    backgrounds.push(bgRow);
    fontLines.push(fontRow);
  }

  // 批量应用格式设置
  homeBoxTargetRange.setFontLines(fontLines).setBackgrounds(backgrounds);
}

关键改进说明 #

• 值存在性判断而非索引匹配：不再依赖两个范围的行索引对应，而是直接检测HomeBox的值是否存在于To-Do List的指定格式集合中，完美适配非连续场景
• 用Set优化查找效率：将已完成任务存入Set，查找时间复杂度从O(n)降至O(1)，数据量较大时性能提升明显
• 兼容多列非连续范围：原代码仅支持单列，修正后可处理任意行列数的非连续命名范围
• 空值过滤：避免空单元格被误处理，保证逻辑严谨性

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