Google Apps Script问题：勾选Google表单响应表复选框后无法触发自定义PDF邮件发送

嘿，我仔细看了你的代码，发现几个关键问题导致脚本完全没反应，咱们一步步来修复：

1. 最致命的问题：rowData变量未定义 #

你在创建info对象时用到了rowData，但根本没读取当前编辑行的数据，代码执行到这里就会直接报错中断。在const entryRow = e.range.getRow();后面加上这行，获取当前行的所有数据：

const rowData = sheet.getRange(entryRow, 1, 1, 17).getValues()[0];

这里的1, 1, 17表示从第1列开始，取1行共17列的数据（对应你表单里的17个字段，从Timestamp到Comment），确保rowData能正确拿到每一列的值。

2. 重复定义sheet变量 #

你一开始已经通过e.source.getActiveSheet()获取了目标表单，后面又重新获取了一次，这属于重复定义变量，会导致语法错误。删掉这行冗余代码：

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");

3. 文件名中的键不匹配 #

在createPDF函数里，你用了info['Part Number or Serial Number'][0]作为文件名的一部分，但你的info对象里对应的键是'Part Number '（注意末尾有个空格），这会导致获取不到值。把文件名部分改成：

.setName(firstName + " " + lastName + " " + info['Part Number '][0]);

或者更规范一点，把info里的键改成不带空格的'PartNumber'，避免后续出错。

4. 简单触发器权限不足 #

默认的onEdit简单触发器没有权限访问DriveApp和GmailApp这类需要授权的服务，这会导致脚本悄悄失败。你需要改成可安装触发器：

• 打开脚本编辑器，点击左侧的「触发器」图标（时钟样式）
• 点击「添加触发器」，配置如下：
  • 选择函数：onEdit
  • 选择部署类型：「头部署」
  • 事件源：「电子表格」
  • 事件类型：「编辑时」
• 按照提示完成授权，这样脚本就有足够权限操作Drive和发送邮件了。

5. 复选框判断的兼容性优化 #

有些情况下e.range.isChecked()的判断可能不稳定，改成直接判断单元格值会更可靠：

if (e.range.getValue() === true) {

修复后的完整代码 #

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const checkboxColumn = 19; // Adjust if needed

  // Check if the edited sheet is "Form Responses 1" and the edited column is the checkbox column
  if (sheet.getName() === "Form Responses 1" && e.range.getColumn() === checkboxColumn) {

    // If the checkbox is checked, proceed with creating and sending the PDF
    if (e.range.getValue() === true) {
      const entryRow = e.range.getRow();
      // 获取当前行的所有数据
      const rowData = sheet.getRange(entryRow, 1, 1, 17).getValues()[0];

      const info = {
        'Timestamp': [rowData[1]],
        'Email Address': [rowData[2]],
        'What Happened ?': [rowData[3]],
        'Why is it a Problem ?': [rowData[4]],
        'Who Detected /  Who is Affected ?': [rowData[5]],
        'Where is the Problem ?': [rowData[6]],
        'When Detected ?': [rowData[7]],
        'How was it Detected': [rowData[8]],
        'How Many ?': [rowData[9]],
        'Part Number ': [rowData[10]],
        'Type of the component ': [rowData[11]],
        'Camera Stage': [rowData[12]],
        'Quantity to be blocked by PN': [rowData[13]],
        'Proposed disposition plan': [rowData[14]],
        'Due Date ': [rowData[15]],
        'Comment': [rowData[16]]
      };

      const pdfFile = createPDF(info);
      sheet.getRange(entryRow, 20).setValue(pdfFile.getUrl());
      sendEmail(info["Email Address"][0], pdfFile);
    }
  }
}

function createPDF(info) {
  const pdfFolder = DriveApp.getFolderById("1ejP3Dsd4y4TiIjtsJ3fHf5g_cmjvHDbo");
  const tempFolder = DriveApp.getFolderById("1cvIus2hbg25W3SgqeLP30Fc4661k_HR6");
  const templateDoc = DriveApp.getFileById("1sfZGb1jo0amoOFB1p_P-1UNyu_gKUDb8ReUnFNeQkBM");

  const newTempFile = templateDoc.makeCopy(tempFolder);
  const openDoc = DocumentApp.openById(newTempFile.getId());
  const body = openDoc.getBody();

  // Extract first and last name from email address
  const email = info['Email Address'][0] || "";
  const names = email.split(".");
  const firstName = names[0];
  const lastName = names.length > 1 ? names[1] : "";

  body.replaceText("{Date of Request}", info['Timestamp'][0] || "");
  body.replaceText("{Requestor}", info['Email Address'][0] || "");
  body.replaceText("{What}", info['What Happened ?'][0] || "");
  body.replaceText("{Why}", info['Why is it a Problem ?'][0] || "");
  body.replaceText("{Who}", info['Who Detected /  Who is Affected ?'][0] || "");
  body.replaceText("{Where}", info['Where is the Problem ?'][0] || "");
  body.replaceText("{When}", info['When Detected ?'][0] || "");
  body.replaceText("{How}", info['How was it Detected'][0] || "");
  body.replaceText("{Qty}", info['How Many ?'][0] || "");
  body.replaceText("{PN}", info['Part Number '][0] || "");
  body.replaceText("{Type}", info['Type of the component '][0] || "");
  body.replaceText("{Stage}", info['Camera Stage'][0] || "");
  body.replaceText("{Qty B}", info['Quantity to be blocked by PN'][0] || "");
  body.replaceText("{Plan}", info['Proposed disposition plan'][0] || "");
  body.replaceText("{Due}", info['Due Date '][0] || "");
  body.replaceText("{Comment}", info['Comment'][0] || "");

  openDoc.saveAndClose();

  const blobPDF = newTempFile.getAs(MimeType.PDF);
  // 修正文件名的键匹配问题
  const pdfFile = pdfFolder.createFile(blobPDF).setName(firstName + " " + lastName + " " + info['Part Number '][0]);
  newTempFile.setTrashed(true);
  return pdfFile;
}

function sendEmail(email, pdfFile) {
  const subjectUser = "Here's your QC930 Quarantine Entry Form";
  const bodyUser = "Hello,\n\nThank you for submitting a quarantine entry form request. \n\nPlease print and attach this form to all quarantined parts. \n\nMany thanks for your cooperation. \n\nKind regards, \nQuality Team";

  GmailApp.sendEmail(email, subjectUser, bodyUser, {
    attachments: [pdfFile],
    name: 'Quality Team'
  });
}

按照上面的步骤修改后，你再勾选复选框试试，应该就能正常生成PDF并发送邮件了。如果还有问题，可以在脚本编辑器的「查看」→「日志」里查看具体的报错信息，方便进一步排查~

备注：内容来源于stack exchange，提问作者Michelle P