从开始日期到结束日期，每20天的邮件余数

Question

我是新来的编码，我想写一个谷歌应用程序的脚本代码为电子邮件剩余的每20天从开始日期到结束日期。

function emailAlert() {
var sheet = SpreadsheetApp.getActiveSheet();
lastRow = sheet.getLastRow();
lastCol = sheet.getLastColumn();
var dataRange = sheet.getRange( 2, 1, lastRow-1, lastCol);
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var date = new Date(row[3]); // make the sheet value a date object
//Logger.log('original value = '+date);
//Logger.log('method 1 : '+new Date(date.getTime()+5*3600000*24));
twentyDaysFromStartDate = new Date(date.setDate(date.getDate()+20));
var formattedtwentyDaysFromStartDate = Utilities.formatDate(new Date(twentyDaysFromStartDate), "ET", "dd/MM/yyyy");
Logger.log (formattedtwentyDaysFromStartDate);
var expireDateFormat = Utilities.formatDate(
new Date(row[4]),
'ET',
'dd/MM/yyy'
);
Logger.log (expireDateFormat);
var subject = '';
var message =
' Controllare documenti Progetto Formativo ' +
'n' +
' CODICE FISCALE: ' +
row[0] +
'n' +
' NOME: ' +
row[1] +
'n' +
' COGNOME: ' +
row[2] +
'n' +
' DATA INIZIO: ' +
row[3] +
'n' +
' DATA FINE: ' +
expireDateFormat;
var today = Utilities.formatDate(
new Date(),
'ET',
'dd/MM/yyyy',
);
Logger.log(today);
if(formattedtwentyDaysFromStartDate!== expireDateFormat)
{
if(formattedtwentyDaysFromStartDate === today)
{
var subject =
'Progetto formativo' +
row[0] +
' - ' +
expireDateFormat;
MailApp.sendEmail('[e-mail]', subject, message);
}
}
}
}

此代码适用于从开始日期起每20天的电子邮件剩余部分。但它甚至会在过期后发送剩余的电子邮件。有人能帮我一下吗?

Answer 1

你可以调用这个函数来查找今天是否应该发送电子邮件:

function everyNDays(n, startDate, currentDate, terminationDate) {
const day = 86400000;
const t1 = new Date(startDate).valueOf();
const t2 = new Date(currentDate).valueOf();
const t3 = new Date(terminationDate).valueOf();
if (t2 > t3) {
return false;
}
const d = Math.abs(t2 - t1);
const days = Math.floor(d / day);
return days % n == 0;
}

像这样使用:

const now = new Date();
if (everyNDays(20, '2021-01-02', now, '2021-12-31')) {
// send email
}

您可以使用时间驱动触发器每天运行一次电子邮件发送功能。