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.

6 Answers 6

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
    
This gadget is no longer available (I'm the author). –  David Braun Jan 2 at 20:13

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 1 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 '14 at 17:55

I wrote up a script in Google Script, which I would like to contribute in case it helps someone else stumbling across this question. I adapted some code from the Google sample code on Calendar API.

Steps

  1. Go to your Google Drive and start a new empty spreadSheet.
  2. Go to the Menu: ToolsScript EditorBlank Document.
  3. Copy + paste the code into the new Code.gs and save the file
  4. Go to the Menu: ResourcesAdvanced 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 search box 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: RunRunMe
  11. An authorisation request will popup—accept it.
  12. If all went well you should be able to return to the sheet and see the results.

Here is the link to the script: http://gist.github.com/paucoma/7b7b6cecddfa79d25531#file-gglcaleventsonspreadsheet-gs

share|improve this answer

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

share|improve this answer

Based on my need for, i discovered you're question.

Later on i found a cute solution : GTimeReport

share|improve this answer
    
If you affiliated with the product please disclose this in your answer and explain how the product solves the problem. –  jonsca Mar 6 at 0:46

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.