9

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?

6

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.

The script searches a specific calendar for events within a defined date range and sums up the hours and days that these events have been registered as. It fills the Sheet which is associated to this script with

  • a summary of "Same Name" Events accumulated days and hours
  • a list of each event that was captured with its associated start date, end time and durration in hours.

Steps

1. Go to your google Drive and start a new Empty SpreadSheet.
2. Go to the Menu: Tools --> Script Editor.
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** --> Click OK
6. Modify the gbl... vars to suit your interest.
7. Go to the Menu --> Run --> RunMe
8. An Authorization request will popup, (only the first time you run it)
  8a. Click Review Permissions
  8b. Select your google account, click continue
  8c. It will say "This App isn't verified", click Advanced, then Goto <project name> (unsafe)
  8d. It will tell you which permissions are required, Click Allow
9. If all went well you should be able to return to the Sheet and see the results.  
10. For easy Access from the sheet add the function within the Menu: Tools -- > Macros --> Import

---- Functions ----
Run --> RunMe : Runs Using the Defined Range
Run --> RunLast24h : Runs using the last 24h as date range
Run --> RunToday : events from Today
Run --> RunYesterday: events from Yesterday

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

  • Took a little massaging to get it to do exactly what I want, but this is working for me. – MBraedley Sep 28 '20 at 17:27
  • @MBraedley Glad it worked out for you, If you find the time, comment on the Gist what mods/massages you required to get it working to your needs to be able to improve it for the next person. – Pau Coma Ramirez Sep 29 '20 at 7:48
  • 1
    I've put it in this gist. I created a calendar just to track which days I'm in the office, and which days I'm working from home, so I don't really care about hours, just days or fractions thereof, so I added some more logic to tabulate the totals for the various events (right now those being "Home", "Office", and "Vacation", although I'll probably be adding "Work Travel" as well). – MBraedley Oct 1 '20 at 1:47
2

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

Later on i found a cute solution : GTimeReport

  • 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);
}
}
  • 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
  • I cannot get this to work, what would be the "correct" calendar ID? has it changed since this code was created? – macutan Oct 23 '20 at 19:37
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

  • 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.