AppsScript: total hours of events with same name.

hoursSameName.js

// add menu
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name:"Calcular Horas", functionName: "calculateHours"}];
  ss.addMenu("Hours", menuEntries);
  // calcular al iniciar
  calculateHours();
}

function count_hours(cal_id, event_name){
  var hours = 0;
  var cal = CalendarApp.getCalendarById(cal_id);
  var this_year = new Date(2013,0,1);
  var now = new Date()
  var events = cal.getEvents(this_year, now, {search: event_name});
  
  for ( i = 0 ; i < events.length ; i++){
    var event = events[i];
    if ( event_name.toLowerCase() == event.getTitle().toLowerCase() ) {
      //Logger.log(event.getTitle());
      var start = event.getStartTime() ;
      var end =  event.getEndTime();
      start = new Date(start);
      end = new Date(end);
      hours = hours + ( end - start ) / ( 1000 * 60 * 60 );
    }
  }
  
  var cal_name = cal.getName();
  // retorna el nombre del calendario y numero de horas del evento
  return [cal_name, hours];
}

function hours_in_events(events){
  var hours = 0;
  for ( i = 0 ; i < events.length ; i++){
    var event = events[i];
    Logger.log(event.getTitle());
    var start = event.getStartTime() ;
    var end =  event.getEndTime();
    start = new Date(start);
    end = new Date(end);
    hours = hours + ( end - start ) / ( 1000 * 60 * 60 );
  }
  return hours;
}

function authorize() {
  var oauthConfig = UrlFetchApp.addOAuthService("calendar");
  var scope = "https://www.googleapis.com/auth/calendar";
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oauthConfig.setAuthorizationUrl("https://accounts.google.com/OAuthAuthorizeToken");    
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");  
}

/* 
 * Count hours of events with same name
 */
function countHours(calId, eventName){
  authorize();
  var cal = CalendarApp.getCalendarById(calId);
  var key = "...";
  var query = encodeURIComponent(eventName); 
  calId = encodeURIComponent(calId);
  var params = {
    method: "get",
    oAuthServiceName: "calendar",
    oAuthUseToken: "always",
  };
  var url = "https://www.googleapis.com/calendar/v3/calendars/"+
    calId+"/events?q=" + query + "&key=" + key;
  var request = UrlFetchApp.fetch(url, params);
  var response = Utilities.jsonParse(request.getContentText());
  //Logger.log(response);
  var cal_name = response.summary;
  var items = response.items;
  var start, end;
  var hours = 0;
  for ( i = 0 ; i < items.length ; i++){
    if ( items[i].status != "cancelled" ){
      if ( items[i].summary == eventName ){
        start = items[i].start.dateTime;
        end =  items[i].end.dateTime;
        start = new Date(start.replace(/-/g,'/').replace(/[A-Z]/,' ').substr(0,19) );
        end = new Date(end.replace(/-/g,'/').replace(/[A-Z]/,' ').substr(0,19));
        hours = hours + ( end - start ) / ( 1000 * 60 * 60 );
      }
    }
  }
  // retorna el nombre del calendario y numero de horas del evento
  return [cal_name, hours];
}

function calculateHours(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var id_cal_pos = 1;
  var event_name_pos = 2;
  var cal_name_pos = 1;
  var total_hours_pos = 4;
  var s = ss.getSheets()[0];
  var rows = s.getDataRange();
  var nRows = rows.getNumRows();
  var values = rows.getValues();
  // from second row
  for ( var i = 1; i < nRows ; i ++){
    var row = values[i];
    var cal_hours = count_hours(row[id_cal_pos], row[event_name_pos]);
    var h = cal_hours[1];
    var cal_name = cal_hours[0];
    s.getRange(i+1, cal_name_pos).setValue(cal_name);
    s.getRange(i+1, total_hours_pos).setValue(h);
  }
}