使用AppScript的setValues()函数时如何保留边框、合并单元格与公式？

问题 #

我近期编写了一段AppScript代码，用于从一个区域获取数据并将其设置到另一个工作表中。代码功能正常，但设置后的数据因未保留表格边框、对齐方式及公式而难以阅读。Sheet1中的目标区域带有边框和公式，但运行上述saved脚本后，保存的区域既无边框也无公式，请问能否解决该问题？

现有脚本 #

function saved() 
{
  //Source link
  var ssraw = SpreadsheetApp.getActiveSpreadsheet();
  var sheetraw = ssraw.getSheetByName('Sheet1');
  var range = sheetraw.getRange('C3:I22');
  var data = range.getValues();
  var mat = sheetraw.getRange('H3').getValue();
   if (mat==="RR No.:") //TR
    {
    var reqsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("by rail");
    reqsheet.getRange(reqsheet.getLastRow()+2,1,20,7).clear();
    reqsheet.getRange(reqsheet.getLastRow()+2,1,20,7).setValues(data);
    }
  else if (mat==="TR No.:") //RR
    {
    var reqsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("by road");
    reqsheet.getRange(reqsheet.getLastRow()+2,1,20,7).clear();
    reqsheet.getRange(reqsheet.getLastRow()+2,1,20,7).setValues(data);
    }
}

解决方案 #

问题核心在于getValues()和setValues()仅处理单元格的数值/文本内容，不包含格式、边框和公式；同时clear()会清除目标区域的所有内容和格式。可通过以下调整解决：

1. 优化后的完整代码 #

function saved() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('Sheet1');
  const sourceRange = sourceSheet.getRange('C3:I22');
  const mat = sourceSheet.getRange('H3').getValue();
  
  let targetSheet;
  if (mat === "RR No.:") {
    targetSheet = ss.getSheetByName("by rail");
  } else if (mat === "TR No.:") {
    targetSheet = ss.getSheetByName("by road");
  } else {
    return; // 无匹配类型时直接退出
  }
  
  // 计算目标区域的起始行
  const targetStartRow = targetSheet.getLastRow() + 2;
  // 动态匹配源区域的行列数，避免硬编码出错
  const targetRange = targetSheet.getRange(targetStartRow, 1, sourceRange.getNumRows(), sourceRange.getNumColumns());
  
  // 仅清除目标区域的内容（保留原有格式，若需完全清空可改用clear()）
  targetRange.clearContent();
  // 复制源区域的所有内容、公式、格式、边框到目标区域
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_ALL, false);
}

2. 关键调整说明 #

• 替换值传递为copyTo()：使用PASTE_ALL参数可一次性复制公式、边框、对齐方式等所有单元格属性，替代仅传递内容的getValues()/setValues()；
• 修改清除方式：用clearContent()替代clear()，仅清除目标区域的文本/数值，保留原有格式（若需要完全清空目标区域格式，可保留clear()，后续copyTo()会覆盖）；
• 动态行列数：通过getNumRows()和getNumColumns()获取源区域的实际大小，避免硬编码20、7导致的适配问题；
• 简化代码结构：减少重复的SpreadsheetApp.getActiveSpreadsheet()调用，提升执行效率。

3. 按需复制特定属性（可选） #

如果不需要复制所有属性，可将PASTE_ALL替换为以下参数：

• PASTE_FORMULAS：仅复制单元格公式
• PASTE_FORMAT：仅复制格式（边框、对齐方式、字体等）
• PASTE_VALUES_AND_FORMAT：复制数值和格式，不包含公式

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