Send emails from a google sheet when a due date is crossed (overdue invoices for example) article: http://blog.gsmart.in/google-sheets-send-email-based-on-date/ ‎

google-sheets-send-email-based-on-date.js

function onOpen() 
{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Invoice')
      .addItem('mark Overdue', 'doOverdueCheck')
      .addItem('show Overdue Info', 'showOverDueInfo')
      .addItem('send Emails', 'sendOverdueEmails')
      .addToUi();
}
 
function doOverdueCheck()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var data_range = sheet.getDataRange();
  var last_row = data_range.getLastRow();
  var today= new Date();
  today.setHours(0,0,0,0);
  
  sheet.getRange('E:E').clearContent();
  for(var r=2;r<=last_row;r++)
  {
    var inv_date = data_range.getCell(r,4).getValue();
    inv_date.setHours(0,0,0,0);
    if(today > inv_date)
    {
      sheet.getRange(r, 5).setValue("overdue");
    }
  }
}

function sendOverdueEmails()
{
  
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  
  var data_range = sheet.getDataRange();
  var last_row = data_range.getLastRow();
  var today= new Date();
  today.setHours(0,0,0,0);

  for(var r=2;r<=last_row;r++)
  {
    var inv_date = data_range.getCell(r,4).getValue();
    inv_date.setHours(0,0,0,0);
    if(today > inv_date)
    {
      sendEmail(r);
    }
  }
  
}

function sendEmail(row)
{
  var overdue = getOverDueInfo(row);
  
  var templ = HtmlService
      .createTemplateFromFile('client-email');
  
  templ.overdue = overdue;
  
  var message = templ.evaluate().getContent();
  
  MailApp.sendEmail({
    to: overdue.email,
    subject: "Your invoice is due.",
    htmlBody: message
  });
  
}

function showOverDueInfo()
{
  var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
  
  var overdue = getOverDueInfo(row);
  
  var templ = HtmlService
      .createTemplateFromFile('dialog-box');
  
  templ.overdue = overdue;
  
  SpreadsheetApp.getUi().showModalDialog(templ.evaluate(), 'Overdue info');
  
  
}

function getOverDueInfo(row)
{
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  
  var values = sheet.getRange(row,1,row,4).getValues();
  var rec = values[0];
  
  var overdue = 
      {
        first_name:rec[0],
        last_name:rec[1],
        email: rec[2],
        due_date:rec[3]
      };
   
   overdue.name = overdue.first_name +' '+ overdue.last_name;
   overdue.date_str = sheet.getRange(row,4).getDisplayValue();
  
   var due_date = new Date(overdue.due_date);
   due_date.setHours(0,0,0,0);
   var today = new Date();
   today.setHours(0,0,0,0);
  
   var difference_ms = Math.abs(today.getTime() - due_date.getTime() );
   
   overdue.num_days = Math.round(difference_ms/(24*60*60*1000) );
  
   return overdue;
}

how can I access dialog-box html file?

Can I compensate you to modify this for my specific spreadsheet? Also, I need to sent out specific emails based on upcoming dates, not overdue dates.