Import a Spreadsheet Schedule of Events to iCal or Google Calendar

Tags: , ,

Bottom line: You can batch import events from a spreadsheet to iCal or Google Calendar by formatting the headers correctly, exporting / import as .csv, then export / import as .ics.

Update May 07, 2013: This post has been completely rewritten and updated here: http://n8henrie.com/2013/05/spreadsheet-to-calendar. Feel free to reference this post as well, but the new post is much more thorough and has a few new tools that I wrote to facilitate the process of getting spreadsheet events into your calendar app of choice.

NB: This process works for me using Numbers.app, Google Calendar, and iCal. I think it should also work with Excel or Google Docs spreadsheets, but I can’t say for sure.

I try to keep all of my appointments in a digital calendar that syncs across all my devices. It’s a big deal for me – I really like to be able to look in one place to find out where I ought to be at any given moment. Getting a syncing, mobile-accessible calendar for the lecture-based years of medical school was actually a major objective leading to the creation of our medical student group, SMRT. I previously used Google Calendar but have recently converted to iCal / iCloud (mostly due to issues deleting events from Google Calendar with my iPhone – a bug that persisted for years and drove me nuts).

Over the last year or so, I’ve been handed a new schedule every month or two on my rotations. Unfortunately, these have almost always been in some kind of spreadsheet format (or they’re printed from a spreadsheet, which I request be emailed to me). Because calendaring software doesn’t know how to interpret spreadsheet information directly, I’ve developed a workflow that is a bit circuitous but not terribly onerous and allows me to bulk convert and import my spreadsheet schedules.

This process requires a Google Calendar account to process one of the files and spit out another format. You don’t have to use it for calendaring, but you can. This whole process may take from 10 minutes to an hour depending on your skill with spreadsheets and a bit of luck.

Main Idea

  1. Give the spreadsheet the appropriate headers and format it to make Google Calendar happy
  2. Export as comma separated values (csv)
  3. Import to Google Calendar (stop here if you’re a GCal user, continue if iCal user)
  4. Use Google Calendar to export to iCalendar format (.ics, iCalendar is not the same as iCal)
  5. Import the .ics to iCal

1. Headers and Spreadsheet Formatting

This is the hardest and most important step. In order for step 3 to work, you have to get the spreadsheet set up correctly. First, open up a copy of my spreadsheet template, which you can

Please note that the Numbers.app version also has some embedded instructions in rough draft format (nothing not posted here). I think these also show up in the Excel version, but not in the GDocs version.

Basically, it’s just a spreadsheet with the headers that Google Calendar will recognize. These are:

Subject,Start Date,Start Time,End Date,End Time,All Day Event,Description,Location,Private

[source]

You should take your spreadsheet schedule and copy and paste the information into the appropriate columns. Keep in mind that Numbers can convert date and time formats pretty well by selecting the column in question and opening up Inspector (“command option i” or view -> inspector) to “cells” (a box with “42” on my version). Makes converting a bunch of “Jan 5 2002” to “01/05/2002” a snap. Leave the columns in the order you found them. Other formatting info:

  • Subject: The event title
  • Dates: MM/DD/YYYY format seems to work for me.
  • Times: both “7:00 PM” and “19:00” formats seem to work
  • All day event: Will be true / false (checked or unchecked in Numbers)
  • Description: The “notes” part, optional
  • Location: Optional
  • Private: Another true / false checkbox, will make events on a shared calendar private to everyone but “owners.” I think this is also optional / no response required.

2. Export as .csv

File -> export in Numbers, unicode seems to work fine. AFAIK, the equivalent functions in Excel and Google Docs should work just as well. If you have multiple sheets in Numbers, it will export to a folder with multiple .csv files. Figure out which one has the data you need with Quick Look or a text editor, you can delete the other .csv files.

3. Import to Google Calendar

Head over to Google Calendar. If this is home base for you, you can import the events directly to your calendar. If this is just an intermediary step, I recommend creating a new temporary / junk calendar for this purpose.

Optional: How to create a temporary calendar: Click the down arrow to the right of “My Calendars” in the left hand column to create a new calendar. Name it “junk,” or “osdifj,” or whatever you want, you can delete it later.

Next, head to your main calendar settings by clicking the same right arrow mentioned above. Towards the middle of the screen, look for the import calendar button. Click it, make sure you have the right calendar selected in the bottom box, then navigate to the file and click “import.”

You will now either get an error message, which probably means you need to go back to Step 1 and make sure everything is formatted correctly (can you tell this has happened to me a few times?), or you will have imported your schedule. Look through GCal to make sure everything looks right. If so, congrats! Google Calendar users can end their journey here, thanks for reading. If you want to import to iCal, you’re getting close.

4. Export to iCalendar Format

