Google Forms触发自动化失败：服务器错误排查求助

问题背景 #

我是一名新手，正在为团队创建自动化流程：当用户提交Google Forms表单后，通过Apps Script从关联的Google Sheets收集数据，将数据填充到Google Docs模板中，再将模板转换为PDF并发送至指定邮箱。

但执行时始终触发以下服务器错误，自动化流程完全无法运行，目标邮箱也未收到PDF文件：

Exception: We're sorry, a server error occurred. Please wait a bit and try again.
at onFormSubmit(Code:38:16)

已完成的设置 #

• 已配置正确的脚本属性
• 已创建并关联GCP项目编号

待排查代码 #

function onFormSubmit(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  const templateId = "(I removed this for privacy reasons)";
  const folderName = "(I removed this for privacy reasons)";
  const row = e.range.getRow(); // Correct way to get the row from the event
  const responses = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const data = {};
  headers.forEach((header, index) => {
    data[header] = responses[index] || "N/A"; // Fallback to "N/A" if no value
  });
  const placeholders = {
    "{{Started}}": data["Yesterday i started the day with ____ tasks in ASANA"],
    "{{Completed}}": data["Yesterday i completed ____ tasks in ASANA"],
    "{{Accomplished1}}": data["1st Task Completed"],
    "{{Accomplished2}}": data["2nd Task Completed"],
    "{{Accomplished3}}": data["3rd Task Completed"],
    "{{Accomplished4}}": data["4th Task Completed"],
    "{{Accomplished5}}": data["5th Task Completed"],
    "{{Accomplished6}}": data["6th Task Completed"],
    "{{Priorities1}}": data["1st Task Priority"],
    "{{Priorities2}}": data["2nd Task Priority"],
    "{{Priorities3}}": data["3rd Task Priority"],
    "{{Priorities4}}": data["4th Task Priority"],
    "{{Priorities5}}": data["5th Task Priority"],
    "{{Priorities6}}": data["6th Task Priority"],
    "{{Questions1}}": data["Please input all questions or problems encountered"],
  };
  const folders = DriveApp.getFoldersByName("(I removed this for privacy reasons)");
  if (!folders.hasNext()) {
    throw new Error(`Folder "(I removed this for privacy reasons)" not found.`);
  }
  const folder = folders.next();
  Logger.log("Creating document...");
  Logger.log("Folder found: " + folder.getName());
  const template = DriveApp.getFileById(templateId);
  const newDoc = template.makeCopy(`(I removed this for privacy reasons)_${data["Timestamp"] || "Untitled"}`, folder);
  const doc = DocumentApp.openById(newDoc.getId());
  const body = doc.getBody();
  for (const key in placeholders) {
    body.replaceText(key, placeholders[key]);
  }
  doc.saveAndClose();
  const pdf = DriveApp.getFileById(newDoc.getId()).getAs("application/pdf");
  const email = "(I removed this for privacy reasons)"; 
  GmailApp.sendEmail(email, "Your Daily Plan", "Attached is your Daily Plan.", {
    attachments: [pdf],
  });

  DriveApp.getFileById(newDoc.getId()).setTrashed(true);
}

可能的问题及修复方案 #

1. 文档保存延迟导致的服务器错误（对应错误行38） #

服务器错误常出现在文档保存操作未完成时就执行后续PDF生成步骤，添加强制延迟确保文档同步完成：

doc.saveAndClose();
Utilities.sleep(3000); // 等待3秒，给服务器足够时间处理文档状态

2. Drive资源重复获取触发限流 #

多次调用DriveApp.getFileById(newDoc.getId())会增加服务器请求负载，直接缓存文件对象减少重复请求：

const newDoc = template.makeCopy(`...`, folder);
const doc = DocumentApp.openById(newDoc.getId());
// ...执行文本替换...
doc.saveAndClose();
Utilities.sleep(3000);
// 直接使用已存在的newDoc对象生成PDF
const pdf = newDoc.getAs("application/pdf");
// ...发送邮件...
// 直接使用newDoc对象删除文件
newDoc.setTrashed(true);

3. 占位符正则匹配冲突 #

replaceText默认按正则表达式解析内容，若占位符包含下划线等特殊字符，会导致匹配失败，需转义特殊字符：

for (const key in placeholders) {
  // 转义正则特殊字符（.*+?^${}()|[\]\\）
  const escapedKey = key.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  body.replaceText(escapedKey, placeholders[key]);
}

4. 优化数据读取逻辑，减少Sheet IO #

表单提交事件e本身包含完整的提交数据，无需重复从Sheet读取整行，降低服务器IO压力：

// 替换原row、responses相关代码
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const data = {};
headers.forEach((header, index) => {
  data[header] = e.values[index] || "N/A"; // 直接从事件对象取提交数据
});

5. 增强日志定位问题 #

在关键步骤添加详细日志，方便排查具体失败环节：

Logger.log("Parsed form data: " + JSON.stringify(data));
Logger.log("Created document ID: " + newDoc.getId());
Logger.log("PDF file size: " + pdf.getBytes().length + " bytes");

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