Google App Script导出PDF数据不同步问题排查与解决

我的Google表格中最新的doc_no为TRY/2024/05/06/0707857126，表格已成功更新为该最新数据，但使用以下Google App Script代码导出PDF并发送邮件时，邮件附件中的PDF却显示旧的doc_no：TRY/2024/05/06/0707431726。请问为何会出现这种导出数据不同步的情况？该如何解决？

原代码 #

let url = "https://docs.google.com/spreadsheets/d/" + sheetExportId + "/export" +
    "?format=pdf" +
    "&size=A4" +
    "&portrait=true" +
    "&fitw=true" +
    "&sheetnames=false" +
    "&printtitle=false" +
    "&pagenumbers=false" +
    "&gridlines=false" +
    "&fzr=true" +
    "&gid=" + sheetId;
  let token = ScriptApp.getOAuthToken();
  let response = UrlFetchApp.fetch(url, {
    headers: {
      Authorization: "Bearer " + token
    }
  });
  let blob = response.getBlob().setName(pdfName);
  let folder = DriveApp.getFolderById(folderPdfId); // Change this to the desired folder

  // Check if a file with the same name already exists and delete it
  let existingFiles = folder.getFilesByName(blob.getName());
  if (existingFiles.hasNext()) {
    let existingFile = existingFiles.next();
    existingFile.setTrashed(true); // Move the existing file to trash
  }

  let pdfFile = folder.createFile(blob);

  console.log(`Sending email to ${emailRecipient}`);
  MailApp.sendEmail({
    to: emailRecipient,
    subject: emailSubject,
    body: emailBody,
    attachments: [pdfFile.getAs(MimeType.PDF)]
  });

问题原因 #

• Google Sheets云端缓存机制：直接通过URL导出PDF时，请求可能读取的是表格的缓存版本，而非实时更新的数据。尤其是当表格刚完成数据更新就触发导出操作时，云端缓存还未同步最新内容。
• 缺少强制刷新步骤：原代码中没有添加强制同步表格数据到云端的操作，导致导出请求获取到的是旧缓存数据。

解决方法 #

1. 强制刷新表格数据 #

在生成导出URL前，调用SpreadsheetApp.flush()方法，该方法会强制将所有待处理的表格操作提交到云端，确保缓存同步最新数据。

2. 添加缓存破击参数 #

在导出URL的末尾添加&cachebust= + 时间戳参数，让每次导出请求都是唯一的，避免读取旧缓存。

修改后的完整代码 #

// 先强制刷新表格数据，同步到云端
SpreadsheetApp.flush();

let url = "https://docs.google.com/spreadsheets/d/" + sheetExportId + "/export" +
    "?format=pdf" +
    "&size=A4" +
    "&portrait=true" +
    "&fitw=true" +
    "&sheetnames=false" +
    "&printtitle=false" +
    "&pagenumbers=false" +
    "&gridlines=false" +
    "&fzr=true" +
    "&gid=" + sheetId +
    "&cachebust=" + new Date().getTime(); // 添加缓存破击参数

let token = ScriptApp.getOAuthToken();
let response = UrlFetchApp.fetch(url, {
  headers: {
    Authorization: "Bearer " + token
  }
});
let blob = response.getBlob().setName(pdfName);
let folder = DriveApp.getFolderById(folderPdfId);

// 删除同名旧文件
let existingFiles = folder.getFilesByName(blob.getName());
if (existingFiles.hasNext()) {
  let existingFile = existingFiles.next();
  existingFile.setTrashed(true);
}

let pdfFile = folder.createFile(blob);

console.log(`Sending email to ${emailRecipient}`);
MailApp.sendEmail({
  to: emailRecipient,
  subject: emailSubject,
  body: emailBody,
  attachments: [pdfFile.getAs(MimeType.PDF)]
});

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