Google Sheet Data to Calendar - Handling Empty or Incorrect Dates

Question

I have a Google Sheets document where each sheet within the spreadsheet refers to a different file. Range B3:E5 is always used for key deadlines, dates, and a relevant Google Calendar entry as follows:

B3:C3 (merged): Description; D3: Date; E3: [randomnumbers]@google.com  
B4:C4 (merged): Description; D4: Date; E4: [randomnumbers]@google.com  
B5:C5 (merged): Description; D5: Date; E5: [randomnumbers]@google.com

I have a script that checks for the calendar entry referred to in column E of that range, creates one if none exists, or updates the entry if one exists, with information from the date and description in B:D. It's a modified form of the top answer at Create Google Calendar Events from Spreadsheet but prevent duplicates.

My issue is that those cells don't always include information (e.g. I might have information in rows 3 and 5, but not in row 4). In that case, the script still creates three calendar entries but the entries associated with the empty entries (e.g. the one from row 4) are set as "December 31, 1969".

I'd like to build a way to catch these empty dates (or bad dates, where the script can't properly interpret the D column into a date for the calendar entry, for example where a typo created "Apriil 4, 2018", which also produces the December 31, 1969 entry) and either error out in the case of bad entries or not create an event for no data entries.

Here's my script with some comments in it. I would really appreciate any help that you guys can give. Obligatory "Not particularly proficient in Javascript, hence my often-clumsy Google-fu".

function exportEvents() {
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(3, 2, 3, 4); // Range: Key Deadlines Description and Dates (B3:E6)
  var fileno = sheet.getSheetName();
  var data = range.getValues();
  var calID = "[calIDinformation]@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calID);
  var formulas = sheet.getRange(3, 2, 1, 3).getFormulas(); // Snagging one line of formulae which get broken in this process; tofix?
  for (i=0; i<data.length; i++) {
    var row = data[i];
    var description = row[0];     // First column (B - "Description")
    var date = new Date(row[2]);  // Third column (D - "Date")
    var id = row[3];              // Fourth column (E - Dates, written in white)
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventById(id);
    }
    // Catches an exception if no event exists
    catch (e) {
    }
    if (!event) {
      var newEvent = cal.createAllDayEvent(fileno+' - '+description, date).getId();
      row[3] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(fileno+' - '+description);
      event.setAllDayDate(date);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet and restore formulas in first row
  range.setValues(data);
  sheet.getRange(3, 2, 1, 3).setFormulas(formulas);
}

Jack Brown · Accepted Answer · 2018-01-11T22:21:09.120

You can add the following code to the loop to skip invalid dates

if(date.toString() == "Invalid Date")
      continue;

Basically, the continue statement causes the loop to skip that iteration.

NOTE: This works for blank or typo in dates (But have not tested for all possible outcomes)

Your final code will look something like:

function exportEvents() {
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(3, 2, 3, 4); // Range: Key Deadlines Description and Dates (B3:E6)
  var fileno = sheet.getSheetName();
  var data = range.getValues();
  var calID = "[calIDinformation]@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calID);
  var formulas = sheet.getRange(3, 2, 1, 3).getFormulas(); // Snagging one line of formulae which get broken in this process; tofix?
  for (i=0; i<data.length; i++) {
    var row = data[i];
    var description = row[0];     // First column (B - "Description")
    var date = new Date(row[2]);  // Third column (D - "Date")
    if(date.toString() == "Invalid Date")
          continue;                   // Skip remaining loop for this iteration

    var id = row[3];              // Fourth column (E - Dates, written in white)
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventById(id);
    }
    // Catches an exception if no event exists
    catch (e) {
    }
    if (!event) {
      var newEvent = cal.createAllDayEvent(fileno+' - '+description, date).getId();
      row[3] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(fileno+' - '+description);
      event.setAllDayDate(date);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet and restore formulas in first row
  range.setValues(data);
  sheet.getRange(3, 2, 1, 3).setFormulas(formulas);
}

Edit:

To evaluate blanks and Typos separately you can insert a code like:

 if(date.toString() == "Invalid Date"){
          if(row[2].trim() == ""){ //if the cell is blank skip it
            continue;
          } else {                 //If the date entered is wrong, highlight it red
            range.getCell(i+1,3).setBackground("Red") 
            continue;
          }
 }

This will skip over blanks and highlight the cells (red) with the invalid dates.

That definitely works for the blank dates; thank you! Any idea on how to address the "bad dates"? I looked at the page that you sent and suspect that "Break" instead of "Continue" might be right (maybe "Browser.msgBox('Bad Date Detected') && Break;"), but obviously searching for "Invalid Date" finds both blank and invalid; would that be a change to that 'if' statement or should I look to nest an 'if' and if so what would be a test that only gets invalid and not blank? — Databoy2k, Jan 11 '18 at 20:40
@Databoy2k Please have a look at my edit. If you rather want the code to stop when it encounters a bad date, just modify the else statement. — Jack Brown, Jan 11 '18 at 21:01

Google Sheet Data to Calendar - Handling Empty or Incorrect Dates

1 Answers1