javascript - 电子邮件主题应根据单元格值更改
问题描述
我创建了一个 Google 脚本,用于在用户提交表单后发送电子邮件。该脚本运行良好。但不恰当的一件事是电子邮件的主题。我希望根据单元格值更改电子邮件主题。
目前,我正在使用这个脚本:
function afterFormSubmit(e) {
const info = e.namedValues;
const pdfFile = createPDF(info);
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);
}
function sendEmail(email,pdfFile){
var DOR = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Final Responses").getActiveCell();
var NOO = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Final Responses").getActiveCell();
var subject = "DTR - KCCO - " + NOO + " - " + DOR;
var body = "Sir," + String.fromCharCode(10)+ String.fromCharCode(10) + "Please find the attached Daily Transaction Report for the outlet " + NOO + " for " + DOR + "." + String.fromCharCode(10) + String.fromCharCode(10)+ "--" + String.fromCharCode(10) + "Thanks & Regards" + String.fromCharCode(10) + "KCCO - Automation Team" ;
GmailApp.sendEmail(email, subject, body, {
attachments: [pdfFile],
name: 'KCCO - Automation Team'
});
}
function createPDF(info) {
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);
const pdfFile = pdfFolder.createFile(blobPDF).setName('DTR - KCCO - '+info['Name of Outlet'][0]+ " - " + info['Date of Report'][0]);
tempFolder.removeFile(newTempFile);
return pdfFile;
}
谷歌表单提交的响应链接如下:
https://docs.google.com/spreadsheets/d/1m6Aca0TcLf5UZ7P9CvAdQDokoazQljsqDT6CGh0K3j4/edit?usp=sharing
我想要出口的名称,即 F2:F 和报告日期,即 C2:C
例如,如果用户现在提交表单,数据将出现在第 37 行,因此出口和报告的名称应为 F37 和 C37。
请指导。
解决方案
推荐阅读
- iis - IIS 反向代理背后的 Keycloak
- php - Chart.js 中的日期时间选择器
- python - 在python中使用循环添加所有输入后,如何垂直打印所有计算元素?
- java - 在 Spring Retryable 中的所有尝试之前退出
- python - 使用 discord.py 嵌入多个页面
- svn - TortoiseSVN:文件在日志中标记为“修改”和“添加”
- android - Android首选项屏幕主题不起作用
- python - 使用python分隔列
- elasticsearch - 我想在 Elastic Search 的数组中搜索字段
- javascript - 通过反应应用为所有图像设置基本路径