批量发送供应商余额确认邮件代码报错，寻求技术支持

问题场景 #

需实现从Google Sheet读取供应商数据，基于Google Doc模板生成余额确认函PDF，批量发送邮件给供应商，自行编写的Google Apps Script运行报错。

原代码 #

function sendBalanceConfirmationLetters() {
  // --- 配置项 ---
  const sheetName = "VendorBalances"; // Google Sheet工作表名称
  const subject = "Balance Confirmation Request";
  const senderEmail = Session.getActiveUser().getEmail(); // 发件人邮箱
  const templateDocId = "1ElTC5N5yeHWLkyH2C5c0bbjWR-sLA9q2Rn6vOPX3azM"; // Google Doc模板ID
  const sentLogSheetName = "SentLog"; // 发送记录工作表名称

  // --- 读取Sheet数据 ---
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const headers = data[0]; // 首行为表头

  // --- 匹配列索引 ---
  const vendorNameCol = headers.indexOf("Vendor Name");
  const vendorEmailCol = headers.indexOf("Vendor Email");
  const balanceCol = headers.indexOf("Balance");

  if (vendorNameCol === -1 || vendorEmailCol === -1 || balanceCol === -1) {
    Logger.log("工作表缺少必填列。");
    return;
  }

  // --- 创建或获取发送日志表 ---
  let sentLogSheet = ss.getSheetByName(sentLogSheetName);
  if (!sentLogSheet) {
    sentLogSheet = ss.insertSheet(sentLogSheetName);
    sentLogSheet.appendRow(["Timestamp", "Vendor Name", "Vendor Email", "Balance", "Status"]);
  }

  // --- 遍历处理每个供应商 ---
  for (let i = 1; i < data.length; i++) { // 跳过表头行
    const vendorName = data[i][vendorNameCol];
    const vendorEmail = data[i][vendorEmailCol];
    const balance = data[i][balanceCol];

    if (!vendorEmail || !vendorName || balance === undefined || balance === "") {
      Logger.log(`跳过第${i + 1}行：数据缺失。`);
      continue;
    }

    try {
      // --- 复制模板生成新文档 ---
      const doc = DocumentApp.openById(templateDocId).makeCopy();
      const body = doc.getBody();

      // --- 替换占位符 ---
      body.replaceText("{{Vendor Name}}", vendorName);
      body.replaceText("{{Balance}}", balance.toFixed(2)); // 格式化为两位小数

      // --- 转换为PDF ---
      const pdfBlob = doc.getAs("application/pdf");
      doc.removeFromFolder(DriveApp.getFileById(doc.getId()).getParents().next());// 删除临时文件
      DriveApp.getFileById(doc.getId()).setTrashed(true);// 移至回收站

      // --- 发送邮件 ---
      MailApp.sendEmail({
        to: vendorEmail,
        subject: subject,
        body: `Dear ${vendorName},\nPlease find attached the balance confirmation letter.`,
        attachments: [pdfBlob],
        name: "Balance Confirmation",
      });

      // --- 记录发送日志 ---
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, "Sent"]);

      Logger.log(`邮件已发送至 ${vendorEmail} (${vendorName})。`);

    } catch (e) {
      Logger.log(`发送邮件至 ${vendorEmail} (${vendorName}) 失败：${e}`);
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, `Error: ${e}`]);
    }
  }

  Logger.log("余额确认函发送流程完成。");
}

常见报错原因与修复方案 #

1. 权限不足 #

• 问题：脚本首次运行未获取完整权限，或权限范围不足（如无法访问Drive文件、发送邮件）。
• 修复：运行脚本时按提示完成授权，确保授予Google Drive、Gmail、Google Sheets的访问权限。

2. 模板占位符不匹配 #

• 问题：Doc模板中的占位符（如{{Vendor Name}}）与代码中replaceText的搜索文本不一致（大小写、空格、符号差异）。
• 修复：检查模板文档，确保占位符完全匹配代码中的字符串，无多余空格或格式差异。

3. Balance字段非数值类型 #

• 问题：Sheet中Balance列存储为文本格式，调用toFixed(2)时会抛出类型错误。
• 修复：将Balance转换为数值后再格式化：

  // 替换原Balance格式化代码
  const formattedBalance = typeof balance === 'number' ? balance.toFixed(2) : parseFloat(balance).toFixed(2);
  body.replaceText("{{Balance}}", formattedBalance);
  

