A Timesheet to Google Calendar Importer (in Perl)

Somebody once told me that a good engineer / scientist is extremely lazy. He HAS to be extremely otherwise he isn’t good. And I like being lazy (read: I love to automate repetitive tasks).

Working as a contractor, we have to fill in timesheets which have to be approved and so. Now, I also like to view in my agenda when I’ve worked and how long. This information is important at the end of the month when creating an invoice (which state the number of days I’ve worked).

The project management tool at my current job, sends out emails when a certain period has been approved by my manager. The email body looks somewhat like this:

These emails all get sorted in my Courier IMAP email server into a specific mailbox, e.g. ‘INBOX.Moretrix.Timesheets’.

My agenda is currently held at Google Calendar, which is an excellent tool for creating private and public agenda’s.

The programming language I’ve used is, of course, Perl. In Perl, there are easy to use modules like Net::IMAP::Simple and Net::Google::Calendar which are going to help us building this automated process.

We will need to gather some information, before we start writing our code. The following is required:
– IMAP user login account
– IMAP password
– IMAP mailbox
– GMail user account
– GMail password
– Google Calendar name (you need to create a separate calendar in Google Calendar, I’ve called mine ‘Business’

The header of script shows which modules to use:

This part is pretty basic and requires no explanation.

The following part will try to connect to the IMAP server and initialize a connection to the Google Calendar.

Still no rocket science, we use the modules like they are described in their perldoc pages, and finally we search for the calendar we want to edit in Google Calendars.

So at this point we have an IMAP connection and a connection to Google Calendars. Now we will loop through all the messages in the configured IMAP folder and start parsing the messages.
We will only need the ‘TOTAL’ lines with all the working hours and the line stating ‘Week Ending’. The latter is used to calcute the dates of the working hours.
Since this IMAP folder also contains other emails than the timesheet emails, we’ll refine our search procedure by taking only those emails that match a certain subject.

At this point, we know that the mail message number (which is in $message) is an email that we need to parse.
Now we will grab the information required from the body of the email and start the calculation of the dates.

In the next part, we cleanup the @totals array, since we don’t need the days Sunday and Saturday, nor do we need the grand total displayed in the email.

The comments in the code explain enough.
Now that we have our totals (which are the values per day), we can loop through the days and create the appropriate calendar items:

Leave a Reply

Your email address will not be published. Required fields are marked *