7

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?

3

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

| improve this answer | |
2

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

Later on i found a cute solution : GTimeReport

| improve this answer | |
  • 1
    If you affiliated with the product please disclose this in your answer and explain how the product solves the problem. – jonsca Mar 6 '15 at 0:46
  • Although this answer could have been answered much more in detail, etc... IT HELPED ME OUT greatly! Terrific little tool. Thanks! – Sibe Jan Mar 8 '17 at 15:27
1

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);
}
}
| improve this answer | |
  • And when that link no longer works? – ale 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
1

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

| improve this answer | |
  • This gadget is no longer available (I'm the author). – David Braun Jan 2 '15 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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