Script to read Google Calendar Events and Count total Hours

gglCalEventsOnSpreadSheet.gs

/*
**Steps**
  1. Go to your google Drive and start a new Empty SpreadSheet.
  2. Go to the Menu: Tools --> Script Editor --> Blank Document.
  3. Copy+paste the code into the new Code.gs and Save the File
  4. Go to the Menu: Resources --> Advanced Google Services
    5. Scroll till you find the **Calendar API** and turn it **ON** 
    6. Click On the Link to the **Google Developers Console.** *a new tab will open.*
    7. In the SearchBox type Calendar and click on Calendar API and enable it.
    8. Once enabled return to the window with the code and click OK to return.
  9. Modify the gbl... vars to suit your interest.
  10. Go to the Menu --> Run --> RunMe
  11. An Authorization request will popup, Accept it.
  12. If all went well you should be able to return to the Sheet and see the results.  
  
  Result looks like
Event	            Date	      Start Time	End Time	Durration
Event's name 1  	2016/12/14	20:00	      22:00	    2
Total		          2016/12/17	2016/12/17	          2
Event	            Date	      Start Time	End Time	Durration
Event's name 2  	2016/12/15	1:00	      4:00	    3
Total		          2016/12/17	2016/12/18	          3
  
*/

// year from 1
var gblFromYear = 2016;
var gblToYear = 2016;
// month from 0
var gblFromMonth = 11;
var gblToMonth = 11;
// days from 1
var gblFromDay = 12;
var gblToDay = 18;
// interval is [gblFromYear.gblFromMonth.gblFromDay, gblToYear.gblToMonth.gblToDay)

var gblCalendarName = "set CalendarSummary";
var gblEventQueries = ["Event's name 1", "Event's name 2", "etc.", "this is breaker element! don't delete, just ignore it"]

// for breaker
var MAGIC_NUMBER = 1;

function RunMe(){
  for (var i = 0, iGblEventQuery = gblEventQueries[0], offset = 0; i < gblEventQueries.length; iGblEventQuery = gblEventQueries[++i]) {
    offset += fillMySheetWithCalendarEventDetails(
      gblCalendarName,
      iGblEventQuery,
      new Date(gblFromYear,gblFromMonth, gblFromDay), 
      new Date(gblToYear,gblToMonth,gblToDay),
      offset
    ); 
  };
}

function fillMySheetWithCalendarEventDetails(myCalendarName,myEventName,myFromDate,myToDate, offset) {
  var mySheet = SpreadsheetApp.getActiveSheet();
  var events = getMyCalendarEventsTime(myCalendarName,myEventName,myFromDate,myToDate);
  if (events.length > MAGIC_NUMBER) {
    var range=mySheet.getRange(mySheet.getActiveCell().getRow() + offset,mySheet.getActiveCell().getColumn(),events.length,5);
    range.setValues(events);
    range = mySheet.getRange(mySheet.getActiveCell().getRow() + offset,mySheet.getActiveCell().getColumn(),1,5);
    range.setBackground("#c9daf8");
    range = mySheet.getRange(mySheet.getActiveCell().getRow() + offset + events.length - 1,mySheet.getActiveCell().getColumn(),1,5);
    range.setBackground("#ECECEC");
  }
  return events.length;
}

