Car Licensing Reminders

Who has never left their insurance renewal and car licensing to the last minute?  With it comes the stress of coordinating insurance, paperwork, landlord signature and a visit to TCD (MOT in the UK).

One option is to set oneself reminders in the calendar each year.  However, if the reminder arrives at the wrong time, we often don't act on it.  Where was the licence number? What's the phone for the insurance? etc.

To automate the process, I created a simple spreadsheet (Google Sheet) that emails me:

Each row will automatically send an email.  But more importantly it is immediately actionable:

I have the link, the phone & the details to renew it straight away.  As the deadline approaches emails highlight the sense of Urgency:

Once done, I only need to change a single cell:  2021 -> 2022 and the notifications will run the following year.

Get in touch if you need help automating your work or your life.

For the developers out there, here is the google script that runs it all:

function email_if_due(sheet_name)
{
  // var sheet = SpreadsheetApp.getActiveSheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name); 
  
  var data_range = sheet.getDataRange();
  var last_row = data_range.getLastRow();
  var today= new Date();
  today.setHours(0,0,0,0);
  
  sheet.getRange('B4:B15').clearContent(); // clear column of due emails
  for(var r=4;r<=last_row;r++)
  {
    var start_string = data_range.getCell(r,4).getValue();
    var end_string = data_range.getCell(r,5).getValue();
    // convert date string to date
    var start_date = new Date(start_string).setHours(0,0,0,0)
    var end_date = new Date(end_string).setHours(0,0,0,0)
    
    // get email information from sheet
    var em_address = data_range.getCell(r,3).getValue();
    var em_title = data_range.getCell(r,6).getValue();
    var em_message = data_range.getCell(r,7).getValue();
    
      
    if(today >= start_date && today <= end_date)
    {
     MailApp.sendEmail(em_address, em_title, em_message)
     sheet.getRange(r, 2).setValue("SEND");
    }
  }
}

function sendNotifications(){
email_if_due("Car");
email_if_due("Bike");
}