google-apps-script - 谷歌表格 - 电子邮件
问题描述
我正在考虑在提交谷歌表单时触发自动电子邮件。它工作正常,但是我在下面的代码中遇到了 3 个问题
- 每次响应都会触发两封电子邮件,而我只希望发送一封电子邮件
- 电子邮件是从“所有者”的 ID 发送的,而不是在谷歌表单上提交回复的那个。
- 它应该在 col 7 中将所有成功发送的电子邮件的状态捕获为“EMAIL SENT”,但是,它没有这样做。
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";
function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow(); // Number of rows to process
var dataRange = sheet.getRange(numRows, 2, 1, sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[5]; // First column
var message = 'Hello,'+ "\n"
+ "\n"
+ 'We have received an inquiry from your customer in Inbound'+ ".\n"
+ "\n"
+ 'Lead No is' + " - "
+ row[1] + "\n"
+ "\n"
+ 'Kindly arrange a callback'+ "\n"
+ "\n"
+ 'Regards,'+ "\n"
+ 'Team Inbound' + "\n"
+ "\n"
+ 'This is an auto-generated email'; // Second column
var emailSent = row[7]; // Third column
if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
var subject = "Inbound Inquiry"+ " - " + row[1];
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 8).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}
解决方案
试试这种方式:
function sendEmails() {
var sheet=SpreadsheetApp.getActiveSheet();
var startRow=2;
var dataRange=sheet.getRange(startRow,1,sheet.getLastRow()-startRow+1,sheet.getLastColumn());//I think startRow is a better fit for the first parameter and my guess is that you did't really intend for data.length to be only 1 row.
var data=dataRange.getValues();
for (var i=0;i<data.length;++i) {
var row=data[i];
var emailAddress=row[5];//column 6
var message=Utilities.formatString('Hello,\n\nWe have received an inquiry from your customer in Inbound.\n\nLead No is - %s\n\nKindly arrange a callback.\n\nRegards,\nTeam Inbound\n\nThis is an auto-generated email.',row[1]);
var emailSent = row[7];//column 8
if (emailSent != "EMAIL_SENT") { // Prevents sending duplicates
var subject = "Inbound Inquiry"+ " - " + row[1];//column 2
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 8).setValue("EMAIL_SENT");//column 8 and I don't think it's necessary to flush in the loop
}
}
}
检查评论以确保我得到了正确的列。
推荐阅读
- flutter - 如何在轻按小部件后滑动小部件?
- r - 将列名作为标签添加到我的 Sankey 图中
- python - Meme 命令 discord.py
- tensorflow - 如何解决“内核似乎已经死机。它将自动重启”
- javascript - NextJs 上富文本棱镜的目标 _blank
- css - 如何在页面末尾添加不同的背景颜色?
- reactjs - 无法以 antd 形式显示来自 Firebase 实时数据库的数据
- javascript - 如何在 NuxtJs 应用程序中重定向到有关 Firebase 身份验证的主页
- java - Application Insights Java SDK + 代理不能很好地协同工作
- python - 运行位于项目文件夹中的进程,未安装在计算机上