如何根据Google表格中的单元格日期发送提醒日期? [英] How do you send reminder date based on cell date in Google Sheets?
问题描述
我一直在研究如何根据特定日期从Google表格中的单元格发送电子邮件.我发现了这文章,它确实我几乎想做什么,因为时间对我来说并不重要.
I have been researching how to send e-mails based on a specific date from a cell in Google Sheets. I have found this article, which does almost exactly what I would like to do as time does not matter to me.
我的问题是,当我使用代码时,出现错误TypeError:在object中找不到toLocaleDateString函数. (第19行,文件代码").
My problem is that when I use the code, I get the error TypeError: Cannot find function toLocaleDateString in object . (line 19, file "Code").
这是我拥有的工作表设置的副本: https: //docs.google.com/spreadsheets/d/1FlrpvLMRMuq8t6pI4inlKI2acrZJ68pyGHQ7Xtqi5AU/edit?usp=sharing
Here is a copy of the sheet setup I have: https://docs.google.com/spreadsheets/d/1FlrpvLMRMuq8t6pI4inlKI2acrZJ68pyGHQ7Xtqi5AU/edit?usp=sharing
这是我的代码,为我的列设置格式等
Here is my code, formatted for my columns etc
var EMAIL_SENT = "EMAIL_SENT";
function sendEmails3() {
var today = new Date().toLocaleDateString(); // Today's date, without time
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 999; // Number of rows to process
// Fetch the range of cells A2:B999
var dataRange = sheet.getRange(startRow, 1, numRows, 999)
// 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[3]; // POC Email column D
var subject = row[5]; // Subject column F
var message = row[6]; // Message column G
var emailSent = row[7]; // Output the message is sent in column H
var reminderDate = row[2].toLocaleDateString(); // date specified in cell C
if (reminderDate != today) // Skip this reminder if not for today
continue;
if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 4).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}
如何解决此错误?除了错误之外,我还可以通过电子邮件将工作表中的两个联系人发送电子邮件吗?
How can I fix this error? In addition to the error is there a way I can e-mail both points of contact in my sheet?
感谢您提供的所有帮助.
Thank you for all the help you can provide.
推荐答案
此行:
var reminderDate = row[2].toLocaleDateString(); // date specified in cell C
您应该分解为多个操作:
You should break up into multiple operations:
var cellValue,reminderDate;//Define variables at top of function
cellValue = row[2];// date specified in cell C
if (!cellValue) {continue;}//If there is no cell value continue looping
if (typeof cellValue === 'object') {
reminderDate = cellValue.toLocaleDateString();
} else {
cellValue = new Date(cellValue);//Convert date as string to object
reminderDate = cellValue.toLocaleDateString();
}
这篇关于如何根据Google表格中的单元格日期发送提醒日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!