Checking between dates and times of two dates before sending a mail with GmailApp

[Michail Kotantakis]

Hi group

i have setup a script that checks the following cases:

•  if a start time is > than the end time and throws an error message
• if the selected date is < than today and throws an error
• if the selected date is == to today && the current time is > than the start time and throws an error 

This is working fine and it bis the desired operation. Here is also the script and a legend with the cells and their formatting 

Script

function checkCellD2() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName("Sheet1");

  var starttime = sheet.getRange("A2").getValue();

  var endtime = sheet.getRange("B2").getValue();

  var date1 = sheet.getRange("C2").getValue();

  var date2 = sheet.getRange("D2").getValue();

  var hrdiff = sheet.getRange("E2").getValue();

  var mindiff = sheet.getRange("F2").getValue();

  var CT = sheet.getRange("G2").getValue();

date1 = Utilities.formatDate(date1, Session.getScriptTimeZone(),'dd/MM/yyyy');

date2 = Utilities.formatDate(date2, Session.getScriptTimeZone(),'dd/MM/yyyy');

var today = Utilities.formatDate(new Date(),"0.0.0.0", "dd/MM/yyyy");

if (date1 < today){

   throw "Your date is in the past. Correct and resend";

   }

else if (date2 == "" && starttime > endtime){

  throw "the end time is earlier than the start time. Correct and resend"

  }

else if (today == date1 && CT > starttime){

    throw 'Error!!! The DATE-TIME selection is earlier than the DATE-TIME now. Correct and re-send';

}

else if (date2 != "" && date2 <= date1){

    throw 'Error!!! The DATE-TIME selection is earlier than the DATE-TIME now. Correct and re-send';

}

else {

  Logger.log("starttime:"+starttime);

  Logger.log("endtime:"+endtime);

  Logger.log("date1:"+date1);

  Logger.log("date2:"+date2);

  Logger.log("ct:"+CT);

  Logger.log(hrdiff);

  Logger.log(mindiff);

  }

}

Legend with cells formatting

• A2 is an increment of 5 minutes with data validation ->list of items
• B2 is an increment of 5 minutes with data validation ->list of items
• date1 is a date picker with data validation ->is valid date
• date2  is a date picker with data validation ->is valid date //not initially setup
• hoursdifference is formatted as elapsed hours and has the formula of B2-A2
• minutes difference is formatted as elapsed minutes and has the formula B2-A2
• current time has the formula =TEXT(NOW(),"HH:MM:SS"), to retrieve the current time and is updated every one minute from the calculation settings of the sheet

What i want to introduce is the addition of a date2 and add the following checks plus modifications to the table, to the existing ones, before sending the mail:

• if  date2 is empty && start time > end time throw error (by deleting the date from the date picker)
• if date2 is not empty && date2 <= date1 throw error

So the modified script i am trying is :

function checkCellD2() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName("Sheet1");

  var starttime = sheet.getRange("A2").getValue();

  var endtime = sheet.getRange("B2").getValue();

  var date1 = sheet.getRange("C2").getValue();

  var date2 = sheet.getRange("D2").getValue();

  var hrdiff = sheet.getRange("E2").getValue();

  var mindiff = sheet.getRange("F2").getValue();

  var CT = sheet.getRange("G2").getValue();

date1 = Utilities.formatDate(date1, Session.getScriptTimeZone(),'dd/MM/yyyy');

date2 = Utilities.formatDate(date2, Session.getScriptTimeZone(),'dd/MM/yyyy');

var today = Utilities.formatDate(new Date(),"0.0.0.0", "dd/MM/yyyy");

if (date1 < today){

   throw "Your date is in the past. Correct and resend";

   }

else if (date2 == "" && starttime > endtime){

  throw "the end time is earlier than the start time. Correct and resend"

  }

else if (today == date1 && CT > starttime){

    throw 'Error!!! The DATE-TIME selection is earlier than the DATE-TIME now. Correct and re-send';

}

else if (date2 != "" && date2 <= date1){

    throw 'Error!!! The DATE-TIME selection is earlier than the DATE-TIME now. Correct and re-send';

}

else {

  Logger.log("starttime:"+starttime);

  Logger.log("endtime:"+endtime);

  Logger.log("date1:"+date1);

  Logger.log("date2:"+date2);

  Logger.log("ct:"+CT);

  Logger.log(hrdiff);

  Logger.log(mindiff);

  }

}

After introducing the above to the script, obviously i need to do some modifications to the table cells and the calculations they do, which i managed to do also like the following:

Legend with modified cells formatting & calculations

• A2 is an increment of 5 minutes with data validation ->list of items
• B2 is an increment of 5 minutes with data validation ->list of items
• date1 is a date picker with data validation ->is valid date
• date2  is a date picker with data validation ->is valid date
• hoursdifference is formatted as elapsed hours and has the formula of:
  =IF(D2="",B2-A2,B2-A2+(B2<A2) and the elapsed hours format remains
• minutes difference is formatted as elapsed minutes and has the formula OF:
  =IF(D2="",B2-A2,B2-A2+(B2<A2) and the elapsedminutes format remains.
  
• current time has the formula =TEXT(NOW(),"HH:MM:SS"), to retrieve the current time and is updated every one minute from the calculation settings of the sheet

So the issues that happen are:

If i delete the date2 - DEL button on the keyboard, and has no value,  i will get the following error:

Exception: The parameters (String,String,String) don't match the method signature for Utilities.formatDate. at checkCellD2(Code:13:19)

which refers to the line for the utilities format of date2

Could someone reccomend a way to sort this and also if my conditional statements would work?

Thanks a lot in advance