Google Apps Script：带批注行跨表迁移的格式兼容问题求助

需求说明 #

• 电子表格包含两个工作表：A PLANIFIER 和 MASTER
• 需要将A PLANIFIER中B列值为OUI的行，按指定列映射关系迁移到MASTER：
  • A PLANIFIER的B、D、F-U、V-AB、AE列 → MASTER的H、J、L-AA、AM-AS、BL列

遇到的问题 #

• 使用moveTo()：可迁移批注，但会覆盖目标单元格原有格式，破坏条件格式规则的显示
• 使用copyTo()：能保留目标格式，但无法迁移批注；尝试用setComment()和getComment()解决未成功

现有脚本 #

原脚本（基于copyTo） #

function transferData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("A PLANIFIER");
  var targetSheet = ss.getSheetByName("MASTER");
  // 获取B列为'OUI'的行号数组
  var ouiData = sourceSheet.getDataRange().getValues().map((x, i) => [i + 1, x[1]]).filter(x => x[1] == 'OUI');
  // 获取目标工作表的下一个空行（基于H列）
  var targetSheetNextRow = targetSheet.getRange(1, 8).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;

  ouiData.forEach(y => {
    var row = y[0];

    sourceSheet.getRange("B" + row).copyTo(targetSheet.getRange("H" + targetSheetNextRow), {contentsOnly:true});
    sourceSheet.getRange("D" + row).copyTo(targetSheet.getRange("J" + targetSheetNextRow), {contentsOnly:true});
    sourceSheet.getRange("F" + row + ":U" + row).copyTo(targetSheet.getRange("L" + targetSheetNextRow + ":AA" + targetSheetNextRow), {contentsOnly:true});
    sourceSheet.getRange("V" + row + ":AB" + row).copyTo(targetSheet.getRange("AM" + targetSheetNextRow + ":AS" + targetSheetNextRow), {contentsOnly:true});
    sourceSheet.getRange("AE" + row).copyTo(targetSheet.getRange("BL" + targetSheetNextRow), {contentsOnly:true});

    // 清除源行已迁移内容
    sourceSheet.getRange("B" + row + ":AE" + row).clearContent();

    // 更新目标下一行号
    targetSheetNextRow = targetSheetNextRow + 1;
  })

  sortM();
  sortAP();
}

尝试的解决方案脚本（基于moveTo） #

function transferData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("A PLANIFIER");
  var targetSheet = ss.getSheetByName("MASTER");
  // 获取B列为'OUI'的行号数组
  var ouiData = sourceSheet.getDataRange().getValues().map((x, i) => [i + 1, x[1]]).filter(x => x[1] == 'OUI');
  // 获取目标工作表的下一个空行（基于H列）
  var targetSheetNextRow = targetSheet.getRange(1, 8).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;

  ouiData.forEach(y => {
    var row = y[0];

    sourceSheet.getRange("B" + row).moveTo(targetSheet.getRange("H" + targetSheetNextRow));
    sourceSheet.getRange("D" + row).moveTo(targetSheet.getRange("J" + targetSheetNextRow));
    sourceSheet.getRange("F" + row + ":U" + row).moveTo(targetSheet.getRange("L" + targetSheetNextRow + ":AA" + targetSheetNextRow));
    sourceSheet.getRange("V" + row + ":AB" + row).moveTo(targetSheet.getRange("AM" + targetSheetNextRow + ":AS" + targetSheetNextRow));
    sourceSheet.getRange("AE" + row).moveTo(targetSheet.getRange("BL" + targetSheetNextRow));

    // 清除目标行格式
    targetSheet.getRange("H" + targetSheetNextRow + ":BL" + targetSheetNextRow).clearFormat();

    // 更新目标下一行号
    targetSheetNextRow = targetSheetNextRow + 1;

    // 恢复目标行格式和条件格式
    var rng = targetSheet.getRange("A7:CL7")
    rng.copyTo(targetSheet.getRange("A6:CL200" + row), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
    rng.copyTo(targetSheet.getRange("A6:CL200" + row), SpreadsheetApp.CopyPasteType.PASTE_CONDITIONAL_FORMATTING, false);

    // 恢复源行格式和数据验证
    var rng = sourceSheet.getRange("A50:AG50")
    rng.copyTo(sourceSheet.getRange("A6:AG50" + row), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
    rng.copyTo(sourceSheet.getRange("A6:AG50" + row), SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);
  })

  sortM();
  sortAP();
}

修正后的解决方案脚本 #

function transferData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("A PLANIFIER");
  var targetSheet = ss.getSheetByName("MASTER");
  
  // 获取所有B列为'OUI'的行号
  var ouiData = sourceSheet.getDataRange().getValues().map((row, idx) => [idx + 1, row[1]]).filter(item => item[1] === 'OUI');
  if (ouiData.length === 0) return; // 无符合条件的行直接退出
  
  // 获取目标工作表下一个空行（基于H列）
  var targetNextRow = targetSheet.getRange(1, 8).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;
  
  // 统一管理列映射关系，便于维护
  var columnMappings = [
    {source: "B", target: "H"},
    {source: "D", target: "J"},
    {source: "F:U", target: "L:AA"},
    {source: "V:AB", target: "AM:AS"},
    {source: "AE", target: "BL"}
  ];
  
  ouiData.forEach(item => {
    var sourceRow = item[0];
    var currentTargetRow = targetNextRow;
    
    // 遍历映射规则，复制内容+迁移批注
    columnMappings.forEach(mapping => {
      var sourceRange = sourceSheet.getRange(`${mapping.source}${sourceRow}`);
      var targetRange = targetSheet.getRange(`${mapping.target}${currentTargetRow}`);
      
      // 仅复制内容，保留目标单元格原有格式和条件格式
      sourceRange.copyTo(targetRange, {contentsOnly: true});
      
      // 逐个迁移单元格批注
      sourceRange.getValues().forEach((_, colIdx) => {
        var sourceCell = sourceRange.getCell(1, colIdx + 1);
        var targetCell = targetRange.getCell(1, colIdx + 1);
        var comment = sourceCell.getComment();
        
        if (comment) {
          targetCell.setComment(comment);
        }
      });
    });
    
    // 清除源行的内容和批注
    sourceSheet.getRange(`B${sourceRow}:AE${sourceRow}`).clearContent().clearComments();
    
    targetNextRow++;
  });
  
  sortM();
  sortAP();
}

方案说明 #

1. 列映射统一管理：将列对应关系封装为数组，减少重复代码，便于后续修改
2. 内容与批注分离处理：
   • 用copyTo({contentsOnly: true})复制内容，完全保留目标单元格的原有格式和条件格式（条件格式会自动应用到新行，只要新行在规则范围内）
   • 单独遍历源单元格，提取批注后设置到目标单元格，解决copyTo无法迁移批注的问题
3. 源数据清理：清除源行的内容和批注，避免重复迁移

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