4. 临时文件删除逻辑错误 #

• 问题：getParents().next()若模板副本存在于多个文件夹中会报错，且删除与移至回收站的逻辑重复。
• 修复：简化临时文件清理逻辑，直接移至回收站即可：

  // 替换原临时文件删除代码
  DriveApp.getFileById(doc.getId()).setTrashed(true);
  

5. 邮箱格式无效 #

• 问题：Sheet中供应商邮箱格式错误，导致MailApp.sendEmail失败。
• 修复：添加邮箱格式验证：

  // 在数据检查环节添加邮箱验证
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  if (!vendorEmail || !emailRegex.test(vendorEmail) || !vendorName || balance === undefined || balance === "") {
    Logger.log(`跳过第${i + 1}行：数据缺失或邮箱格式无效。`);
    continue;
  }
  

6. Google服务配额限制 #

• 问题：免费版Google Workspace账号每日邮件发送配额为100封，超出会触发报错。
• 修复：拆分发送批次，或升级账号提升配额。

修复后完整代码 #

function sendBalanceConfirmationLetters() {
  // --- 配置项 ---
  const sheetName = "VendorBalances"; // Google Sheet工作表名称
  const subject = "Balance Confirmation Request";
  const senderEmail = Session.getActiveUser().getEmail(); // 发件人邮箱
  const templateDocId = "1ElTC5N5yeHWLkyH2C5c0bbjWR-sLA9q2Rn6vOPX3azM"; // Google Doc模板ID
  const sentLogSheetName = "SentLog"; // 发送记录工作表名称
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/; // 邮箱格式正则

  // --- 读取Sheet数据 ---
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const headers = data[0]; // 首行为表头

  // --- 匹配列索引 ---
  const vendorNameCol = headers.indexOf("Vendor Name");
  const vendorEmailCol = headers.indexOf("Vendor Email");
  const balanceCol = headers.indexOf("Balance");

  if (vendorNameCol === -1 || vendorEmailCol === -1 || balanceCol === -1) {
    Logger.log("工作表缺少必填列。");
    return;
  }

  // --- 创建或获取发送日志表 ---
  let sentLogSheet = ss.getSheetByName(sentLogSheetName);
  if (!sentLogSheet) {
    sentLogSheet = ss.insertSheet(sentLogSheetName);
    sentLogSheet.appendRow(["Timestamp", "Vendor Name", "Vendor Email", "Balance", "Status"]);
  }

  // --- 遍历处理每个供应商 ---
  for (let i = 1; i < data.length; i++) { // 跳过表头行
    const vendorName = data[i][vendorNameCol];
    const vendorEmail = data[i][vendorEmailCol];
    const balance = data[i][balanceCol];

    // 数据有效性检查
    if (!vendorName || balance === undefined || balance === "" || !vendorEmail || !emailRegex.test(vendorEmail)) {
      Logger.log(`跳过第${i + 1}行：数据缺失或邮箱格式无效。`);
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, "Skipped: Invalid Data"]);
      continue;
    }

    try {
      // --- 复制模板生成新文档 ---
      const doc = DocumentApp.openById(templateDocId).makeCopy();
      const body = doc.getBody();

      // --- 替换占位符 ---
      const formattedBalance = typeof balance === 'number' ? balance.toFixed(2) : parseFloat(balance).toFixed(2);
      body.replaceText("{{Vendor Name}}", vendorName);
      body.replaceText("{{Balance}}", formattedBalance);

      // --- 转换为PDF ---
      const pdfBlob = doc.getAs("application/pdf");
      // 清理临时文件
      DriveApp.getFileById(doc.getId()).setTrashed(true);

      // --- 发送邮件 ---
      MailApp.sendEmail({
        to: vendorEmail,
        subject: subject,
        body: `Dear ${vendorName},\nPlease find attached the balance confirmation letter.`,
        attachments: [pdfBlob],
        name: "Balance Confirmation",
      });

      // --- 记录发送日志 ---
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, "Sent"]);
      Logger.log(`邮件已发送至 ${vendorEmail} (${vendorName})。`);

    } catch (e) {
      const errorMsg = `Error: ${e.message || e}`;
      Logger.log(`发送邮件至 ${vendorEmail} (${vendorName}) 失败：${errorMsg}`);
      sentLogSheet.appendRow([new Date(), vendorName, vendorEmail, balance, errorMsg]);
    }
  }

  Logger.log("余额确认函发送流程完成。");
}

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