Google Sheets合并单元格的背景检查与数据遍历问题求解

原代码的核心问题是未处理合并单元格场景，导致遍历到合并区域的非左上角单元格时获取空值，同时逐个调用getRange()效率极低。以下是优化后的完整解决方案：

优化后的代码 #

function collectDataUntilColor() {
  const Sheet_1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("你的工作表名称"); // 替换为实际工作表名
  const COLUMN_INDEX = 4; // D列的索引（A=1）
  const START_ROW = 5;
  const MAX_CHECK_ROW = Sheet_1.getLastRow();
  
  // 批量获取D列的背景色和值，减少API调用
  const bgColors = Sheet_1.getRange(START_ROW, COLUMN_INDEX, MAX_CHECK_ROW - START_ROW + 1, 1).getBackgrounds();
  const cellValues = Sheet_1.getRange(START_ROW, COLUMN_INDEX, MAX_CHECK_ROW - START_ROW + 1, 1).getValues();
  
  const Concat_rows = {};
  let lastProcessRow = MAX_CHECK_ROW;

  // 第一步：定位终止行（遇到背景色#c6d9f0时停止，不包含该行）
  for (let i = 0; i < bgColors.length; i++) {
    const currentRow = START_ROW + i;
    const cell = Sheet_1.getRange(currentRow, COLUMN_INDEX);

    if (cell.isPartOfMerge()) {
      const mergeRange = cell.getMergeRegion();
      // 只检查合并区域左上角单元格的背景色（合并区域颜色统一）
      if (mergeRange.getRow() === currentRow) {
        if (bgColors[i][0] === "#c6d9f0") {
          lastProcessRow = currentRow - 1;
          break;
        }
        // 跳过合并区域的剩余行
        i += mergeRange.getNumRows() - 1;
      }
    } else {
      if (bgColors[i][0] === "#c6d9f0") {
        lastProcessRow = currentRow - 1;
        break;
      }
    }
  }

  // 第二步：遍历收集数据，处理合并单元格
  let i = 0;
  while (i < bgColors.length) {
    const currentRow = START_ROW + i;
    if (currentRow > lastProcessRow) break;

    const cell = Sheet_1.getRange(currentRow, COLUMN_INDEX);
    let value, a1Notation;

    if (cell.isPartOfMerge()) {
      const mergeRange = cell.getMergeRegion();
      // 合并单元格仅左上角有有效值
      const topLeftCell = mergeRange.getCell(1, 1);
      value = topLeftCell.getValue();
      a1Notation = topLeftCell.getA1Notation();
      // 跳过合并区域的其他行
      i += mergeRange.getNumRows() - 1;
    } else {
      value = cellValues[i][0];
      a1Notation = cell.getA1Notation();
    }

    // 仅存储非空值
    if (value.toString().trim().length > 0) {
      Concat_rows[value] = a1Notation;
    }

    i++;
  }

  console.log(Concat_rows);
  return Concat_rows;
}

关键改进点 #

• 批量处理数据：一次性获取整列的背景色和值，避免频繁调用getRange()触发API限额，提升运行效率
• 合并单元格处理：
  • 用isPartOfMerge()判断单元格是否属于合并区域，getMergeRegion()获取合并范围
  • 合并区域仅取左上角单元格的值和A1符号，跳过区域内其他行，避免存储空值
• 终止逻辑修正：仅检查合并区域左上角单元格的背景色，确保终止行判断准确，不会提前或延后终止遍历

原代码问题说明 #

1. 效率低下：逐个单元格调用getRange()和getBackground()，容易触发Google Apps Script的API调用限额
2. 合并单元格处理缺失：合并区域的非左上角单元格返回空值，导致数据丢失；若终止色单元格在合并区域内，可能错误设置终止行
3. 终止逻辑不严谨：未考虑合并区域的背景色统一特性，可能重复判断或错误终止

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