Script to read Google Calendar Events and Count total Hours

gglCalEventsOnSpreadSheet.gs

var gblFromYear = 2015;
var gblToYear = 2015;
var gblFromMonth = 01;
var gblToMonth = 04;
var gblFromDay = 01;
var gblToDay = 01;

var gblCalendarName = "Work";
var gblEventQuery = "TT";

function RunMe(){
  fillMySheetWithCalendarEventDetails(
    gblCalendarName,
    gblEventQuery,
    new Date(gblFromYear,gblFromMonth, gblFromDay), 
    new Date(gblToYear,gblToMonth,gblToDay)
  );
}
function fillMySheetWithCalendarEventDetails(myCalendarName,myEventName,myFromDate,myToDate){

  var mySheet = SpreadsheetApp.getActiveSheet();
  var events = getMyCalendarEventsTime(myCalendarName,myEventName,myFromDate,myToDate)
  var range=mySheet.getRange(mySheet.getActiveCell().getRow(),mySheet.getActiveCell().getColumn(),events.length,5);
  range.setValues(events);

}
function getMyCalendarEventsTime(myCalendarName,myEventName,myFromDate,myToDate){
  var myStartStart = "";
  var myDetails = [];
  var myRows = 0;                  
  myDetails[myRows++] = ["Event","Date", "Start Time", "End Time", "Durration"];
  Logger.log("%s <-> %s", myFromDate.toISOString(), myToDate.toISOString());
  var myCalId = getCalendarKey(myCalendarName);
  Logger.log(myCalId);
  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;
        }
      }
    } else {
      Logger.log('No calendars found.');
    }
    pageToken = calendars.nextPageToken;
  } while (pageToken);
  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;
  }
}
/*
**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.  
*/