Google Sheets中借助Google Script实现单元格合并与公式复制的咨询

功能说明 #

• 合并同步功能：以标注credits的单元格为固定定位锚点，自动识别绿色列的所有合并单元格范围，对应将同范围的蓝色列执行合并操作，支持任意可变合并行数，无需固定合并数量
• 自动填充公式功能：绑定编辑触发事件，当蓝色列任意单元格有内容填充时，自动将绿色列对应行的公式按相对引用规则填充到目标位置

完整脚本代码 #

// 配置参数，请根据你的表格实际情况修改
const CONFIG = {
  // 蓝色列相对绿色列的偏移量：如果蓝色列在绿色列右侧第1列就填1，右侧第2列填2，以此类推
  blueColumnOffset: 1,
  // 绿色单元格匹配的十六进制颜色值，可右键绿色单元格查看格式获取色值
  greenCellColor: "#00ff00",
  // 蓝色单元格匹配的十六进制颜色值
  blueCellColor: "#0000ff"
}

// 功能1：同步绿色列合并规则到蓝色列
function syncBlueColumnMerge() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  // 定位标注credits的单元格
  const creditsRange = sheet.createTextFinder("credits").matchEntireCell(true).findNext();
  if (!creditsRange) return SpreadsheetApp.getUi().alert("未找到标注为credits的单元格");
  const greenColIndex = creditsRange.getColumn();
  const blueColIndex = greenColIndex + CONFIG.blueColumnOffset;
  const lastRow = sheet.getLastRow();
  
  // 遍历绿色列获取所有合并单元格范围
  let row = creditsRange.getRow() + 1;
  while (row <= lastRow) {
    const currentCell = sheet.getRange(row, greenColIndex);
    if (currentCell.isPartOfMerge()) {
      const mergedRange = currentCell.getMergedRanges()[0];
      const mergeStartRow = mergedRange.getRow();
      const mergeEndRow = mergedRange.getLastRow();
      // 对应合并蓝色列同范围单元格
      sheet.getRange(mergeStartRow, blueColIndex, mergeEndRow - mergeStartRow + 1, 1).mergeVertically();
      // 跳转到合并范围下一行继续遍历
      row = mergeEndRow + 1;
    } else {
      row ++;
    }
  }
  SpreadsheetApp.getUi().alert("蓝色列合并同步完成");
}

// 功能2：蓝色列有内容输入时自动填充绿色列公式
function autoFillFormulaOnBlueEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  // 定位标注credits的单元格
  const creditsRange = sheet.createTextFinder("credits").matchEntireCell(true).findNext();
  if (!creditsRange) return;
  const greenColIndex = creditsRange.getColumn();
  const blueColIndex = greenColIndex + CONFIG.blueColumnOffset;
  
  // 校验编辑的单元格是否属于蓝色列且有内容
  if (range.getColumn() !== blueColIndex || range.isBlank()) return;
  const editRow = range.getRow();
  const greenTargetCell = sheet.getRange(editRow, greenColIndex);
  // 跳过已有内容的绿色单元格
  if (!greenTargetCell.isBlank()) return;
  // 获取上一行绿色单元格的公式按相对引用填充
  const upperGreenCell = sheet.getRange(editRow - 1, greenColIndex);
  if (upperGreenCell.getFormula()) {
    upperGreenCell.autoFill(sheet.getRange(editRow - 1, greenColIndex, 2, 1), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  }
}

部署使用步骤 #

1. 打开你要操作的Google表格，点击顶部菜单栏「扩展程序」>「Apps Script」进入脚本编辑器
2. 删除编辑器内默认的myFunction代码，将上述完整代码粘贴进去
3. 先修改代码开头CONFIG里的参数，匹配你表格的实际列偏移和单元格色值，保存项目
4. 首次使用先选中syncBlueColumnMerge函数点击运行，按照提示授权所需的表格访问权限，执行完成即可完成蓝色列合并同步
5. 配置自动填充公式的触发器：点击脚本编辑器左侧闹钟图标「触发器」>「添加触发器」，按如下配置：
   • 选择要运行的函数：autoFillFormulaOnBlueEdit
   • 选择事件源：「电子表格」
   • 选择事件类型：「编辑」
   • 保存触发器即可生效

注意事项 #

• 如果需要调整合并规则，修改绿色列合并后重新运行syncBlueColumnMerge函数即可更新蓝色列合并状态
• 公式填充默认按相对引用规则生效，如果需要绝对引用可以在公式对应的行/列前加$符号调整

内容的提问来源于stack exchange，提问作者George C Matthias III