Google Sheets动态行分组脚本优化：支持表格变更自动更新

需求说明 #

实现以A列为空的行为分组标题，后续B列数据与标题行B列相同的行自动归为同一分组的功能，且分组需随筛选工作表（由主表动态生成）的更新同步调整。现有脚本无法自动同步变更，以下提供两种优化方案：

方案1：手动触发（清除旧分组后重建） #

每次执行时先清除所有现有行分组，再按照规则生成新分组，避免重复分组导致层级错误，适合手动更新场景。

方案2：自动触发（表格变更时同步更新） #

通过安装Google Apps脚本触发器，让工作表数据发生变更（如主表更新触发筛选表同步）时，自动执行分组逻辑，实现全自动化同步。

完整优化代码 #

function groupRowsStaffInWork() {
  const sheetName = 'INTEGRATIONS';
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  if (!sheet) {
    SpreadsheetApp.getUi().alert(`未找到名为${sheetName}的工作表`);
    return;
  }
  
  // 第一步：清除所有现有行分组
  clearAllRowGroups_(sheet);
  
  // 第二步：按规则重新分组
  groupRowsByCriteria_({
    sheet: sheet,
    startRow: 2, // 从第2行开始（跳过表头）
    collapse: true // 是否折叠分组
  });
}

/**
* 递归清除工作表中所有行分组
* @param {Sheet} sheet 目标工作表
*/
function clearAllRowGroups_(sheet) {
  const maxDepth = sheet.getMaxRowGroupDepth();
  // 从最深层级开始清除，确保所有分组都被移除
  for (let depth = maxDepth; depth > 0; depth--) {
    const rowGroups = sheet.getRowGroups();
    rowGroups.forEach(group => {
      if (group.getDepth() === depth) {
        group.remove();
      }
    });
  }
}

/**
* 按规则分组行：A列为空的行为分组标题，后续B列相同的行归为一组
* @param {Object} criteria 分组配置
* @return {Sheet} 处理后的工作表
*/
function groupRowsByCriteria_(criteria) {
  const sheet = criteria.sheet;
  const startRow = criteria.startRow;
  const rowEnd = getLastRow_(sheet);
  if (rowEnd < startRow) return sheet;
  
  // 获取A列和B列的显示值（从startRow到最后一行）
  const columnARange = sheet.getRange(startRow, 1, rowEnd - startRow + 1, 1);
  const columnBRange = sheet.getRange(startRow, 2, rowEnd - startRow + 1, 1);
  const columnA = columnARange.getDisplayValues().flat();
  const columnB = columnBRange.getDisplayValues().flat();
  
  const groups = [];
  let currentGroup = null;
  
  // 遍历每行，构建分组
  for (let i = 0; i < columnA.length; i++) {
    const currentRowNum = startRow + i;
    const aCellValue = columnA[i];
    const bCellValue = columnB[i];
    
    // 识别分组标题行：A列为空
    if (aCellValue.trim() === '') {
      // 若当前有未完成的分组，先存入队列
      if (currentGroup) {
        groups.push(currentGroup);
      }
      // 初始化新分组：记录标题行的B值，子行从标题行下一行开始
      currentGroup = {
        targetBValue: bCellValue,
        childRows: []
      };
    } 
    // 识别分组子行：属于当前分组且B值与标题行一致
    else if (currentGroup && bCellValue === currentGroup.targetBValue) {
      currentGroup.childRows.push(currentRowNum);
    }
    // 遇到不属于当前分组的行，结束当前分组
    else if (currentGroup) {
      groups.push(currentGroup);
      currentGroup = null;
    }
  }
  
  // 加入最后一个未完成的分组
  if (currentGroup) {
    groups.push(currentGroup);
  }
  
  // 对每个分组的子行执行分组操作
  groups.forEach(group => {
    if (group.childRows.length === 0) return;
    // 将子行按行号排序，再按连续行批量处理
    const sortedRows = group.childRows.sort((a, b) => a - b);
    const consecutiveRuns = getRunLengths_(sortedRows);
    
    consecutiveRuns.forEach(run => {
      const [firstRow, rowCount] = run;
      const targetRange = sheet.getRange(firstRow, 1, rowCount);
      targetRange.shiftRowGroupDepth(1);
      // 若配置为折叠，则自动折叠分组
      if (criteria.collapse) {
        targetRange.collapseGroups();
      }
    });
  });
  
  return sheet;
}

/**
* 计算连续行号的运行长度，返回[起始行号, 行数]的二维数组
* @param {Number[]} rowNumbers 待处理的行号数组
* @return {Number[][]} 连续行号分组结果
*/
function getRunLengths_(rowNumbers) {
  if (!rowNumbers.length) return [];
  
  return rowNumbers.reduce((result, currentRow, index) => {
    // 若当前行与前一行不连续，新建分组
    if (index === 0 || currentRow !== rowNumbers[index - 1] + 1) {
      result.push([currentRow]);
    }
    // 更新当前分组的行数
    const lastGroup = result[result.length - 1];
    lastGroup[1] = (lastGroup[1] || 0) + 1;
    return result;
  }, []);
}

/**
* 获取工作表最后有可见内容的行号
* @param {Sheet} sheet 目标工作表
* @param {Number} columnNum 可选：指定列号，默认检查所有列
* @return {Number} 最后有内容的行号
*/
function getLastRow_(sheet, columnNum) {
  const targetRange = columnNum 
    ? sheet.getRange(1, columnNum, sheet.getLastRow() || 1, 1)
    : sheet.getDataRange();
  
  const values = targetRange.getDisplayValues();
  let lastRowIndex = values.length - 1;
  
  // 从底部往上找第一个非空行
  while (lastRowIndex > 0 && !values[lastRowIndex].join('').trim()) {
    lastRowIndex--;
  }
  
  return lastRowIndex + 1;
}

/**
* 工作表变更触发函数：自动同步分组
* @param {Object} event 触发器事件对象
*/
function onSheetChange(event) {
  const targetSheetName = 'INTEGRATIONS';
  const changedSheet = event.range.getSheet();
  
  // 仅当变更发生在目标工作表时执行分组
  if (changedSheet.getName() === targetSheetName) {
    groupRowsStaffInWork();
  }
}

使用指南 #

手动执行 #

在Google Apps脚本编辑器中，选择 groupRowsStaffInWork 函数，点击运行按钮，即可清除旧分组并生成新分组。

自动执行 #

1. 打开脚本编辑器，点击菜单栏「编辑」>「当前项目的触发器」
2. 点击「添加触发器」按钮，配置如下：
   • 选择要运行的函数：onSheetChange
   • 选择部署类型：「头部署」
   • 选择事件源：「从电子表格」
   • 选择事件类型：「更改」
3. 保存触发器，之后目标工作表数据变更时会自动更新分组

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