Google Apps Script to take information from Google Sheet and create events in Google Calendar

sheet_to_calendar.gs

// Sheet structure:
// Columns:
// event_id,	city,	start,	end,	location,	title, notes
// leave event_id empty, it will be updated when the script runs
var 
  COL_EVENT_ID = 0, COL_EVENT_ID_ADDRESS = 'A',
  COL_CITY = 1,
  COL_START = 2,
  COL_END = 3,
  COL_LOCATION = 4,
  COL_TITLE = 5,
  COL_NOTES = 6;

function onOpen() {
 SpreadsheetApp.getUi()
   .createMenu('Manage')
   .addItem('Sync to Calendar', 'syncToCalendar')
   
   .addToUi();
}

function syncToCalendar() {
  var events = getData()
  Logger.log('events: ' + JSON.stringify(events));

  var calendars = getCalendars(events);
  
  events.forEach(function (event) {

    if (event.id == "") {
      // new event, create it
      Logger.log('new event, create if');
      var calendarEvent = calendars[event.city].createEvent(
        event.title, new Date(event.start), new Date(event.end), {
        location: event.location,
        description: event.notes
      });
      Logger.log('event created: ' + calendarEvent.getId());
      
      var cellAddress = COL_EVENT_ID_ADDRESS + event.row;
      SpreadsheetApp.getActiveSpreadsheet().getRange(cellAddress)
      .setValue(calendarEvent.getId());
      Logger.log('cell updated');
    } else {
      // existing event, update it
      Logger.log('existing event, update it');
      
      var ev = calendars[event.city].getEventById(event.id)
      Logger.log('retrieved event: '+ev)
        ev.setTitle(event.title)
        .setTime(new Date(event.start), new Date(event.end))
        .setLocation(event.location)
        .setDescription(event.notes);
    }
    
  });
  
  SpreadsheetApp.getUi().alert('Done!');
}

function getCalendars(events) {
  
  
  var cities = [];
  
  events.forEach(function (event) {
    if (cities.indexOf(event.city) < 0 ) {
      cities.push(event.city);
    }
  });
  
  Logger.log('cities: '+ JSON.stringify(cities));
  
  var cityCalendars = {};
  cities.forEach(function(city) {
    var calendars = CalendarApp.getCalendarsByName(city);
    Logger.log('Found %s matching calendars.', calendars.length);
    if (calendars.length == 0) {
      // no such calendar, create one
      var calendar = CalendarApp.createCalendar(city + ' Calendar', {
        summary: 'Schedule for ' + city + '.',
        color: '#'+(Math.random()*0xFFFFFF<<0).toString(16)
      });
      Logger.log('Created the calendar "%s", with the ID "%s".',
                 calendar.getName(), calendar.getId());
      cityCalendars[city] = calendar;
    } else if (calendars.length == 1) {
      cityCalendars[city] = calendars[0]
    } else {
      Logger.log('cannot find or create calendar or so many calendars: ' + calendars);
    }
  });
  
  return cityCalendars;
}

function getData() {
  events = []
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    var event = {};
    
    Logger.log('data[i] ' + JSON.stringify(data));

    event.id = data[i][COL_EVENT_ID];
    event.city = data[i][COL_CITY];
    event.start = data[i][COL_START];
    event.end = data[i][COL_END];
    event.location = data[i][COL_LOCATION];
    event.title = data[i][COL_TITLE];
    event.notes = data[i][COL_NOTES];
    
    event.row = i + 1;
    
    events.push(event);
    Logger.log('event: ' + JSON.stringify(event));
  }
  
  return events
}