exportGcalEvents.gs

// Export gCal events to gSheet
//
// Saves cal events between 2 dates to a sheet
// Calculates event duration by hour as a float (1hr 30m = 1.5)
// ** Open a google sheet > Tools > Script Editor > Paste this > Enter your own params > Save > Run

function exportGcal(){
  // PARAMS
  // ------
  // myEmail  : string
  //            gcal owner email address
  // startDate: string
  //            full date for start of search
  // endDate  : string
  //            full date for end of search
  // ------
  
  var myEmail = "[email protected]";
  var startDate = "January 1, 2018 00:00:00 CST"
  var endDate = "December 31, 2018 23:59:59 CST"
    
  
  var cal = CalendarApp.getCalendarById(myEmail);
  var events = cal.getEvents(new Date(startDate), new Date(endDate));
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Clear out the sheet (technically optional)
  sheet.clearContents();  

  // Create header
  var header = [["cal_address", "event_title", "event_description", "event_location", "event_start", "event_end", "calc_duration", "visibility", "date_created", "last_updated", "my_status", "created_by", "event_allday", "event_recurring"]]
  var range = sheet.getRange(1,1,1,14);
  range.setValues(header);

  // Loop events, start writing on row 2 (i+2)
  for (var i=0; i < events.length; i++) {
    var row = i+2;
    var calcDuration = '';
 
    // Map event details to header column
    var details=[[myEmail,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), calcDuration, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getmy_status(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent()]];
    var range=sheet.getRange(row,1,1,14);
    range.setValues(details);

    // Calc duration as a float
    var cell=sheet.getRange(row,7);
    cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
    cell.setNumberFormat('.00');
  }
}