life is life

I’ve playing around lately with the Google data API, and also with the VSTO 2005 SE for Office 2007, and it seems that interconnecting these two APIs is simpler than ever!

After spending some time just fiddling around, I headed towards building a simple yet useful [some might disagree with one or both of these affirmations] application. The scenario that I had in mind when I built this sample was the following:

1. I use Outlook Calendar as my main calendar, but from time to time I find myself without my laptop or pocket pc or desktop – this is when Google Calendars comes in handy. I am able to add appointments on the fly, and the Outlook can load my Google Calendar and display it [Tools->Account Settings->Internet Calendars]. The inconvenience is that I ended up in having 2 calendars in Outlook
2. The appointments that I add in Outlook are not updated in the Google Calendar [unless you do it by hand-importing the calendar from Google …], as Outlook sees the Google Calendar as a read-only resource.
3. Google can send SMS notifications to you for upcoming events [which is very useful when you've only got your phone with you on a busy day]

So what I wanted was to be able to work only in Outlook, and then to have the Outlook and Google calendars sync automatically. As far as I’ve seen, there are some solutions on the market that are free and more in the "actually working" category [for example OggSync, but it's not the only one], but the power that you have when you control the code is really fun :-)

So what did I do? I installed VSTO 2005 SE on my machine, I already had Outlook 2007 installed, and I started coding.

The steps were the following:

1.Make a new Office Outlook Add-In project in VS2005

2.Create a new button on the Outlook main toolbar

Office.CommandBarButton button;
GoogleCalendarClient client;
Outlook.Items calendarItems;
Outlook.MAPIFolder calendarFolder;

void CreateMenu()
{
    foreach (Office.CommandBar cb in Application.ActiveExplorer().CommandBars)
    {
        if (cb.Name == "Menu Bar")
        {
            button = (Office.CommandBarButton)cb.Controls.Add(Office.MsoControlType.msoControlButton, Type.Missing, Type.Missing, cb.Controls.Count, true);
            button.Caption = "Sync";
            button.Visible = true;
            button.Enabled = true;
            button.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(OnSync);
        }
    }
}

private void OnSync(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
{
    calendarFolder = Application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderCalendar);
    calendarItems = calendarFolder.Items;
    client = new GoogleCalendarClient(calendarItems, Application);
}

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    CreateMenu();
}

private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}

3. Now we’ve got the button up there on the Menu Bar, and we’ve also hooked up the event handler for the button with the "OnSync" method. Nothing new or interesting so far. The next step is to call the actual sync operation. For this we need to get the list of appointments from both Outlook and Google.

3.1 Get the list of appointments from Outlook, within a [-30 days, +30 days] range from today:

private Microsoft.Office.Interop.Outlook.Application application;private Outlook.Items calendarItems;private Outlook.MAPIFolder calendarFolder; calendarFolder = Application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderCalendar); calendarItems = calendarFolder.Items;foreach (Outlook.AppointmentItem appointment in calendarItems) { if (DateTime.Now.Add(new TimeSpan(30, 0, 0, 0)) > appointment.Start && DateTime.Now.Subtract(new TimeSpan(30, 0, 0, 0)) < appointment.Start) { outlookEvents.Add(new MyEvent( appointment.Subject, appointment.FormDescription.Comment, appointment.Start, appointment.End, TimeSpan.FromMinutes(appointment.ReminderMinutesBeforeStart), "sms"  )); } }

3.2 Get the list of appointments form Google. For this I used the Google .NET APIs:

private string username = "username";private string password = "1_h4x0r_u";private string uri = "http://www.google.com/calendar/feeds/<username>@gmail.com/private/full";private CalendarService calendarService;private EventQuery query;private EventFeed calFeed; calendarService = new CalendarService("TudorSalomie-Application-1");// Create the query object: query = new EventQuery(); query.Uri = new Uri(uri); query.StartTime = DateTime.Now.Subtract(new TimeSpan(30, 0, 0, 0)); query.EndTime = DateTime.Now.Add(new TimeSpan(30, 0, 0, 0));// Tell the service to query: calFeed = calendarService.Query(query);foreach (EventEntry ee in calFeed.Entries) { googleEvents.Add(new MyEvent( ee.Title.Text, ee.Summary.Text, ee.Times.Count > 0 ? ee.Times[0].StartTime : DateTime.MinValue, ee.Times.Count > 0 ? ee.Times[0].EndTime : DateTime.MinValue, new TimeSpan(ee.Reminder.Days, ee.Reminder.Hours, ee.Reminder.Minutes, 0), ee.Reminder.Method.ToString())); }

4. Represent the data in a common format so that we can easily work with it regardless of the format (Google/Outlook). As you might have noticed I have been using in both places a class called MyEvent that is used for my internal representation of an Appointment/Event. The class is more of getter/setter thing, except for the two methods that can transform my internal representation back into Google/Outlook format. The two methods are presented below:

public Outlook.AppointmentItem GetAsOutlookAppointment(Microsoft.Office.Interop.Outlook.Application application) { Outlook.AppointmentItem ai = (Outlook.AppointmentItem)application.CreateItem(Outlook.OlItemType.olAppointmentItem); ai.Start = this.StartTime; ai.End = this.EndTime; ai.Body = this.Description; ai.Subject = this.Title; ai.AllDayEvent = false; ai.ReminderMinutesBeforeStart = this.ReminderTime.Minutes; return ai; }public EventEntry GetAsGoogleAppointment() { EventEntry ee = new EventEntry(); When w = new When(); w.StartTime = this.StartTime; w.EndTime = this.EndTime; w.Reminder = new Reminder(); w.Reminder.Days = this.ReminderTime.Days; w.Reminder.Hours = this.ReminderTime.Hours; w.Reminder.Minutes = this.ReminderTime.Minutes; w.Reminder.Method = Reminder.ReminderMethod.sms; ee.Times.Add(w); ee.Summary.Text = this.Description; ee.Title.Text = this.Title; ee.Reminder = new Reminder(); ee.Reminder.Method = Reminder.ReminderMethod.sms; ee.Reminder.Days = this.ReminderTime.Days; ee.Reminder.Hours = this.ReminderTime.Hours; ee.Reminder.Minutes = this.ReminderTime.Minutes; return ee; }

5. Now we have a list of all the appointments in the Google Calendar, we have one with all those from the Outlook Calendar. The sync part is no biggy – just compare appointments and make sure they appear in both places. I have implemented no way of treating collisions, or of updating just parts of appointments, although I might just do that if I find out I’ll be needing something like that, or if anybody out there requests it.

// mix the outlookEvents with the googleEvents foreach (MyEvent ev in googleEvents) { if (!outlookEvents.Contains(ev)) { Outlook.AppointmentItem ai = ev.GetAsOutlookAppointment(application); ai.Save(); } }foreach (MyEvent ev in outlookEvents) { if (!googleEvents.Contains(ev)) { EventEntry ee = ev.GetAsGoogleAppointment(); calendarService.Insert(new Uri(uri), ee); } }

I think that’s all there is to it :) If you’re interested in the whole code, or even in a setup project feel free to mail me. As I’ve said, I only tested it on Outlook 2007 [oh, and you need the VSTO Runtime in order to be able to run it].

Gotta go … , just received a reminder, of an appointment I set in Outlook, on my phone from Google that I’ve got to go buy some groceries :)

If you want to spread the word: