Google Script for Sheets - Skip rows that have incorrect Date formats when adding to Calendar

Question

I need help with this script I use to add data from a Sheet to a Google Calendar. I admit, I found it online, and I edited it to fit my sheet. It would work perfectly, the only problem is the sheet is populated from data entered in a JotForm that we use at my job for people who need to notify us of upcoming events that we need to provide support for.
That being said, not everyone is careful enough to make sure that their event End Time is after their event Start Time. This causes the code to break and not execute due to the incorrect times (you cannot add an event to Google Calendar if your End Time is before your Start Time.

Here is the code I have so far:

function createCalendarEvent() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendar = CalendarApp.getCalendarById('CalendarIDHere');

  var startRow = 2;  // First row of data to process - 2 exempts my header row
  var numRows = sheet.getLastRow();   // Number of rows to process
  var numColumns = sheet.getLastColumn();

  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns); 
  var data = dataRange.getValues();

  var complete = "Done";

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var name = row[1];             //Event Name
    var sDate = new Date(row[2]);  //Start date/time
    var eDate = new Date(row[3]);  //End date/time
    var attendees = row[4];        //Number of Guests 
    var location = row[5];         //Event Location
    var organizer = row[6];        //Event Organizer
    var contact = row[7];          //Organizer e-mail
    var department = row[8];       //Department
    var setup = row[9];            //Room Set-up Needed
    var config = row[10];          //Room Configuration Requested
    var rSetup = row[11];          //Details about Room Set-up
    var food = row[12];            //Food Services Needed
    var additional = row[13];      //Additional Info
    var eventID = row[14];         //event marked Done

    if (eventID != complete) {
      var currentCell = sheet.getRange(startRow + i, numColumns);
      calendar.createEvent(name, sDate, eDate,
      { 
        description: 'Department: ' + department + '\r' + 'Organizer: ' + organizer + '\r' + 'Organizer E-mail: ' + contact + '\r' + '\r' + 'Attendees: ' + attendees + '\r' + 'Room Configuration Type: ' + config + '\r' + '\r' + 'Room Set Up, Security, Cleaning details: ' + '\r' + rSetup + '\r' + '\r' + 'Food Services?: ' + food + '\r' + '\r' + 'Additional Information: ' + additional
      });

      currentCell.setValue(complete);
    }
  }
}

What can I add to this that will cause the script to check the data and skip any rows that have date/time in the eDate cell before the date/time in the sDate cell?

Tedinoz Tedinoz · Accepted Answer · 2018-11-05T01:38:04

The essence of the questioner's problem is that "not everyone is careful enough to make sure that their event End Time is after their event Start Time. This causes the code to break and not execute due to the incorrect times (you cannot add an event to Google Calendar if your End Time is before your Start Time."

At face value, this is not an issue about Google Sheets, but rather about conditional logic in Jotform. Fortunately JotForm can manage this. Conditions-for-start-and-end-dates explains exactly how to edit JotForm to ensure the start date and end date are in sync. However, it is possible that the questioner doesn't have the ability to edit JotForm so, until then, we'll focus on response data coming through in Google Sheets logic.

The code below is an extract to be inserted after the loop begins and before initialising the rest of event variables. This allows the code to evaluate the start and end date, and proceed with processing ONLY if the start date is earlier than the end date.

I have added three if statements and a variable mismatch.
mismatch is initialised at the start of each new event row with a value of zero. In the event that a date comparison highlights a problem, the variable is given a value of 1. This enables the code to test for various errors in the start and end dates. If, after all this, mismatch doesn't have a value of 1 then the processing can continue "as usual", otherwise, the code can loop to the next event.
if #1 - tests whether the start date is greater than the end date; if yes then give a warning, set a new background, and give mismatch a value of 1 (one).
if #2 - tests whether the start date is equal to the end date; if yes then give a warning, set a new background, and give mismatch a value of 1 (one).
if #3 - tests whether mismatch has a value of 1; if no, then continue processing ; if yes, loop back for next event.

  for (var i = 0; i < data.length; ++i) {
    var mismatch = 0;
    var row = data[i];
    var sDate = new Date(row[2]);  //Start date/time
    var eDate = new Date(row[3]);  //End date/time
    Logger.log("Start = "+sDate+", end = "+eDate);
    if ((sDate - eDate) >0){
      // "start date is later than end data");
      SpreadsheetApp.getUi().alert("AUDIT WARNING: Start date > end date. Script will NOT stop but this request marked in Red. ");
      sheet.getRange(i+2,1,1,6).setBackground("red");
      mismatch = 1;
    }
    if ((sDate - eDate) == 0){
      // start date and end date are the same");
      SpreadsheetApp.getUi().alert("AUDIT WARNING: Start date and end date are the same. Script will NOT stop but this request marked in Orange. ");
      sheet.getRange(i+2,1,1,6).setBackground("darkorange");
      mismatch = 1;
    }
    if (mismatch != 1){
      //by definition, start date is less than end date. 
      // insert code to process

    }

  }

This screenshot shows the effect of processing

Google Script for Sheets - Skip rows that have incorrect Date formats when adding to Calendar

1 Answers