Send email based on cell value (date)

Question

I've looked around and have bits and pieces but can't put the puzzle together.

I need to send an email 90 days before a date contained in a list of cells in the same column.

This is what my data looks like:

For example, the script should send an email on 01/08/19 with the following text:

Reminder birthday Jon Doe 01/11/2019

Have you tried any code to achieve this? – ross Jul 09 '19 at 14:15 — ross, Jul 09 '19 at 14:15
Are you sending the email to yourself? – AMolina Jul 09 '19 at 14:26 — AMolina, Jul 09 '19 at 14:26

score 1 · Answer 1 · answered Jul 09 '19 at 14:36

Try this:

function send(){
  var ss = SpreadsheetApp.getActiveSheet();
  var firstDate = new Date(); // today

  var data = ss.getRange("A6:C" + ss.getLastRow()).getValues(); // gets the name and the bday 
  for(var i = 0; i < data.length; i++){
    if (time(firstDate, data[i][2]))
        Logger.log("SEND"); // Here you would send the email.
    else
      Logger.log("NOT SENT");
  }
}

function time(firstDate, secondDate){
  var oneDay = 24*60*60*1000; // hours*minutes*seconds*milliseconds

  var diffDays = Math.round(Math.abs((firstDate.getTime() - secondDate.getTime())/(oneDay)));
  if (diffDays <= 90)
    return true;
  else 
    return false;
}

Here is where I found how to calculate the time difference. And my code is build assuming you have the list starting on A6.

score 0 · Answer 2 · answered Jul 11 '19 at 13:57

I'm use this script for alert bank certificates expiration, yo can easy adapt for your problem, sorry but I Dont have not time to translate.

function Vencimiento() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var lastRow = sheet.getLastRow();
  lastRow = lastRow-4;
  var rango = sheet.getRange(5, 2, lastRow, 4); 
  var valores = rango.getValues();
  var inicio = Date.now();
  for(var i = 0; i < lastRow; i++){ 
    var vence = Date.parse(valores[i][2]);     
    var dif = Math.round((vence-inicio)/86400000) ; 
     switch (dif){
      case 0:{enviaCorreo(valores[i][0],valores[i][1],valores[i][3],dif);break};
      case 7:{enviaCorreo(valores[i][0],valores[i][1],valores[i][3],dif);break};
      case 15:{enviaCorreo(valores[i][0],valores[i][1],valores[i][3],dif);break};
      case 45:{enviaCorreo(valores[i][0],valores[i][1],valores[i][3],dif);break};
    }
  }
}

function enviaCorreo(empresa,cert,banco,dias){
  var array = ["mail@gmail.com", "mail@gmail.com"];
  if(dias == 0){
    var str = ("El día de hoy se vencio el certificado nro: "+cert+" del banco "+banco);
    Mail(array,str);
  }else{
  var str = ("Restan "+ dias+" para que se venza el certificado nro: "+cert+ " del banco "+banco);
    Mail(array,str);}
}

function Mail(destinatarios,mensaje){
  var recipient = destinatarios;
  var enviar = mensaje;
  GmailApp.sendEmail(destinatarios, 'Alerta Certificado bancario', enviar); 
}

Send email based on cell value (date)

2 Answers2