根据日期发送提醒电子邮件 [英] Send reminder emails based on date

问题描述

我正在使用以下脚本从Google工作表中发送电子邮件提醒，但希望对其进行修改，以便在每行单元格F中指定的日期发送电子邮件。

I'm using the following script to send email reminders from a Google Sheet, but would like to modify it so that it send the email out on a date specified in cell F of each row.

var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  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[0];  // First column
    var subject = row[1];     // Second column
    var message = row[2];    // Third column
    var emailSent = row[3];     

    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();
    }
  }
}

这是我有的和任何尝试在其中添加日期失败非常糟糕。

That's what I have and any attempts to add a date in there have failed pretty badly.

我遇到了这个早期的问题： Google Apps Script - 根据单元格日期发送电子邮件，但无法将其与我的脚本相结合。

I came across this earlier question: Google Apps Script - Send Email based on date in cell but was unable to combine it with my script.

推荐答案

解决方案Serge在为您设置了一个非常灵活的脚本，可以使用日期/时间的任何部分作为发送标准的阶段。

The solution Serge provided in that previous answer sets the stage for you to have a very flexible script, able to use any portion of the date / time as a criteria for sending.

这是一个更简单和更不灵活的方法。假设：

Here's a simpler and less flexible approach. Assumptions:

• 日期在电子表格中作为日期而不是字符串。


• 我们只关心日期匹配;


• 电子表格中的脚本和提醒日期基于相同的时区。

• The date is in the spreadsheet as a date, not a string.
• We only care that the date matches; hours, minutes and seconds are inconsequential.
• The script and the reminder dates in the spreadsheet are based on the same timezone.

这里的魔法是关于比较日期。 JavaScript Date对象是一个数字表示从1970年代开始的时间，世界时间。比较日期的平等是困难的。但是，由于上述假设，我们只关心日期，这是有帮助的。为了解决时区问题并消除小时，分钟等的影响，我们只需使用相同的Date方法从我们要比较的日期对象生成日期字符串。

The magic here is all about comparing dates. A JavaScript Date object is a numeric representation of time elapsed from the start of 1970, Universal time. Comparing equality of dates then, is difficult. However, thanks to the assumption above, we only care about the date, which is helpful. To get around timezone concerns and eliminate the effect of hours, minutes, etc., we just use the same Date method to generate date strings from the date objects we want to compare. The toLocaleDateString() method adjusts for time zones for us.

结果脚本：

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[0];  // First column
    var subject = row[1];     // Second column
    var message = row[2];    // Third column
    var emailSent = row[3];     
    var reminderDate = row[5].toLocaleDateString();  // date specified in cell F

    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();
    }
  }
}

这篇关于根据日期发送提醒电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！