Script: How to skip Google Sheets rows with incomplete data sets

Question

I'm tracking my local legislative session for work, which requires monitoring and adjusting calendar events for 300+ bills and counting (800+ by the end of the session). I've compiled a script from a few sources, and the sheet is saving me an insane amount of time.

It copies the calendar ID of an event to avoid creating duplicate events. It's working very well save for one thing - for any bill that doesn't have a date/time specified in columns 8 & 9 respectively, the script creates a calendar event on Wednesday 0 1969.

I want the script to:

A)simply skip processing all rows in which the cell in column 9 is empty, or

B)get the range of a filter view and only create events for those rows which are visible.

I'd prefer B, if possible. I've tried a few things, but I'm at a loss. While I can manually copy paste only the calendared events to another sheet and run the script there with no issues, I'd rather it be simpler than that. Any help would be greatly appreciated.

The code as it stands:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Calendared Bills",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};



function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 2;  
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "redactedaddress@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (i=0; i<data.length; i++) {
    if (i < headerRows) continue; 
    var row = data[i];
    var date = new Date(row[8]);  
    var title = row[2];           
    var tstart = new Date(row[9]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[9]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[4];
    var desc = row[15];
    var id = row[3];  
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {

    }
    if (!event) {

      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc,}).getId();
      row[3] = newEvent; Utilities.sleep(1000);  
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
    }
    debugger;
  }
  range.setValues(data);
}

Answer 1

You can check if the date object is valid date, using date.valueOf() function and isNaN() function as shown here. The valueOf() function returns a number if the date is valid and you can check the number using isNaN() function

Finally, you can use continue to skip that particular iteration when the date is invalid. Like so:

var tstart = new Date(row[9])
if(isNaN(tstart.valueOf()))
  continue;

Your final code would look like this:

function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 2;  
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "redactedaddress@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (i=0; i<data.length; i++) {
    if (i < headerRows) continue; 
    var row = data[i];
    var date = new Date(row[8]);  
    var title = row[2];           
    var tstart = new Date(row[9]);
    if(isNaN(tstart.valueOf()))
      continue;                // skip row, go to the next row
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[9]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[4];
    var desc = row[15];
    var id = row[3];  
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {

    }
    if (!event) {

      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc,}).getId();
      row[3] = newEvent; Utilities.sleep(1000);  
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
    }
    debugger;
  }
  range.setValues(data);
}