In google calender I have all the times my co-workers work on a project. I have found two scripts to extact the times through the api and have them displayed in a google spreadsheet. The first script, found here http://sellfisch.de/simple-zeiterfassung-mit-google-kalender/ , is exactly what I want, but it only spits out the total sums. In the second script "function caltest3" (at the end of the post) I can change the date range and get the exact days and times each co-worker worked on a project.
My question is how do I integrate the second one with the first script in order to use the simplicity of the first script with its input box asking which month to pull and total sums etc. First script: TimeSheet-Script V0.6 Written by Tobias Sell (c)2012 by Mobfish.net Licensed under Creative Commons (http://creativecommons.org/licenses/by-sa/3.0/) */ //Registration function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [ {name: "General Report", functionName: "createReport"}, {name: "Personal Report", functionName: "personalReport"}]; ss.addMenu("Time Management", menuEntries); } //ReportObject function Report(){ this.startDate=null; this.endDate=null; this.projectReport=new Object(); this.memberReport=new Object(); this.timeEntryTable=new Object(); this.addTimeEntry=function(user, event){ var time=event.getEndTime()-event.getStartTime(); time=time/(1000*60); //Convert milisecs to min (we're not that accurate here) var project=event.getTitle().split("\ ")[0]; project=project.substring(1,project.length); //Adding values to projectReport if(!this.projectReport[project]) this.projectReport[project]=time; else this.projectReport[project]+=time; if(!this.memberReport[user]) this.memberReport[user]=time; else this.memberReport[user]+=time; } } function createReportSheet(report) { var sheet=SpreadsheetApp.getActiveSpreadsheet(); sheet.getRange("a1:e100").clear(); sheet.getRange("a1:e1").merge().setValue("Time Management").setFontSize(24); sheet.getRange("a2").setValue("From:"); sheet.getRange("b2").setValue(report.startDate); sheet.getRange("c2").setValue("To:"); sheet.getRange("d2").setValue(report.endDate); sheet.getRange("a4").setValue("Project").setFontWeight("bold"); sheet.getRange("b4").setValue("Time").setFontWeight("bold"); sheet.getRange("d4").setValue("Member").setFontWeight("bold"); sheet.getRange("e4").setValue("Time").setFontWeight("bold"); SpreadsheetApp.flush(); } function getDates(report){ var sheet=SpreadsheetApp.getActiveSpreadsheet(); if(sheet.getRange("b2").getValue()!=""){ report.startDate=new Date(sheet.getRange("b2").getValue()); report.endDate=new Date(sheet.getRange("d2").getValue()); Logger.log("Load date from sheet"); }else{ var range=Browser.inputBox("What month do you want to report?","08.2012 (Month.Year)", Browser.Buttons.OK); var r=range.split("\."); report.startDate = new Date(r[1], r[0]-1, 1,0,0,0,0); report.endDate=new Date(r[1], r[0],0,23,59,59); } } /* Creates a Report about all accessable calendars with suitable events. */ function createReport() { //Generate Basic Layout (or overwrite it) var report=new Report(); getDates(report); createReportSheet(report); //Create report by interating through calendars var calendars=CalendarApp.getAllCalendars(); //iterate through all shared callendars for(var i=0; i<calendars.length;i++){ var cal=calendars[i]; var events=cal.getEvents(report.startDate, report.endDate); for (var i=0;i<events.length;i++) { //http://www.google.com/google-d-s/scripts/class_calendarevent.html var details=[[events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getEndTime()]]; var row=i+3; var range=sheet.getRange(row,6,1,4); range.setValues(details); } } } //Create report to spreadsheets Logger.log("Creating report"); updateSpreadsheet(report); /* Creates a report filtered by a given calendarname */ function personalReport() { var report=new Report(); getDates(report); createReportSheet(report); var targetCalendar=Browser.inputBox("Which calender shall be used to created report?"); var calendars=CalendarApp.getAllCalendars(); //iterate through all shared callendars for(var i=0; i<calendars.length;i++){ var cal=calendars[i]; if(cal.getName()==targetCalendar){ var events=cal.getEvents(report.startDate, report.endDate); //Iterate through all events for(var j=0; j<events.length;j++){ var event=events[j]; //if relevant, add entry to report if(event.getTitle()[0]=="#"){ report.addTimeEntry(cal.getName(),event); } } } } //Create report to spreadsheets Logger.log("Creating report"); updateSpreadsheet(report); } /* Draws all Values of a Report onto the spreadsheet */ function updateSpreadsheet(report){ var start=5; var sheet=SpreadsheetApp.getActiveSpreadsheet(); //Create PROJECT report range var size=getSize(report.projectReport); var rep=new Array(); var row=0; for(var p in report.projectReport){ rep[row]=new Array(2); rep[row][0]=""+p; rep[row][1]=showTime(report.projectReport[p]); row++; } var cells = sheet.getRange("a"+start+":b"+(start-1+size)); cells.setValues(rep); cells.sort({column: 2, ascending: false}); sheet.getRange("b"+(start+size)).setFormula('=sum(B'+start+':B'+(start-1+size)+')'); //Create MEMBERS report range var size=getSize(report.memberReport); var rep=new Array(); var row=0; for(var p in report.memberReport){ rep[row]=new Array(2); rep[row][0]=""+p; rep[row][1]=showTime(report.memberReport[p]); row++; } var cells = sheet.getRange("d"+start+":e"+(start-1+size)); cells.setValues(rep); cells.sort({column: 5, ascending: false}); SpreadsheetApp.flush(); } /* ----------------------------------------------------------------------------------------- *** Helpermethods *** ----------------------------------------------------------------------------------------- */ /* Calculates the size of an object (number of entries) */ function getSize(object){ var c=0; for(var p in object){ c++; } return c; } /* Shows time in an human readable format */ function showTime(number){ var h=Math.floor(number/60); var m=number%60; var s=h+':'; if(m<10) s=s+'0'; s=s+m; return s; } Second Script function caltest3(){ //http://www.google.com/google-d-s/scripts/class_calendar.html#getEvents // The code below will retrieve events between 2 dates for the user's default calendar and // display the events the current spreadsheet var cal = CalendarApp.getCalendarsByName('test')[0]; var sheet = SpreadsheetApp.getActiveSheet(); var events = cal.getEvents(new Date(2013,11,01), new Date(2013,11,31)); for (var i=0;i<events.length;i++) { //http://www.google.com/google-d-s/scripts/class_calendarevent.html var details=[[events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getEndTime()]]; var row=i+4; var range=sheet.getRange(row,1,1,4); range.setValues(details); -- You received this message because you are subscribed to the Google Groups "Google Spreadsheets API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
