Javascript Google Apps 脚本 - 根据单元格中的日期发送电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11025491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Google Apps Script - Send Email based on date in cell
提问by jjones312
I've looked around and have bits and pieces but can't put the puzzle together. I'm attempting to create a script that will run on a trigger configured to run daily. The trigger will be setup under Resources option in the editor.
我环顾四周，零零碎碎，但无法将拼图拼凑在一起。我正在尝试创建一个脚本，该脚本将在配置为每天运行的触发器上运行。触发器将在编辑器的资源选项下设置。
Basically I'm looking for the script to capture a range of cells, identify a due date, which will be populated in a column, match it to the current date. If it matches then send a email. I've started with the send a email from spreadsheet tutorial at Google. I've added in a if statement to check for the date but I'm losing it on the comparsion to dataRange. Anyone might help correct these or point me to in direction to research.
基本上我正在寻找脚本来捕获一系列单元格，确定一个截止日期，它将填充在一列中，将其与当前日期匹配。如果匹配，则发送电子邮件。我已经开始从 Google 的电子表格教程发送电子邮件。我已经添加了一个 if 语句来检查日期，但在与 dataRange 的比较中我失去了它。任何人都可能会帮助纠正这些问题或指出我的研究方向。
The script appears to run but nothing happens, which I believe is because "if (currentTime == dataRange)" The dataRange is not matching correctly??
脚本似乎运行但没有任何反应，我认为这是因为“if (currentTime == dataRange)” dataRange 没有正确匹配？？
Here is the code:
这是代码：
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 50; // Number of rows to process
// Fetch the range of cells
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Get todays date
var currentTime = new Date();
var month = currentTime.getMonth() + 1;
var day = currentTime.getDate();
var year = currentTime.getFullYear();
//Test column for date due & match to current date
if ( currentTime == dataRange) {
for (i in data) {
var row = data[i];
var emailAddress = row[0]; // First column
var message = row[1]; // Second column
var subject = "Task Item Due";
MailApp.sendEmail(emailAddress, subject, message);
}
}
}
I'm updating the suggestion provided by Srik and providing his suggestion in the below code. I've attempted to post this a couple time so I'm not sure why its not making past peer review?
我正在更新 Srik 提供的建议并在下面的代码中提供他的建议。我已经尝试发布了几次，所以我不确定为什么它没有通过同行评审？
function sendEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 50; // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 50);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Browser.msgBox(data)
for (i in data) {
var row = data[i];
var date = new Date();
var sheetDate = new Date(row[1]);
if (date.getDate() == sheetDate.getDate() && date.getMonth() == sheetDate.getMonth() && date.getFullYear() == sheetDate.getFullYear());
{
var emailAddress = row[0]; // First column
var message = row[2]; // Second column
var subject = "Sending emails from a Spreadsheet";
MailApp.sendEmail(emailAddress, subject, message);
// Browser.msgBox(emailAddress)
}
}
}
The code appears to run but I'm getting the following error w/in the script editor. "Failed to send email: no recipient (line 23)". But it still sends emails. It should compare the dates and only send the email if the date matches. Its sending an email for every row. I've shared out the spreadsheet to see the code and how the spreadsheet is setup. Shared Spreadsheet
代码似乎可以运行，但在脚本编辑器中出现以下错误。“无法发送电子邮件：没有收件人（第 23 行）”。但它仍然发送电子邮件。它应该比较日期，并且只有在日期匹配时才发送电子邮件。它为每一行发送一封电子邮件。我已经分享了电子表格以查看代码以及电子表格的设置方式。 共享电子表格
UPDATED CODE W/ SERGE HELP on the logger and Utilities.formatDate.. Thanks!!
在记录器和 Utilities.formatDate 上使用 SERGE 帮助更新代码..谢谢！
function sendEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow()-1; // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Logger.log(data)
for (i in data) {
var row = data[i];
var date = new Date();
date.setHours(0);
date.setMinutes(0);
date.setSeconds(0);
//Logger.log(date);
var sheetDate = new Date(row[2]);
//Logger.log(sheetDate);
var Sdate = Utilities.formatDate(date,'GMT+0200','yyyy:MM:dd')
var SsheetDate = Utilities.formatDate(sheetDate,'GMT+0200', 'yyyy:MM:dd')
Logger.log(Sdate+' =? '+SsheetDate)
if (Sdate == SsheetDate){
var emailAddress = row[0]; // First column
var message = row[1]; // Second column
var subject = "Your assigned task is due today." +message;
MailApp.sendEmail(emailAddress, subject, message);
//Logger.log('SENT :'+emailAddress+' '+subject+' '+message)
}
}
}
回答by Serge insas
here is a working version of your code, I used Utilities.formatDate to make strings of your dates, so you can choose what you compare (only days, hours ? min ?)
这是您的代码的工作版本，我使用 Utilities.formatDate 制作日期字符串，因此您可以选择比较的内容（仅天、小时？分钟？）
I commented the mail call just for my tests, re-enable it when you need
我评论了邮件呼叫只是为了我的测试，当你需要时重新启用它
function sendEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow()-1; // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
Logger.log(data)
for (i in data) {
var row = data[i];
var date = new Date();
var sheetDate = new Date(row[1]);
Sdate=Utilities.formatDate(date,'GMT+0200','yyyy:MM:dd')
SsheetDate=Utilities.formatDate(sheetDate,'GMT+0200', 'yyyy:MM:dd')
Logger.log(Sdate+' =? '+SsheetDate)
if (Sdate == SsheetDate){
var emailAddress = row[0]; // First column
var message = row[2]; // Second column
var subject = "Sending emails from a Spreadsheet";
// MailApp.sendEmail(emailAddress, subject, message);
Logger.log('SENT :'+emailAddress+' '+subject+' '+message)
}
}
}
don't forget to look at the logs ;-)
不要忘记查看日志;-)
回答by eddyparkinson
I would do it this way ....
我会这样做....
Setup the spreadsheet like this: https://docs.google.com/spreadsheet/ccc?key=0AkGlO9jJLGO8dDJad3VNTkhJcHR3UXlJSVRNTFJreWc
像这样设置电子表格：https: //docs.google.com/spreadsheet/ccc?key=0AkGlO9jJLGO8dDJad3VNTkhJcHR3UXlJSVRNTFJreWc
Change the code to:
将代码更改为：
function sendEmails() {
var spreadsheet = SpreadsheetApp.openById('Type spreadsheet key here from spreadsheet URL');
/// e.g. var spreadsheet = SpreadsheetApp.openById('0AkGlO9jJLGO8dDJad3VNTkhJcHR3UXlJSVRNTFJreWc');
var sheet = spreadsheet.getSheets()[0]; // gets the first sheet, i.e. sheet 0
var range = sheet.getRange("B1");
var dateString = new Date().toString();
range.setValue(dateString); // this makes all formulas recalculate
var startRow = 4; // First row of data to process
var numRows = 50; // Number of rows to process
// Fetch the range of cells
var dataRange = sheet.getRange(startRow, 1, numRows, 4)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
if( row[3] == true) {
var emailAddress = row[0]; // First column
var message = row[1]; // Second column
var subject = "Task Item Due";
try {
MailApp.sendEmail(emailAddress, subject, message);
} catch(errorDetails) {
// MailApp.sendEmail("[email protected]", "sendEmail script error", errorDetails.message);
}
}
}
}
The trigger:
触发：
Because of the trigger, you will need to open the spreadsheet using openById. To do this, in the code replace 'Type spreadsheet key here from spreadsheet URL'. To find the key, open the google docs spreadsheet, the link has "key=A0a0df..." paste the code. See the example.
由于触发器，您将需要使用 openById 打开电子表格。为此，请在代码中替换“在此处从电子表格 URL 中键入电子表格键”。要找到密钥，请打开 google docs 电子表格，链接有“key=A0a0df...”粘贴代码。请参阅示例。
JavaScript: if you want to learn more about using Java Script, I recommend http://www.w3schools.com/js/default.asp
JavaScript：如果您想了解有关使用 Java Script 的更多信息，我推荐http://www.w3schools.com/js/default.asp
回答by Srik
You are comparing two different objects here
您在这里比较两个不同的对象
if ( currentTime == dataRange) {
currentTime is a Date object whereas dataRange is a Range object - they'll never be equal and so nothing happens. What you could do is (DATE_COL is the column having your date)
currentTime 是一个 Date 对象，而 dataRange 是一个 Range 对象——它们永远不会相等，所以什么也不会发生。您可以做的是（DATE_COL 是包含您的日期的列）
for ( var i in data ){
var row = data[i] ;
var today = new Date();
var date = new Date(row[DATE_COL]) ;
if (today.getDate() == date.getDate() &&
today.getMonth() == date.getMonth() &&
today.getYear() == date.getYear() {
/* Your email code here */
}
}
