I need to fine tune the script, [0] is clients name, [5] are expiry dates, [6] my mail. The script should automatically check every day at 8 am, and send auto mails if something is expired. The mail should be sent 15 days before expiry date [5], and should be sent only 1 time when reach expiry. With this seems to work with no errors, but something isn't right. Thank you for your help

function alertSender() {
  var today = new Date();
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => {
    var values = sheet.getDataRange().getValues().filter(r => r[0] != '');
    for (n = 1; n < values.length;   n) {
      var cell_date = values[n][5];
      var expired = (cell_date - 15 * 86400000) > today;
      if (expired) {
        MailApp.sendEmail(values[n][6], 'Mail automatica scadenza', 'Polizza scaduta per il sig. '   values[n][0]);
        Logger.log('Mail inviata all\'indirizzo '   values[n][6]   ' del sig. '   values[n][0]);
      }
    }
  })
}

CodePudding user response：

Add "SENT" on cell H1 of each tab. Colmn H will receive the date of email sending. This date will be tested to prevent recurrences.

Try

function alertSender() {
  var today = new Date();
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => {
    var values = sheet.getDataRange().getValues()
    values.forEach(r => {
      if (r[0] != '' && r[7] == '') {
        var cell_date = new Date(r[5]);
        var expired = (cell_date <= new Date(today.getTime()   15 * 86400000));
        if (expired) {
          MailApp.sendEmail(r[6], 'Mail automatica', 'Servizio scaduto per il sig. '   r[0]);
          Logger.log('Mail inviata all\'indirizzo '   r[6]   ' del sig. '   r[0]);
          r[7] = Utilities.formatDate(today, Session.getScriptTimeZone(), "dd/MM/yyyy HH:mm")
        }
      }
    })
    sheet.getDataRange().setValues(values)
  })
}

to get the exact date, you can try

var expired = (cell_date.valueOf() == new Date(today.getFullYear(), today.getMonth(), today.getDate()   15).valueOf())