如何根据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屋！