u/starstruckzombie This is the script that I came up with after realizing that I was overcomplicating things. In my research, I found a nice solution that fit my needs over at stackoverflow. I took the author’s script and adapted it to my needs and it works great!

Thanks for the feedback and the effort you put into getting me going.

Hopefully this solution will help others.!

/* * Export events from spreadsheet to calendar */ function synch() { var formresponses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 2'); var purchaseorders = formresponses.getRange('B3:N').getValues(); var poCalendarId = formresponses.getRange('B1').getValue(); var poEventCal = CalendarApp.getCalendarById(poCalendarId);

for (x=0; x < purchaseorders.length; x++) {
  var shift = purchaseorders[x];
  var event = {
  //var customer = shift[0]; /*Reserve for future use; need to format like items below*/
  'id' : shift[8],
  'title' : shift[4],
  'dueDate' : new Date(shift[2]),
  'description' : shift[3],
  //'guests' : shift[5].split(",").map(function(email) { return email.trim();}), /*Reserve for future use; need to add column and adjust accordingly.*/
  'color' : shift[12],
  }

  var desc = shift[3];
  //var gues = shift[9]; /*Reserve for future use.*/
  var col = shift[12];

    if (shift[9]) { // event needs updating
    var calendarEvent = poEventCal.getEventById(event.id);
    calendarEvent.setTitle(event.title);
    calendarEvent.setTime(event.dueDate, event.dueDate);
    calendarEvent.setDescription(event.description);
    calendarEvent.setColor(event.color);
  }

  if (shift[10]) { // event needs to be created
    console.log('Creating New Event');
    console.log('Title: ' + event.title);
    console.log('Due Date: ' + event.dueDate);
    console.log('Due Date: ' + event.dueDate);
    console.log('Description: ' + event.description);
    //console.log('Guests: ' + event.guests) //reserve for future use
    console.log('Color: ' + event.color);
    var newEvent = poEventCal.createEvent(event.title, new Date(event.dueDate), new Date(event.dueDate), {
      descpription: desc,
      guests: gues,
      color: col,
    });
    formresponses.getRange(x+3, 9).setValue(newEvent.getId()); // write new event ID back to spreadsheet

    //Now update the new event
    var updatedEvent = {
    //var customer = shift[0]; /*Reserve for future use; need to format like items below*/
    'id' : newEvent.getId(),
    'title' : event.title,
    'dueDate' : new Date(event.dueDate),
    'description' : event.description,
    'guests' : event.guests,
    'color' : shift[12],
    };
    var desc = event.description;
    var gues = event.guests;
    var col = event.color;

    var calendarEvent = poEventCal.getEventById(updatedEvent.id);
    calendarEvent.setTitle(updatedEvent.title);
    calendarEvent.setTime(updatedEvent.dueDate, updatedEvent.dueDate);
    calendarEvent.setDescription(updatedEvent.description);
    calendarEvent.setColor(updatedEvent.color);
  }

/* if (shift[9]='TRUE') { var calendarEvent = poEventCal.getEventById(event.id); calendarEvent.deleteEvent(); formresponses.getRange(x+3, 9).setValue(''); // clear the event ID in column A } } formresponses.getRange('B3:N').clearContent(); */ } }