Saturday, May 14, 2011

Google Calendar assigning wrong times on CSV import? Here's one fix for the time zone bug.

I can get pretty disgusted with Google Calendar, but then I make myself remember how bad Apple's iCal is. By comparison, gCal is a ruddy gem.

I had to remind myself of that today, because gCal was particularly disgusting.

It started when I tried importing my son's baseball team schedule into a public calendar using my old (best on the web!) import directions ...

Gordon's Tech: Import Calendar data into Google Calendar via CSV files

Here's the header and first row of the CSV file that I was finally able to import. It looks like you need the bloody seconds in the date. I worked with Excel for Mac, used the convert functions to turn text into numbers, then chose the precise format, then exported. Subject,Start Date,Start Time,End Date,End Time,All Day Event,Description,Location,Private Edgumbe Peewee Hockey,10/24/09,2:10:00 PM,10/24/09,3:10:00 PM,FALSE,Practice, Highland North,FALSE

This time I did necessary concatenations of strings and type transforms using Apple's excellent (and under-appreciated) and inexpensive Numbers.app spreadsheet.

The good news is that gCal no longer requires "seconds" in the time fields, and, although I don't think it's documented, it will even work with "military" time (14:20 instead of 2:20 PM).

The bad news is that time zone support is flaky. After I imported all of my calendar entries were off by about 3 hours, even though my time zone (general calendar settings) and the calendar's time zone were both CST.

I made several tries at fixing it, but this is what worked.

  1. Switch to Calender Settings:General
  2. Change your time zone to something different.
  3. Save.
  4. Change it back to the correct setting.
  5. Save.

After I did that my next import worked.

Fortunately I know to always do imports into a unique calendar; there's no way to undo an import and repetitively deleting 50 bad calendar items is a good source of Google hate [1]. I created and deleted test calendars until I figured a workaround for the bug. (If you do get bit by a bad import sync to the (abominable) OS X iCal and repair there.)

See also:

[1] Why are calendar apps so awful? My theory is that they are quite hard to do well, but management doesn't value them. So they never receive sufficient investment.

No comments:

Post a Comment