A couple of options here. Either

  • Click the “export calendars” button immediately to the right of the “import calendar” button from above, downloading a .zip of all your Google Calendars in .ics format (haven’t tried this but appears to work), or
  • Still in Google Calendar settings, click the name of the calendar, then click the private “ICAL” button on the next screen, then click the link one more time to download the .ics file. If you have trouble, try option + click, which tries to download a link as a file (on a Mac).

Once you have the .ics file, you can…

5. Import the .ics to iCal

Easy as can be. In iCal’s menu, File -> Import -> Import, and select the .ics file. Choose to import to whichever calendar you please. Should sync to iCloud just fine, assuming that you import to an iCloud synced calendar. Also, I recommend you go back to Google Calendar’s settings and delete the temporary calendar you created in Step 3. Keeps everything cleaned up.

Phew.

Seems like a lot of work, but goes quickly once you’ve done it a few times. It’s especially worthwhile when you have lots of events or lots of “description” info in your spreadsheet that you don’t want to retype. On the other hand, if you don’t have that many events, I highly recommend QuickCal for inputting events to iCal on the Mac. Not only is it a solid, user-friendly, lightweight app, but the devs just seem like good people. They have directly incorporated my feedback into their updates a few times now; their customer support is top notch.

Let me know in the comments if this works out, if you run into issues, or most importantly if you find an easier way to get your spreadsheet schedule into iCal.

  • http://www.n8henrie.com/ Nathan Henrie

    I just received a question via email regarding adding alarms to these calendar events. It looks like there are appropriate .ics headings (for example http://goo.gl/N9Zw5) to add alarms and email notifications, but I can't tell if 1) Google Calendar supports importing those headers from .csv files, or 2) if you can prevent Google Calendar from acting on them if you're just going to re-export to .ics and import to iCal.

    I just gave it a test run and it looks like it does not work as one might hope (at least my first try). I added the relevant Reminders heading to my spreadsheet. Google Calendar appears to have not imported the alarm times appropriately, and its output .ics file doesn't have an alarm at all. Bummer. Anyone else have a way to make this work?

  • Leif

    Thanks, very helpful! My hockey game schedule is exported as an excel sheet and was able to use your template to import into Google Calendar.

  • Evelyn

    this has worked but for one strange thing, I have imported 97 events from excel into google calendar and every event is listed one day short, I have the end time as 18:00 but it is ignoring that day, any idea of why that might be or what I can do to rectify it? Thanks

    • http://www.n8henrie.com/ Nathan Henrie

      Can you clarify what you mean by “one day short”? Are these multi-day events (say a 3-day long event) that are showing up as less than that (2 days)? Or do you mean they’re showing up one day before they’re supposed to (say 02/17 instead of 02/18)?

      Can you copy and paste one or two lines of your excel spreadsheet?

      • Kelly Ott

        Nathan,
        In the discussions we have been having, I shared that I am trying to import a .csv into the google calendar for multiple events for employees within a University to view. Does your converter allow for multiple color coding of differing events upon import? As this import will need to happen at the beginning of each year, I am trying to use your methods to simplify this process for our group. The going back in and color coding each event is tedious. But, also know that I have to make available the same .csv file for employees choosing to use Outlook, and repeating events do not import properly there. Any thoughts?

        • http://n8henrie.com/ Nathan Henrie

          Hi Kelly, to my knowledge the iCalendar format does not allow you to set colors per event, but I could be wrong; I think it is a limitation of the file format itself. If not, it is definitely not something I’ve implemented into icsConverter or icsConverterWebapp.

          My guess is that this is a feature that is implemented by various calendar apps that has nothing to do with the event data you upload, and so you will likely be unable to automate it. If I’m wrong, I would love to learn how to do it!

          What problem do you have importing repeating events to Outlook?? (I have never used Outlook, just curious.)

          • Kelly Ott

            In my original research, I put my original events in to the google calendar, and then set up each repeating event accordingly. (We essentially have three types of events happening every two weeks in the pay cycle.) I then exported the google calendar into an .ics file and attempted to import it into Outlook. Only the three original events would show up in the Outlook calendar. That is why I ended up going back to the .csv doc and setting up each event as a line item. It seems to be the only way I can get the base calendar into both programs.

          • http://n8henrie.com/ Nathan Henrie

            Huh, that’s unfortunate. I just created a repeating event on Google Calendar, exported to .ics, and imported correctly into iCal on my Mac. I’m not sure why Outlook doesn’t import that process correctly; it seems like Google Calendar is doing everything right. Sorry I’m not of more help; feel free to ask any other questions you run into.

          • Kelly Ott

            Thank you so much. You have been VERY helpful.

          • Kelly Ott

            I had a thought. I Took the calendar as it stands now with all of the events speicifically color coded, thinking I could export it to .ics, modify it, and reimport it. The interesting thing is, on reimport – the calendar event flair I specified for each event were color coded properly, but the events themselves were all one color. Thought I would share that this method doesn’t seem to work either.

          • http://n8henrie.com/ Nathan Henrie

            Interesting. You should be able to open up the .ics file in a text editor (like Notepad on Windows or TextEdit on Mac) and see exactly what kind of information it has. It’s a little hard to understand at first, but I think you’ll find that the .ics simply doesn’t contain the information needed for the event colors.

  • Pingback: creare un calendario ics a partire da un csv | il mio piccolo taccuino di appunti()

  • edhyde

    Thank you man, this has helped me a lot, saved me a couple of working hours…

    • http://www.n8henrie.com/ Nathan Henrie

      Glad to have helped. I am planning an updated post soon with better instructions and an app / webapp I wrote in Python that takes care of the Google Calendar upload / download steps.

  • Steph

    Worked Perfectly THANK YOU!

  • Sharman Rice

    Thanks! I am new to Numbers, so cannot figure out how, on my iPad, to export the new file as a CVS file. If this only works for Numbers on a Mac, I’ll have to retry on my PC.

    • http://www.n8henrie.com/ Nathan Henrie

      Huh, I hadn’t tried this route. Like you, I’m surprised to find that you can’t export to .csv from Numbers on iOS. I tried exporting to Google Drive (iOS) in Excel format, but that doesn’t work either (it doesn’t even seem to convert to Google Docs spreadsheet format so you can edit it). Bummer.

      I’ll let you know if I find a solution. If you come up with one, please post it here.

  • Pingback: Import a Spreadsheet Schedule of Events to iCal or Google Calendar — Updated()

  • Pingback: Att importera från excel till google calendar « Alva Mårdsjös liv och leverne()

  • GeordieGuy

    What’s the UUID field used for? I notice that the UUIDs end in your domain name, are you tracking use of your method? Shouldn’t you tell us if you are?

    • http://n8henrie.com/ Nathan Henrie

      Good question. Nope, I’m not tracking with that and wouldn’t even know how. The UUID is just a unique identifier I generate with a large random number; every event in a calendar needs one. I tacked on my domain because that’s what I’ve seen others do, and it helps ensure that the UUID is indeed unique (in the statistically improbable chance that someone’s calendar already had an event with the UUID I my app had generated), and perhaps it will remind someone where they found the converter (self promotion).

      I do use Google App Engine’s logs to occasionally look at usage statistics and error logs; these data are anonymous and compiled in aggregate. If there is any way to use it to identify anyone individually, I don’t know how (and I don’t think there is).

      Further reading on the iCalendar UUID from the Internet Engineering Task Force here. See section “4.8.4.7 Unique Identifier”

      • GeordieGuy

        Later reading up had shown as much. Thanks for replying and clarifying, and sorry for being paranoid.

        • http://n8henrie.com/ Nathan Henrie

          No worries, paranoid is good. That’s one reason the code is all open source (though I just noticed that this thread is on the wrong post, with my apps on the updated post).

  • Jamie

    Thanks for this blog post, it’s been most helpful. I wonder though, is it possible to specify an alarm date and time for each event?

    • http://n8henrie.com/ Nathan Henrie

      Thanks for the kind words! Unfortunately, it’s not possible to specify an alarm at this time. I think this would be a great feature to add, and I hope to work on implementing it eventually, but I have no idea when I’ll get around to it. Until then, if you’re using iCal, you might consider trying an app like Calibrate that can add alarms in batch to events already in an iCal calendar.

  • Roseanne

    Hello there Nathan. :)

    I just wanted to say thank you as your advice helped me to learn how to import a csv list of events into Google Calendar. The UK time format worked for me, using US put my event (I only tried the file with one event to start with) to the wrong date. Anyway, I got it to work apart from one small annoyance.

    For some reason, my end time is showing as the same as my start time (it did this whether I use AM/PM format or 24 hour format). The event should last for two hours starting at 7:45PM and ending at 9:45PM on March 5th this year. I checked my timezones for the calendars and all seemed to be in order (London time in my case) so I am at a loss as to what to do. I’ve checked and rechecked the csv file and have now given up for the night. Any advice you have would be most appreciated.

    Kind Regards
    Roseanne

    • http://n8henrie.com/ Nathan Henrie

      Hi Roaseanne,
      I’m glad to hear you’ve had at least a little bit of luck with the converter. Great idea trying with a single event first — it’s always smart to give something like this a test run you can verify! I really can’t think of what would cause your event times to be acting like that. It’s not a problem I’ve run into before. If you want me to look over your .csv, I’d be happy to give it a shot. Scroll all the way down to the bottom for my contact info.

  • kim

    Thanks for this!! Can you upload a custom color for the events?

    • http://n8henrie.com Nathan Henrie

      It’s definitely not a feature my apps can do, and I’m not sure if it’s possible with the iCalendar files. Depending on the app you upload to (Google Calendar vs iCal) you may be able to change the events’ color in batch.

  • mugga

    Thanks for the awesome directions here!

  • marthalittlefield

    Thank you, Thank you, Thank you!!!!!

    • http://n8henrie.com Nathan Henrie

      Welcome, wecome, you’re welcome!