# Google Calendar + ruby = timesheets

**URL:** <https://www.ruby-forum.com/t/google-calendar-ruby-timesheets/65240>\
**Category:** Ruby\
**Created:** [August 17, 2006, 12:31am UTC](https://www.ruby-forum.com/t/google-calendar-ruby-timesheets/65240 "2006-08-17T00:31:13Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Aleks\_Kissinger](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@Aleks\_Kissinger](https://www.ruby-forum.com/u/Aleks_Kissinger)\
**Post date:** [August 17, 2006, 12:31am UTC](https://www.ruby-forum.com/t/google-calendar-ruby-timesheets/65240/1 "2006-08-17T00:31:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Aleks\_Kissinger](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@Aleks\_Kissinger](https://www.ruby-forum.com/u/Aleks_Kissinger)\
**Post date:** [August 17, 2006, 7:23pm UTC](https://www.ruby-forum.com/t/google-calendar-ruby-timesheets/65240/2 "2006-08-17T19:23:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Aleks\_Kissinger](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@Aleks\_Kissinger](https://www.ruby-forum.com/u/Aleks_Kissinger)\
**Post date:** [August 18, 2006, 4:57pm UTC](https://www.ruby-forum.com/t/google-calendar-ruby-timesheets/65240/3 "2006-08-18T16:57:44Z")

</div>

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](http://www.cabochon.com/~stevey/blog-rants/win32-ruby-scripting.html)

Chris