Google Apps Script 技术指导请求：实现动态邮件主题、正文及PDF文件名配置

我帮你调整了代码，完美实现你要的三个需求，下面是修改后的完整脚本，还有对应的改动说明：

function afterFormSubmit(e) { 
  const info = e.namedValues; 
  // 提前提取核心变量，方便后续复用
  const outletName = info['Name of Outlet'][0];
  const reportDate = info['Date of Report'][0];
  
  const pdfFile = createPDF(info, outletName, reportDate); 
  const entryRow = e.range.getRow(); 
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Final Responses"); 
  ws.getRange(entryRow, 64).setValue(pdfFile.getUrl()); 
  ws.getRange(entryRow, 65).setValue(pdfFile.getName()); 
  sendEmail(e.namedValues['Email address'][0], pdfFile, outletName, reportDate); 
} 

function sendEmail(email, pdfFile, outletName, reportDate){ 
  // 动态拼接邮件主题
  const subject = `Daily Transaction Report - ${outletName} - ${reportDate}`;
  // 动态生成邮件正文
  const body = `Sir, Please find the attached Daily Transaction Report for the outlet ${outletName} on ${reportDate}.`;
  
  GmailApp.sendEmail(email, subject, body, { 
    attachments: [pdfFile], 
    name: 'KCCO - Automation Team' 
  }); 
} 

