Google Apps Script复制值与格式：解决PDF邮件REF错误问题

这个问题我之前也踩过坑——默认复制整个工作表会把公式也一并带过去，一旦临时表和源表的引用关系断裂（比如源表结构变动、临时表位置变化），生成的PDF里就会冒出讨厌的#REF!错误。下面是我验证过的靠谱解决方案，确保只复制值和格式，彻底避开公式带来的麻烦：

核心思路 #

别直接复制整个工作表，而是拆分两步操作：先把源表的视觉格式（列宽、行高、单元格样式等）复制到新表，再把源表的静态值（不带公式的纯文本/数值）同步过去。这样新表里完全没有公式，生成PDF时自然不会出现引用错误。

完整代码示例 #

下面是包含创建临时表、复制值和格式、生成PDF、发送邮件的完整脚本，你可以直接替换参数使用：

function createPDFAndSendEmail() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheetName = "你的源工作表名称"; // 替换成你的源表名称
  const tempSheetName = "临时PDF生成表";
  const recipient = "收件人邮箱@xxx.com"; // 替换成目标收件人邮箱
  const emailSubject = "月度报表PDF";
  const emailBody = "这是你需要的报表PDF，已去除公式避免#REF!错误。";

  // 清理旧的临时表（如果存在），避免同名冲突
  const existingTempSheet = ss.getSheetByName(tempSheetName);
  if (existingTempSheet) {
    ss.deleteSheet(existingTempSheet);
  }

  // 创建新的临时工作表
  const tempSheet = ss.insertSheet(tempSheetName);
  const sourceSheet = ss.getSheetByName(sourceSheetName);

  // 获取源表的全部数据范围
  const sourceRange = sourceSheet.getDataRange();
  const targetRange = tempSheet.getRange(1, 1, sourceRange.getNumRows(), sourceRange.getNumColumns());

  // 第一步：复制格式（列宽、行高、单元格样式、对齐方式等）
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  // 第二步：复制静态值（仅纯文本/数值，不带任何公式）
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  // 可选：同步源表的冻结行/列，让PDF视图和源表保持一致
  tempSheet.setFrozenRows(sourceSheet.getFrozenRows());
  tempSheet.setFrozenColumns(sourceSheet.getFrozenColumns());

  // 生成PDF文件
  const pdfBlob = ss.getBlob(tempSheet);

  // 发送邮件并附上PDF附件
  MailApp.sendEmail({
    to: recipient,
    subject: emailSubject,
    body: emailBody,
    attachments: [pdfBlob.setName("月度报表.pdf")] // 自定义PDF文件名
  });

  // 清理临时工作表，保持表格整洁
  ss.deleteSheet(tempSheet);
}

关键细节说明 #

• 为什么分两步复制？：直接用sheet.copyTo()会复制所有内容（公式、注释、数据验证等），而我们只需要值和格式。分两步能精准控制复制内容，彻底隔离公式。
• 格式复制的完整性：PASTE_FORMAT会同步源表所有视觉相关的格式，确保PDF和源表的外观完全一致。
• 临时表的生命周期：每次运行前删除旧临时表，避免冲突；生成PDF后立即删除，不会留下冗余数据。

额外优化建议 #

如果你的源表有合并单元格、条件格式或者数据验证需要保留，可以在复制步骤中添加对应的粘贴类型：

• 保留条件格式：sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_CONDITIONAL_FORMATTING, false);
• 保留数据验证：sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);

只要不使用PASTE_NORMAL或PASTE_FORMULA这类会复制公式的粘贴类型，就不会出现#REF!错误。

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