Google Apps脚本 - 根据单元格中的数据发送电子邮件 [英] Google Apps Script - Send email based on data in cell
问题描述
这是我的示例工作簿： https://docs.google.com/spreadsheet/ccc?key=0AiHAV8ZZ5nexdDJqODhmamhldjN1ZTRKc09iZXNBZ3c#gid=0
这是我拥有的代码：
function sendEmail（）{
var sheet = SpreadsheetApp.getActiveSheet（）;
var startRow = 2; //处理
的第一行数据var numRows = sheet.getLastRow（） - 1; //要处理的行数
//获取单元格范围A2：B3
var dataRange = sheet.getRange（startRow，1，numRows，sheet.getLastColumn（））;
//获取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（日期）;
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]; //第一列
var message = row [1]; //第二列
var subject =是时候练习了！ +消息;
MailApp.sendEmail（emailAddress，subject，message）;
//Logger.log('SENT：'+ emailAddress +''+ subject +''+ message）
}
}
}
但我不确定它是否正常工作，它会自动发送电子邮件吗？很显然，我知道的脚本很少。
解决方案你现有的共享代码只有1个错误，工作：
var sheetDate = new Date（row [2]）;
你的数组中只有2个索引，所以这应该是：
lockquote
var sheetDate = new Date （row [1]）;
另外，因为您正在使用Utilities.formatDate以yyyy：MM：dd格式，所以您不需要设置小时分钟和秒钟，因为Utilities.formatDate返回一个没有时间分量的字符串。此外，您不需要创建sheetDate或日期，这些都可以构建为Utilities.formatDate（请参见下文）中的第一个参数。关于此主题的另一件事是，因为日期值在电子表格中被格式化为日期，所以它们将作为日期对象返回到脚本中，所以实际上，不需要调用新日期（row [1]）但它并没有伤害任何东西。
函数sendEmail（）{
try {
var sheet = SpreadsheetApp.getActiveSheet（）;
var startRow = 2; //处理
的第一行数据var numRows = sheet.getLastRow（） - 1; //要处理的行数
//获取单元格范围A2：B3
var dataRange = sheet.getRange（startRow，1，numRows，sheet.getLastColumn（））;
//获取Range中每一行的值。
var data = dataRange.getValues（）;
for（i in data）{
var row = data [i];
//Logger.log（sheetDate）;
var Sdate = Utilities.formatDate（new Date（'row-1'），$ date''）
var SsheetDate = Utilities.formatDate 'GMT + 0200'，'yyyy：MM：dd'）
Logger.log（Sdate +'=？'+ SsheetDate）
if（Sdate == SsheetDate）{
var emailAddress = row [0]; //第一列
var message = row [1]; //第二列
var subject =是时候练习了！ +消息;
MailApp.sendEmail（emailAddress，subject，message）;
//Logger.log('SENT：'+ emailAddress +''+ subject +''+ message）
}
}
} catch（err）{
Logger .log（err.lineNumber +' - '+ err）;
}
}
So I'm trying to set up a reminder email to automatically be sent based on the date in a cell. Kind of like this: Google Apps Script - Send Email based on date in cell Here's my sample workbook: https://docs.google.com/spreadsheet/ccc?key=0AiHAV8ZZ5nexdDJqODhmamhldjN1ZTRKc09iZXNBZ3c#gid=0
This is the code that I have:
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 = "It's time to practice!" +message;
MailApp.sendEmail(emailAddress, subject, message);
//Logger.log('SENT :'+emailAddress+' '+subject+' '+message)
}
}
}
But I'm not sure if it's working, and will it automatically send the email out? Obviously, I know very little script.
You only have 1 error in your existing code shared here, that is keeping it from working:
var sheetDate = new Date(row[2]);
You only have 2 indexes in your array, so this should be:
var sheetDate = new Date(row[1]);
Also, because you are using Utilities.formatDate to yyyy:MM:dd format, you do not need to set the hours minute and seconds, because Utilities.formatDate is returning a string with no time component. Furthermore, you do not need to create sheetDate or date, those can both be constructed as the first parameter in the Utilities.formatDate (see below). One other thing on this topic, because your date values are formatted as a date in your spreadsheet, they are being returned to your script as a date object, so really, it isn't necessary to call new Date(row[1]) .. but it doesn't hurt anything.
function sendEmail() {
try{
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();
for (i in data) {
var row = data[i];
//Logger.log(sheetDate);
var Sdate = Utilities.formatDate(new Date(),'GMT-0500','yyyy:MM:dd')
var SsheetDate = Utilities.formatDate(new Date(row[1]),'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 = "It's time to practice!" +message;
MailApp.sendEmail(emailAddress, subject, message);
//Logger.log('SENT :'+emailAddress+' '+subject+' '+message)
}
}
}catch(err){
Logger.log(err.lineNumber + ' - ' + err);
}
}
这篇关于Google Apps脚本 - 根据单元格中的数据发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
