I'm new to coding, i want to write a google apps script code for email remainder for every 20 days from start date to till end date.

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('[email protected]', subject, message);
        }
     }
  }
}

This code works fine for email remainder for every 20 days from start date. But it is even sending the email remainder after the expired date. Please anyone help me with this?

CodePudding user response：

You can call this function to find if today is one of the days when an email should be sent:

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

Use it like this:

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

You can run your email sending function once a day using a time-driven trigger.