function createPDF(info, outletName, reportDate) { 
  const pdfFolder = DriveApp.getFolderById("1ME3EqHOZzBZDBLNPHQxLjf5tWiQpmE8u"); 
  const tempFolder = DriveApp.getFolderById("1ebglel6vwMGXByeG4wHBBvJu9u0zJeMJ"); 
  const templateDoc = DriveApp.getFileById("1kyFimRMdHQZV85F5P6JyCHv1DQ3KJFv1hx5o7lIcNZo"); 
  const newTempFile = templateDoc.makeCopy(tempFolder); 
  const openDoc = DocumentApp.openById(newTempFile.getId()); 
  const body = openDoc.getBody(); 

  // 以下替换文本逻辑保持原有功能不变
  body.replaceText("{Name of Person}", info['Name of Person'][0]); 
  body.replaceText("{Date of Report}", info['Date of Report'][0]); 
  body.replaceText("{Name of Outlet}", info['Name of Outlet'][0]); 
  body.replaceText("{Opening Petty Cash Balance}", info['Opening Petty Cash Balance'][0]); 
  body.replaceText("{Total Petty Cash Received}", info['Total Petty Cash Received'][0]); 
  body.replaceText("{Closing Petty Cash Balance}", info['Closing Petty Cash Balance'][0]); 
  body.replaceText("{Total Petty Cash Paid}", info['Total Petty Cash Paid'][0]); 
  body.replaceText("{Available Float Cash Balance}", info['Available Float Cash Balance'][0]); 
  body.replaceText("{Milk for Outlet}", info['Milk for Outlet'][0]); 
  body.replaceText("{Cold Drink And Ice Cube Purchased}", info['Cold Drink And Ice Cube Purchased'][0]); 
  body.replaceText("{Goods Purchased for Outlet}", info['Goods Purchased for Outlet'][0]); 
  body.replaceText("{Staff Milk/Toast etc.}", info['Staff Milk/Toast etc.'][0]); 
  body.replaceText("{Water Tank Charges}", info['Water Tank Charges'][0]); 
  body.replaceText("{Repair And Maintenance}", info['Repair And Maintenance'][0]); 
  body.replaceText("{Petrol Expenses}", info['Petrol Expenses'][0]); 
  body.replaceText("{Freight Paid}", info['Freight Paid'][0]); 
  body.replaceText("{Purchase of Soya Chaap}", info['Purchase of Soya Chaap'][0]); 
  body.replaceText("{Purchase of Rumali Roti}", info['Purchase of Rumali Roti'][0]); 
  body.replaceText("{Purchase of Egg Tray}", info['Purchase of Egg Tray'][0]); 
  body.replaceText("{Other Expenses}", info['Other Expenses'][0]); 
  body.replaceText("{Please Specify Other Expenses}", info['Please Specify Other Expenses'][0]); 
  body.replaceText("{Total Tip Amount PAYTM & CARD}", info['Total Tip Amount PAYTM & CARD'][0]); 
  body.replaceText("{Cash Sales - Outlet}", info['Cash Sales - Outlet'][0]); 
  body.replaceText("{Cash Sales - KCCO App}", info['Cash Sales - KCCO App'][0]); 
  body.replaceText("{Card Sales}", info['Card Sales'][0]); 
  body.replaceText("{Credit/Udhar Sales}", info['Credit/Udhar Sales'][0]); 
  body.replaceText("{Paytm Sales}", info['Paytm Sales'][0]); 
  body.replaceText("{KCCO App Sales Razor Pay}", info['KCCO App Sales Razor Pay'][0]); 
  body.replaceText("{Swiggy Sales}", info['Swiggy Sales'][0]); 
  body.replaceText("{Zomato Sales}", info['Zomato Sales'][0]); 
  body.replaceText("{Dunzo Sales}", info['Dunzo Sales'][0]); 
  body.replaceText("{Zomato Gold Sales}", info['Zomato Gold Sales'][0]); 
  body.replaceText("{Dine Out Sales}", info['Dine Out Sales'][0]); 
  body.replaceText("{Total Cancelled Bill Amount}", info['Total Cancelled Bill Amount'][0]); 
  body.replaceText("{Total Sales of the Day - Total of Above}", info['Total Sales of the Day - Total of Above'][0]); 
  body.replaceText("{Total Sales of the Day - As Per PetPooja}", info['Total Sales of the Day - As Per PetPooja'][0]); 
  body.replaceText("{Old Due Receipts - Cash}", info['Old Due Receipts - Cash'][0]); 
  body.replaceText("{Old Due Receipts - Other Modes}", info['Old Due Receipts - Other Modes'][0]); 
  body.replaceText("{Tip Receipts - Card}", info['Tip Receipts - Card'][0]); 
  body.replaceText("{Tip Receipts - Paytm}", info['Tip Receipts - Paytm'][0]); 
  body.replaceText("{Currency Note of INR 2000}", info['Currency Note of INR 2000'][0]); 
  body.replaceText("{Currency Note of INR 500}", info['Currency Note of INR 500'][0]); 
  body.replaceText("{Currency Note of INR 200}", info['Currency Note of INR 200'][0]); 
  body.replaceText("{Currency Note of INR 100}", info['Currency Note of INR 100'][0]); 
  body.replaceText("{Currency Note of INR 50}", info['Currency Note of INR 50'][0]); 
  body.replaceText("{Currency Note of INR 20}", info['Currency Note of INR 20'][0]); 
  body.replaceText("{Currency Note of INR 10}", info['Currency Note of INR 10'][0]); 
  body.replaceText("{Other Currency Note/Coins}", info['Other Currency Note/Coins'][0]); 
  body.replaceText("{Total Available Cash to Handover}", info['Total Available Cash to Handover'][0]); 
  body.replaceText("{Total No. of Credit Bills}", info['Total No. of Credit Bills'][0]); 
  body.replaceText("{S.No.//Name of Person// Mobile No// Credit Approval By// Bill No// Total Amount of Credit}", info['S.No.//Name of Person// Mobile No// Credit Approval By// Bill No// Total Amount of Credit'][0]); 
  body.replaceText("{Total No. of Bills of Credit Recovery}", info['Total No. of Bills of Credit Recovery'][0]); 
  body.replaceText("{S.No.//Name of Person// Mobile No// Mode of Payment// Bill No// Total Amount Received}", info['S.No.//Name of Person// Mobile No// Mode of Payment// Bill No// Total Amount Received'][0]); 
  body.replaceText("{Total No. of Complementary Bills During the Day}", info['Total No. of Complementary Bills During the Day'][0]); 
  body.replaceText("{S.No.//Name of Person// Mobile No//Reason// Bill No//Amount Settled}", info['S.No.//Name of Person// Mobile No//Reason// Bill No//Amount Settled'][0]); 
  body.replaceText("{Total No. of Cancelled Bills During the Day}", info['Total No. of Cancelled Bills During the Day'][0]); 
  body.replaceText("{S.No.//Name of Person// Mobile No// Bill No// Bill Amount}", info['S.No.//Name of Person// Mobile No// Bill No// Bill Amount'][0]); 
  body.replaceText("{S.No.// Bill No// Mode of Payment//Tip Amount Received}", info['S.No.// Bill No// Mode of Payment//Tip Amount Received'][0]); 
  body.replaceText("{No. of Cylinders Received at Outlet}", info['No. of Cylinders Received at Outlet'][0]); 
  body.replaceText("{Timestamp}", info['Timestamp'][0]); 
  body.replaceText("{Email address}", info['Email address'][0]); 
  //body.replaceText("{Total of Cash Denomination}", info['Total of Cash Denomination'][0]); 

  openDoc.saveAndClose(); 
  const blobPDF = newTempFile.getAs(MimeType.PDF); 
  // 动态设置PDF文件名
  const pdfFile = pdfFolder.createFile(blobPDF).setName(`DTR - ${outletName} - ${reportDate}`); 
  tempFolder.removeFile(newTempFile); 
  return pdfFile; 
}

关键改动说明 #

1. 提取核心变量复用：在afterFormSubmit开头先把门店名称和报告日期提取为变量，避免后续重复调用info['XXX'][0]，让代码更简洁易维护。

2. 动态邮件主题：

   • 给sendEmail函数新增outletName和reportDate参数
   • 使用JavaScript模板字符串拼接主题，自动替换为提交的门店和日期

3. 动态邮件正文：

   • 同样用模板字符串修改正文内容，把固定文本替换为动态变量值，让邮件内容更贴合实际提交的报告

4. 规范PDF文件名：

   • 在createPDF的setName方法中，用模板字符串生成DTR - [门店名称] - [报告日期]格式的文件名
   • 给createPDF新增参数，直接传入已提取的变量，避免重复获取

另外，我把原代码里的"全部替换成了标准双引号"，因为原代码中的转义字符在Google Apps Script中会导致语法错误，这是之前脚本可能存在的隐藏问题。

内容的提问来源于stack exchange，提问作者Admin - R K Pareek Co