Google Calendar + ruby = timesheets

I use Google Calendar 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

Hey Aleks,

I use Google Calendar 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

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

···

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

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

···

--
Posted via http://www.ruby-forum.com/.