Google Calendar + ruby = timesheets

Ruby
1

I use Google C. to manage my hours and excel to push out
timesheets, so I wrote a little ruby script that extacts all the
events from a google cal and dumps them to CSV:

I thought someone else here might like it. Just go to calendar
settings and paste the private “ICAL” link into HOUR_CAL.

require ‘open-uri’
require ‘date’

HOUR_CAL = ### url goes here

events = []
ical = open(HOUR_CAL)
ical.each_line do |line|
line.chomp!
if line =~ /BEGIN:VEVENT/
events << {}
elsif line =~ /DT(START|END);TZID=([^:]):(.)/
events.last[$1.downcase] = DateTime.parse($3)
elsif line =~ /SUMMARY:(.*)/
events.last[‘task’] = $1
end
end

ical.close

puts %(“task”,“date”,“in”,“out”,“hours”)
events.each do |e|
t = e[‘task’].gsub(’"’,’’)
d = e[‘start’].strftime(’%m/%d/%Y’)
st = e[‘start’].strftime(’%I:%m %p’)
et = e[‘end’].strftime(’%I:%m %p’)
diff = (e[‘end’]-e[‘start’])*24.0
puts %("#{t}","#{d}","#{st}","#{et}","#{diff}"\n)
end

  • aleks
2

Hey Aleks,

On Thu, Aug 17, 2006 at 07:28:11AM +0900, Aleks Kissinger wrote:

HOUR_CAL = ### url goes here
events.last[‘task’] = $1
et = e[‘end’].strftime(’%I:%m %p’)
diff = (e[‘end’]-e[‘start’])*24.0
puts %("#{t}","#{d}","#{st}","#{et}","#{diff}"\n)
end

  • aleks

This is very cool! Though I wouldn’t parse ical format myself, or
generate my own CSV… You could run in to problems with ical parsing.
IIRC the RFC says each field could be multiple lines, and your regex
might not catch that.

Try this script out (you’ll need the icalendar package):

require ‘rubygems’
require ‘icalendar’
require ‘open-uri’
require ‘csv’

HOUR_CAL = ### url goes here

calendars = Icalendar.parse(open(HOUR_CAL))
CSV::Writer.generate($stdout) do |csv|
csv << [‘task’, ‘date’, ‘in’, ‘out’, ‘hours’]
calendars.each do |calendar|
calendar.events.each do |e|
csv << [ e.summary,
e.dtstart.strftime(’%m/%d/%Y’),
e.dtstart.strftime(’%I:%m %p’),
e.dtend.strftime(’%I:%m %p’),
(e.dtend - e.dtstart)*24.0
]
end
end
end

–Aaron

3

You could also use w32ole to automate the import into excel too ! Here’s
an example :

http://www.cabochon.com/~stevey/blog-rants/win32-ruby-scripting.html

Chris