Akom's Tech Ruminations

Various tech outbursts - code and solutions to practical problems

Syncing, Google Calendar, Google Contacts, Blackberry, Outlook, etc Code and Hacks

Posted by Akom • Monday, September 8. 2008 • Category: Code and Hacks

As the CTO of Namaste Holistics and Nutrition 4 Entrepreneurs (see links in nav above), I was dealing with a progressively more dissatisfied CEO, and with good reason.

She is dealing with an overcomplicated email/calendaring/contacts setup that's somewhat... imperfect.

The following is the setup and what was done to improve it.

This was her setup:
Email:
  • 2 Gmail Accounts
  • 2 IMAP Accounts (hosted)
  • All of the above have countless aliases
  • Thunderbird and Blackberry (BIS) as mail clients

Calendar and Contacts
  • Outlook as primary location
  • Synched to Blackberry via USB cable (error prone and often forgotten)
  • Google Mobile sync'ed to Google Calendar (quirky)

Problems with this setup:
  • Email works on both Thunderbird and Blackberry, but Sent mail is not saved using BIS (except for Gmail accounts, because of their special arrangement with Google).
  • Contacts and Calendar are not synced wirelessly and hence frequently go out of sync. Blackberry Desktop crashes during sync to Outlook, frequently (More on this issue and resolution)
  • Calendar needs to be shared with assistants (currently using AirSet), which adds complexity to all this, and this is also manual (relies on windows being up)
  • We could switch to gmail for all mail accounts, but sending email from 'aliases' has the infamous 'On Behalf Of' (Sender header) issue, because Gmail doesn't trust you


In an effort to resolve this we looked for a one-stop solution to sync google everything to the blackberry. The ideal solution would not rely on any desktop-based components, would be fully OTA (wireless), and I guess would have run on the blackberry itself. I certainly didn't want a BES server as neither Exchange nor Outlook can handle the amount of mail she deals with (yes we've tried).

We tried GooSync (its advertising had the most bold of promises), but they don't even have a Blackberry client - they use another company's product called SyncJe, which is a separate purchase. Out that went. We tried several other products (desktop based) - most didn't work or were disappointing in other ways.

Eventually we decided to make the most of the situation and improve what can be improved. First of all, we ditched Outlook in favor of Google Calendar, since the Google Mobile Sync for blackberry is the one product that actually exists, is free, and works. To make sharing calendar with assistants easier, we signed up for Google Apps (For Your Domain) and set it up there. This way the assistants could have their own accounts with our domain and could theoretically send mail without the not-so-professional "On Behalf Of" showing up in recepients' Outlooks (most other clients ignore this header).

Contacts syncing is just not there. OK, there are various hacky solutions out there, but we figured that the contacts just don't change that often, and for now we can do it manually (export from Blackberry desktop, import to Gmail). Google API's look pretty promising though, eh?

Now the interesting part - initial sync So we have a few hundred events in the current calendaring setup (Outlook and Blackberry)... and now we want to begin using google sync and google calendar. What to do? Google Calendar allows you to import ICAL or CSV files (and the import button is a little hard to find). Outlook 2003 does not export to Ical, but does export to CSV. Unfortunately CSV (as you would expect) does not support recurring events (and most of her events are recurring) - meaning that if you export a years's worth of calendar data and you have a weekly event - you will have 52 separate events in the CSV file.

So once I imported the CSV into Google, instead of a few hundred events we had a few thousand, and they are entirely disconnected from each other - moreover, that was just 2 years' worth - so they end next year. I can't say I liked that. A Google search for Outlook export ICAL turned up a neat VB script which does just that. It worked great, but then Google blew up importing the file with a "Line 256: Null" error. Looking at line 256 revealed an odd thing in the exported ICAL file - one recurring yearly appointment was missing something after an = sign... Which I manually added - I figured out it should say =1SU. Google digested the fixed file. The only problem was - remember those thousands of events I imported first? How do I get rid of them? Apparently there is no UI feature to do this.

To make a long story short, I learned some stuff about the Google Apis today. I downloaded Google Calendar API (php version hosted by Zend), and created a very quick hacky script to delete all events in a google calendar. I found a simpler script online somewhere but it didn't work for Google Apps. The script logs in, pulls up the appointments (25 at a time), deletes them, proceeds to the next 25. In 2 minutes I had a clean calendar. Here is the script (You'll need to rename to it .php) Google-Calendar-delete-all.php.txt

Now the next problem... what time does Google Mobile Sync pick up from? I carefully tried syncing, and, as expected, I got duplicate events on the blackberry! No fear, I can fix this too - I used the Blackberry Desktop to wipe the handheld calendar (Backup button, advanced, calendar, clear), then synced with google again. This time I got 24 weeks of events (that's as far back as google sync goes), which is good enough for the blackberry. I am calling it a day with Calendar.

Now for storing mail sent from Blackberry for the IMAP accounts... BIS has a feature for auto-bcc. I made a new email alias in our hosting account, say 'blackberrybcc@my.domain.com'. Now every time she sends an email, a copy gets sent here. Then I created a filter for the real account (in cpanel , in our case), to file mail with 'blackberrybcc@my.domain.com' in Any Header right into Sent folder. By the way, it seems to be in the "Envelope-To" header.

I am done. It works well enough for now.

0 Trackbacks

  1. No Trackbacks

0 Comments

Display comments as (Linear | Threaded)
  1. No comments

Add Comment


You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

What is the primary language of this blog? (Anti-SPAM question)


Submitted comments will be subject to moderation before being displayed.