function getMyCalendarEventsTime(myCalendarName,myEventName,myFromDate,myToDate){
  var myStartStart = "";
  var myDetails = [];
  var myRows = 0;                  
  myDetails[myRows++] = ["Event","Date", "Start Time", "End Time", "Durration"];
  Logger.log(myFromDate.toISOString(), " <-> ", myToDate.toISOString(), myCalendarName);
  var myCalId = getCalendarKey(myCalendarName);
  Logger.log("myCalId = ", myCalId);
  if (!myCalId) {
    return [];
  }
  var myEvents = Calendar.Events.list(myCalId, {
    timeMin: myFromDate.toISOString(),
    timeMax: myToDate.toISOString(),
    q: myEventName,
    singleEvents: true,
    orderBy: 'startTime',
    maxResults: 100
  });
  var myTotalHours = 0;
  if (myEvents.items && myEvents.items.length > 0) {
    for (var i = 0; i < myEvents.items.length; i++) {
      var event = myEvents.items[i];
      if (event.start.date) {
        // All-day event.
        var start = parseDate(event.start.date);
        Logger.log('%s (%s)', event.summary, Date.parse(event.start.date).toLocaleDateString());
      } else {
        var myStartDate = new Date(getDateFromIso(event.start.dateTime));
        var myEndDate = new Date(getDateFromIso(event.end.dateTime));
        var myDiffHours = ((myEndDate - myStartDate)/(1000 * 60 * 60));
        Logger.log('%s (%s) : %s hours', event.summary, Utilities.formatDate(myStartDate,"GMT+1","yyyy/mm/dd"), myDiffHours);
        if (myStartStart == '')
          myStartStart = Utilities.formatDate(myStartDate,"GMT+1","YYYY/MM/DD");
        myDetails[myRows++] = [event.summary,Utilities.formatDate(myStartDate,"GMT+1","YYYY/MM/dd"),
                               Utilities.formatDate(myStartDate,"GMT+1","HH:mm"),
                               Utilities.formatDate(myEndDate,"GMT+1","HH:mm"),myDiffHours];
        myTotalHours = myTotalHours + myDiffHours;
        myDetails[myRows] = ["Total","",
                               Utilities.formatDate(myStartDate,"GMT+1","YYYY/MM/dd"),
                               Utilities.formatDate(myEndDate,"GMT+1","YYYY/MM/dd"),myTotalHours];
      }
    }
  } else {
    Logger.log('No events found.');
  }
  Logger.log("Total Hours : %s", myTotalHours);
  //Logger.log(myDetails);
  return myDetails;
}
function getCalendarKey(myKey) {
  var calendars, pageToken;
  var myCalId = '';
  do {
    calendars = Calendar.CalendarList.list({
      maxResults: 100,
      pageToken: pageToken
    });
    if (calendars.items && calendars.items.length > 0) {
      for (var i = 0; i < calendars.items.length; i++) {
        var calendar = calendars.items[i];
        if (calendar.summary==myKey){
          myCalId = calendar.id;
          return myCalId;
        }
      }
    }
    pageToken = calendars.nextPageToken;
  } while (pageToken);
  Logger.log('No calendars found.');
  return myCalId;
}
// Function posted by PAUL SOWDEN
// http://delete.me.uk/2005/03/iso8601.html
function getDateFromIso(string) {
  try{
    var aDate = new Date();
    var regexp = "([0-9]{4})(-([0-9]{2})(-([0-9]{2})" +
        "(T([0-9]{2}):([0-9]{2})(:([0-9]{2})(\.([0-9]+))?)?" +
        "(Z|(([-+])([0-9]{2}):([0-9]{2})))?)?)?)?";
    var d = string.match(new RegExp(regexp));

    var offset = 0;
    var date = new Date(d[1], 0, 1);

    if (d[3]) { date.setMonth(d[3] - 1); }
    if (d[5]) { date.setDate(d[5]); }
    if (d[7]) { date.setHours(d[7]); }
    if (d[8]) { date.setMinutes(d[8]); }
    if (d[10]) { date.setSeconds(d[10]); }
    if (d[12]) { date.setMilliseconds(Number("0." + d[12]) * 1000); }
    if (d[14]) {
      offset = (Number(d[16]) * 60) + Number(d[17]);
      offset *= ((d[15] == '-') ? 1 : -1);
    }

    offset -= date.getTimezoneOffset();
    time = (Number(date) + (offset * 60 * 1000));
    return aDate.setTime(Number(time));
  } catch(e){
    return;
  }
}