使用Apps Script导出Google Sheet为PDF时图片消失的问题求助

问题描述 #

现有Google Apps Script脚本，可实现Google Sheet转PDF、发送邮件并保存到指定文件夹，原本能正常捕获包含图片的所有数据。但新增多行数据及图片后，导出的PDF中图片消失。单元格图片通过公式插入，脚本代码如下：

function emailSpreadsheetAsPDF() {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  const ss = SpreadsheetApp.openByUrl("xxxx");

  const value = ss.getSheetByName("xxx").getRange("K2").getValue();
  const email = value.toString();

  const subject = 'Audit Report';

  const body = "Here's a copy of the Audit Report";

  const url = 'xxxx';

  const exportOptions =
     'exportFormat=pdf&format=pdf' + // export as pdf
     '&size=letter' + // paper size letter / You can use A4 or legal
     '&portrait=true' + // orientation portal, use false for landscape
     '&fitw=true' + // fit to page width false, to get the actual size
     '&sheetnames=false&printtitle=false' + // hide optional headers and footers
     '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
     '&fzr=false' + // do not repeat row headers (frozen rows) on each page
     '&gid=1275739079'; // the sheet's Id. Change it to your sheet ID.

     var params = {method:"GET",headers:{"authorization":"Bearer "+ 
     
ScriptApp.getOAuthToken()}};

    // Generate the PDF file
    var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();


    // Send the PDF file as an attachement
    const docName = ss.getSheetByName("PDF").getRange("D6").getValue().toString()
    const docID = ss.getSheetByName("PDF").getRange("D11").getValue().toString() +".pdf"

    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
        fileName: docName + "_" + docID,
        content: response.getBytes(),
        mimeType: "application/pdf"
    }]
});

   const nameFile = ss.getSheetByName("PDF").getRange("D6").getValue().toString() + "_" + 
   ss.getSheetByName("PDF").getRange("D11").getValue().toString() +".pdf"
   DriveApp.getFolderById('xxxx').createFile(response.setName(nameFile));
}

修复方案 #

1. 指定完整导出范围 #

默认导出可能仅覆盖当前可见区域，新增内容后图片可能超出默认范围。在exportOptions中添加&range参数，指定表格的完整数据范围（替换为你实际的行列范围，比如A1:Z200）：

const exportOptions =
   'exportFormat=pdf&format=pdf' +
   '&size=letter' +
   '&portrait=true' +
   '&fitw=true' +
   '&sheetnames=false&printtitle=false' +
   '&pagenumbers=false&gridlines=false' +
   '&fzr=false' +
   '&gid=1275739079' +
   '&range=A1:Z200'; // 替换为实际数据的最后一行和列

2. 调整PDF渲染参数 #

fitw=true可能导致图片被压缩截断，改为fitw=false并提高缩放比例，确保图片完整渲染：

const exportOptions =
   'exportFormat=pdf&format=pdf' +
   '&size=letter' +
   '&portrait=true' +
   '&fitw=false' + // 关闭自适应宽度，保留原始尺寸
   '&scale=4' + // 提高渲染缩放比例，避免图片丢失
   '&sheetnames=false&printtitle=false' +
   '&pagenumbers=false&gridlines=false' +
   '&fzr=false' +
   '&gid=1275739079' +
   '&range=A1:Z200';

3. 添加渲染延迟 #

大量内容下，Sheets可能未完成图片渲染就被抓取，添加延迟确保渲染完成：

// 在生成PDF前添加3秒延迟，可根据内容量调整
Utilities.sleep(3000);
var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();

4. 检查图片嵌入方式 #

若图片通过=IMAGE()公式插入，尝试改为直接插入图片：

• 右键图片 → 设置单元格格式 → 选择“适应单元格大小”
• 确保图片所在行的高度足够容纳图片，避免被自动压缩

5. 重新授权脚本权限 #

脚本权限可能过期或不足，重新授权：
打开脚本编辑器 → 点击“运行” → 按照提示完成权限授权

修复后完整脚本示例 #

function emailSpreadsheetAsPDF() {
  const ss = SpreadsheetApp.openByUrl("xxxx");

  const email = ss.getSheetByName("xxx").getRange("K2").getValue().toString();
  const subject = 'Audit Report';
  const body = "Here's a copy of the Audit Report";
  const url = 'xxxx';

  // 调整后的导出参数，指定完整范围并优化渲染
  const exportOptions =
     'exportFormat=pdf&format=pdf' +
     '&size=letter' +
     '&portrait=true' +
     '&fitw=false' +
     '&scale=4' +
     '&sheetnames=false&printtitle=false' +
     '&pagenumbers=false&gridlines=false' +
     '&fzr=false' +
     '&gid=1275739079' +
     '&range=A1:Z200'; // 替换为你的实际数据范围

  const params = {
    method: "GET",
    headers: {"authorization": "Bearer " + ScriptApp.getOAuthToken()}
  };

  // 延迟等待渲染完成
  Utilities.sleep(3000);
  const response = UrlFetchApp.fetch(url + exportOptions, params).getBlob();

  // 发送邮件
  const docName = ss.getSheetByName("PDF").getRange("D6").getValue().toString();
  const docID = ss.getSheetByName("PDF").getRange("D11").getValue().toString() + ".pdf";
  GmailApp.sendEmail(email, subject, body, {
    htmlBody: body,
    attachments: [{
      fileName: `${docName}_${docID}`,
      content: response.getBytes(),
      mimeType: "application/pdf"
    }]
  });

  // 保存到指定文件夹
  const nameFile = `${docName}_${docID}`;
  DriveApp.getFolderById('xxxx').createFile(response.setName(nameFile));
}

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