Part 2-The Spreadsheet Present
As we continue our mission from 'The spreadsheet is dead long live the spreadsheet ' series, our task is to evolve our old style, quick and dirty solution into something that takes advantage of the advanced capabilities available today on the web, including EditGrid.
Continued from part 1 the speadsheet past .
Ok lab coats on everyone and lets dive straight in. First lets make this more realistic, rather than just having a single calendar called chargeable we are more likely to have different charging calendars for different projects or clients. Given the nature of our work I will choose to use projects rather than clients, as that tends to better represent our current invoicing scheme. Your milage may vary with this approach and there are other possibilities as I mention in the next part of this series. So lets add the calendars in, as many as we need , one for each project (normally this is the GApps admin's role, but it could be anyone).
Next we must grab the URL of each the private calendar feeds and paste them into our new script, in our case I will place them into a collection called feedUrls. Here is the new script. Notice we are importing the EditGrid classes (java based) derived from the WSDL file, your script may do this dynamically at runtime via the WSDL url in which case things may be a little simpler.
EditGrid have chosen a SOAP based API initially, this can be a double edged sword in that once you have your local class model, operations can be made very simple for the programmer with little much reference to documentation. On the other hand it can be tricky to get setup in some cases, particularly if your more web based and familiar with REST like APIs. I have some issue here initially as the default SOAP library used in Groovy could not interpret the WSDL file. This file describes what services and functions are available to the application. I don't really want to get into any religious arguments about which is better REST or SOAP based RPC models as that will just confuse the issue. EditGrid have stated however that they will be introducing a full REST based API in the near future so both parties can benefit. At the end of the day EditGrid does actually provide an API unlike most of the Other web based spreadsheets out there and that is why we have chosen it here. It is also worth noting that from a read point of view REST is supported as each book is available via a permalink in several different formats including XML.
The script essentially scans each project calendar feed and processes it before appending it to the relevant sheet within a new EditGrid Book (A collection of sheets).
1 import groovy.util.XmlSlurper 2 import java.text.SimpleDateFormat 3 4 import EditGrid.EditGridService 5 import EditGrid.EditGridPort 6 import EditGrid.EditGridServiceLocator 7 import EditGrid.Book 8 import EditGrid.CellValue 9 10 def accountingDept = 'Accounts' 11 def READONLY = 0 12 def READWRITE = 1 13 14 def WSDL = 'http://www.editgrid.com/static/EditGrid.wsdl' 15 def appKey = 'our-editgrid-key' 16 // Googles namespaces for GCal date/time elements 17 def ns = 'http://schemas.google.com/g/2005' 18 def feedUrls = ["http://www.google.com/calendar/feeds/feed1/full", 19 "http://www.google.com/calendar/feeds/feed2/full"] 20 21 def slurper = new XmlSlurper() 22 // Date formatters for processing dates 23 def tf = new SimpleDateFormat("yyyy-MM-dd-HH:mm:ss") 24 def sf = new SimpleDateFormat("yyyy-MM-dd' 'HH:mm:ss") 25 // Helper methods, simplify EditGrid cell creation 26 //def dateCell (id,row,col,val ) { return new CellValue(id,row,col,sf.format(val),sf.format(val))} 27 //def basicCell (id,row,col,val ) { return new CellValue(id,row,col,val,val)} 28 def cl = { id,row,col,val -> 29 if(val instanceof java.util.Date) return new CellValue(id,row,col,sf.format(val),sf.format(val)) 30 if(val instanceof java.math.BigDecimal) return new CellValue(id,row,col,val.toString(),val.toString()) 31 else return new CellValue(id,row,col,val,val) 32 } ; 33 34 // Cretate EditGrid Remote service 35 EditGridService service = new EditGridServiceLocator() 36 // Now use the service to get a stub which implements the SDI. 37 EditGridPort port = service.getEditGridPort() 38 // name for our book 39 def name = "Timesheet-" + tf.format((new Date())) 40 // Create the book to hold the timesheets 41 def bookId = port.createBook(appKey, name, 'Summary') 42 // Step through each Calendar feed and create a sheet for each 43 feedUrls.each(){ uploadSheet(cl,appKey,port,bookId,slurper.parse(it).declareNamespace(gd:ns)) } 44 45 // Its very slow, maybe import is a better option 46 47 // add collaboration, add coeditor 48 port.addCoEditor(appKey, bookId, null, accountingDept,READWRITE) 49 50 // The function that does the feed to sheet conversion 51 def uploadSheet(cl,appKey,port,bookId,feed){ 52 def toHours = 1000 * 60 *60 53 def rate = 45 54 def df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS") 55 def row = 0 56 // Create a sheet for this calendar 57 def id = port.insertSheet(appKey, bookId, feed.title.text() + 'TS', 0) 58 // Add a header row 59 CellValue[] cells = [cl(id,-1,0,'From'), 60 cl(id,-1,1,'Until'), 61 cl(id,-1,2,'Description'), 62 cl(id,-1,3,'Hours'), 63 cl(id,-1,4,'Value')] 64 port.appendRow(appKey, id, cells) 65 // TODO Format header row? 66 // Step through the calendar tasks and process them 67 feed.entry.each(){ 68 row++ 69 // Grab the startdate/time of the task 70 // Notice the splice hack to remove the incompatable time zone info 71 def start = df.parse(it."gd:when"."@startTime".text()[0..-4]) 72 // Grab the enddate/time of the task 73 def end = df.parse(it."gd:when"."@endTime".text()[0..-4]) 74 // Calculate time spent = time finished - time started 75 def diff = (end.getTime() - start.getTime()) / toHours 76 // Calculate charge based on time * rate 77 def cost = diff * rate 78 cells = [cl(id,-1,0,start), 79 cl(id,-1,1,end), 80 cl(id,-1,2,it.title.text()), 81 cl(id,-1,3,diff), 82 cl(id,-1,4,cost)] 83 port.appendRow(appKey, id, cells) 84 } 85 } 86
This new script provides a much more complete solution with the following added features over our first quick and dirty version:
- Multiple project calendar support to record tasks by project
- Multiple timesheets in a collection summarised by project
- Collaboration enabled at script level.
This new version also requires less manual intervention, as collaboration is enabled directly using EditGrid's API, the book of timesheets, when compiled is automatically published to the accountant's or bookeeper's workspace on EditGrid.
Notes
1) We used Groovy here which is java based, groovy's Soap module (based on Xfire) has some namespace issues and complex object constraints that meant I could not use it here. Thus I use Apache's Axis WSDL/SOAP libraries and their handy WSDL2Java tool to enable us to talk to the EditGrid API. Your use of SOAP may vary with your language choice of course.
2) You will need at least 2 EditGrid accounts your own and your accountants..
3) You will need EditGrid's Live WSDL address as shown in the script
4) If you use this script directly or emulate it's pattern, you might find it a little slow, this is due to the fact we are making web based remote calls (append) for every task in each calendar. This is of course very inefficient, but I could not find an 'upload multiple rows' function in the API, something I hope they can add later. One could of course use their upload/import function but that would defeat the longer term objectives of these lab exercises as will come apparent in later parts.
Technorati Tags: accounts, API, billing, EditGrid, Enterprise2.0, GoogleCalendar, groovy, Office2.0, Spreadsheet
