Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I'm looking to see or get a weekly summary of the number of hours per event in a Google Calender.

How can I get these hours totalled up?

share|improve this question

migrated from superuser.com Mar 16 '11 at 13:13

This question came from our site for computer enthusiasts and power users.

4 Answers 4

A very nice google calender sidebar (free) gadget. It sums the activities according to tags that you write in the event description.

http://www.theproductivitygame.com/TimeTracker

Tags can be created in the task description in the form:

tags: tagName1, tagName2, etc.

where tagName is the tag/label name that you want to summarise by

share|improve this answer

Time & Activity Report for Google Calendar;

http://www.youcalc.com/apps/1265969469500

Analyze number of activities and amount of time people in your organization spend on different types of activities, customers, projects, etc. The app charts number of activities and hours spent (event duration) across a selected date range and lets you filter down to events with specific owners, specific participants, specific words in the event title, etc.

(There's a few other options listed here; http://www.google.com/support/forum/p/Calendar/thread?tid=4cdbc3495fbd006a&hl=en)

Hope this helps.

share|improve this answer
up vote 0 down vote accepted

finally I used google apps script over a google spreadsheet, the code in a github gist .

// 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);
}
}
share|improve this answer
    
And when that link no longer works? –  Al E. Aug 27 '13 at 14:37
    
@AlEverett nothing is forever. I preferred put the code in a github gist, because I make updates over the code. –  juanpablo Aug 27 '13 at 15:21
    
this was very helpful in getting started -- thanks for the gist! Just need to know that need to create a spreadsheet where Calendar ID is in B2 and Meeting name is in B3 –  Ultrasaurus Jul 27 at 17:55

You can use BlackRabbit to get your total hours or to download the timesheet as csv

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.