Import a Spreadsheet Schedule of Events to iCal or Google Calendar — Updated

Tags: , ,

Bottom Line: Here’s an updated post on how to import a spreadsheet of events into iCal, Calendar.app, Google Calendar, or several other modern calendar apps.

Based on the popularity of my last post on getting a spreadsheet of events into your calendar, I’m apparently not the only person that occasionally wants to turn a spreadsheet into something my digital calendar can understand. For those of you that want to skip straight to the good stuff, this should help:

Sections:

  1. Background
  2. Formatting the spreadsheet
  3. My spreadsheet template
  4. Making a .csv file from the spreadsheet
  5. Converting the .csv file to .ics format and importing
  6. icsConverter and icsConverterWebapp
  7. Importing the .ics file
  8. Conclusion

1. Background

During my third year of medical school, I was frequently given my schedule for a rotation in a Excel spreadsheet format. It took a while, but I was eventually able to come up with a reasonable workflow for converting the spreadsheet into a format that I could import into either Google Calendar or iCal / Calendar.app, which I wrote up here. Still, that process involves converting the spreadsheet into a .csv (comma separated value) format that Google Calendar will understand, uploading to Google Calendar, then optionally re-downloading to .ics format for importing into other calendar apps.

Earlier this week (edit May 05, 2013: it’s now been several months since I started the post), I decided to download Pythonista and see what I thought about Python. Given my recent interest in bash and shell scripting, I thought it sounded reasonable. After a couple of days of tinkering, I decided I could probably write something in Python to automate a few of the steps of my .csv to .ics workflow. NB: I only bring up Pythonista for context, this script will not work on iOS AFAIK.

2. Formatting the spreadsheet

If the formatting is not just right, the conversion process will fail. This is definitely the hardest and most frustrating part, but if you can get it right, the rest is just a few clicks. I’ve put together a few tools in this section to help. A few of the spreadsheet formatting requirements are (feel free to skim these bullets now and come back when you’re actually reformatting your spreadsheet):

  • The spreadsheet will start with a row of headers that describe each column.
  • Each event will be a separate row.
  • The start date, start time, end date, and end time, must be in a separate columns; it won’t work if the date and time for an event are both crammed into a single cell.
  • Omitting quotes and commas, the headers must be exactly: “Subject”, “Start Date”, “Start Time”, “End Date”, “End Time”, “All Day Event”, “Description”, “Location”, “Private”
    • I mean exactly, even capitalization.
    • Extra blank columns to the right are okay.
    • If you’re using my icsConverter tools, order does not matter.
    • Even if you’re not using certain columns, you still need the header (and a blank column underneath).
  • The “Subject” column is the title of the event.
  • Both “Start Date” and “End Date” must be in MM/DD/YYYY format.
    • With nearly any modern spreadsheeting app, you can very easily select an entire column and change its format, e.g. from “Sep 9, 2012″ to “09/09/2012.”
    • In Google Docs Spreadsheets, for example, click the letter at the top of a column of dates to select the whole column at once, then go to format -> number -> date in the menu bar.
    • Leading zeros (09 instead of 9 for September) are optional.
  • “Start Time” and “End Time” can be either 24 hour time (13:45) or 12 AM/PM (01:45 PM)
  • “All Day Event” is evaluated on a “True” / “False” basis.
    • If set to “True”, start and end times will be ignored and an all-day (or multi-day) event created.
    • If set to “True” with a blank “End Date”, a single all-day event will be created.
    • If “False”, left blank, or anything other than “True”, it does nothing, and all other start and end dates and times are required.
  • “Location” is just that.
  • The “Description” column will be put in the note field of the calendar event.
  • “Private” is another “True” / “False” column, but currently does nothing. However, you still need the header.
  • If you decide not to use my icsConverter tools, watch out for cells with leading or trailing spaces, and cells that contain commas; these have been okay in my testing so far, with Numbers and

How to transpose a spreadsheet’s columns and rows: If you need to transpose your spreadsheet’s rows and columns, you can do so pretty easily in Google Docs (I think this also works in Excel, but to my knowledge does not work in Numbers).

  1. Create a brand new, blank spreadsheet (not just a new sheet).
  2. Copy pasta your existing spreadsheet into it. Make sure the columns and rows are long enough to fit all your data, extend them if necessary.
  3. Click the “+” button in the bottom left to create a second sheet.
  4. In the first cell of this second sheet, paste in =TRANSPOSE( 'Sheet1'!A1:Z ) and the whole spreadsheet will turn into a transposed version of the first sheet. Highlight and copy.
  5. You should be able to paste this transposed version into a different spreadsheeting app without issues. However, if pasting back into another Google Docs spreadsheet, you’ll need to use Edit -> Paste special -> Paste values only, or else it will try to paste a reference to your temporary spreadsheet, and this reference will break if that spreadsheet changes or goes away.

3. My spreadsheet template

As you can see, you’ll need to convert the spreadsheet of events to a pretty rigid format, which in my opinion is the most frustrating part of entire ordeal. To facilitate this as much as possible, I’ve created this Google Docs spreadsheet template. You won’t be able to edit my copy, but I recommend downloading a copy to work from, or as a quick reference.

  • If you’re an Excel or Numbers user, you should be able to open and File -> Download as -> Microsoft Excel, which should open nicely in either of these apps.
  • If you’re a Google Docs user, and you’re signed into your account, you can just File -> Make a Copy.

4. Making a .csv file from the spreadsheet

This part couldn’t be much easier.

  • Google Docs: File -> Download as -> csv
  • Numbers.app: File -> Export -> csv. You might end up with a folder with a couple of .csv files. If so, either use quick look (spacebar), open them with a text editor, or look at the filenames (if you’ve named your spreadsheets in Numbers) to find the right one.
  • Excel: File -> Save as -> Format -> Comma separated values (.csv)

5. Converting the .csv file to .ics format and importing

If you’re trying to get these events into Google Calendar, you should be able to upload the .csv file at this point. Good luck. They are extremely strict about the formatting requirements, and anything less than perfect will fail. I think my icsConverter tools below are a little more lenient, so if you’re having trouble using the .csv directly, you can try using them and uploading the resulting .ics file instead.

If you want to import these events into iCal (now Calendar.app) or another application, you should convert the .csv file to an .ics file. As I described in my first post on the topic, one way to do this is to upload to Google Calendar and then re-download as .ics. However, as I’ve mentioned, Google can be a bit particular about the file formatting and has really unhelpful error messages if something is wrong. Also, this requires you to have a Google Calendar account, which isn’t the case for everyone.

For these reasons, I wrote an app and a webapp to handle the conversion.

6. icsConverter and icsConverterWebapp

Please see my disclaimer, and realize that I am not a real programmer. While I believe my apps below to be perfectly safe for normal use, I do not have the expertise to know. Make sure you have a complete and working backup of all your data before trying anything, ever. icsConverterWebapp uploads your data to Google Apps Engine, which I assume is reasonably secure and private. However, if you’re terribly concerned about the security and privacy of your calendar information… don’t use it, because I’m not smart enough to know for sure.

icsConverter is written in Python and relies on the icalendar module to work its magic. If you do Python, you’re almost certainly better than I am, so feel free to check out the code at GitHub. In the repo, I’ve included the requirements.txt created by pip, so you should be able to clone the repo, cd in, pip install -r requirements.txt, and be up and running.

I’ve also used py2app to make icsConverter into a standard OSX app. Install by dragging into your Applications folder, like most other apps. I’ve also included in the .dmg an example tester.csv to show that icsConverter seems to work okay, even with some potentially tricky cases like special characters, extra spaces, commas, etc. that might trip up Google Calendar.

Download icsConverter for OSX (intel only)

For those of you that aren’t on an intel OSX device, I also tried my hand at Google Apps Engine and made icsConverterWebapp. This uses essentially the same code, but you don’t have to install anything; just upload the formatted .csv file, then click to download the converted .ics file. Here’s a copy of tester.csv so you can give it a test run if you’d like. icsConverterWebapp’s code is on GitHub as well.

Link to icsConverterWebapp

Calling the exception handling in icsConverter and icsConverterWebapp “rudimentary” would be a huge overstatement. If you have trouble with a conversion, try the following:

  • Check your headers. Consider C&Ping mine from my Google Docs template above.
  • Check your “Start Date” format (remember, MM/DD/YYYY).
  • Check your “End Date” format (remember, MM/DD/YYYY).
  • Check your headers again.
  • Compare the overall format to my Google Docs template.
  • Just for fun, check your headers one more time.

If none of that works and you are okay with me seeing your calendar events, email me the .csv file at the address in my “Contact” link below. Put “icsConverter” or “icsConverterWebapp” in the subject, respectively. Include the error message (if any) in the body of the email, as well as what spreadsheet app you used to create the .csv file. If you’re terribly concerned about me seeing the calendar events in question, please don’t bother emailing. I really don’t have time to try to debug an issue without being able to replicate the bug, which means I need your .csv file.

7. Importing the .ics file

Before importing the resulting .ics file to your calendar, I strongly recommend creating a new junk calendar that you can use to import and inspect the events. You can generally import the resulting .ics file by simply opening it with your calendar app of choice; on a Mac / OSX, try a double click, unless you know that you don’t use your default app. Hopefully it will ask which calendar to import to, and you should choose your junk calendar.

If everything turned out okay, you can delete the junk calendar and re-import to your real calendar. If something didn’t turn out quite right, you can delete the junk calendar, tweak the .csv file, and try again. Without testing on the junk calendar, you risk importing hundreds of useless events into your calendar, which you might have to manually delete… you don’t want that.

If you do happen to need to edit a bunch of events, I recommend giving a shot to Calibrate, with which I’ve generally had very good luck manipulating dozens or even hundreds of calendar events at once. Great app.

8. Conclusion

As you can see, getting a spreadsheet into your calendar app can be quite an ordeal. I hope this helps you get through the process as smoothly as possible. While it is still more involved than I’d like, I think I’ve covered most of the major “gotchas” that I ran into, and I’ll try to keep this post (and the icsConverter apps) updated as I come across new info. Best of luck!

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

  • Lynne Pitts

    wow and thanks! Perfect timing, as I had to do a data conversion rescue mission for my Mom three days after your updated post!

    • http://n8henrie.com/ Nathan Henrie

      I’m glad to hear that it worked out for you! And thank you very much for your donation to my Oreo habit :) It’s actually the first one I’ve gotten since I started blogging 3.5 years ago, and it came through on my graduation day from medical school, so it means a lot. Thanks!

  • J D

    Greatest invention since the Oreo. Thanks so much for taking the time to get this out there. Best to you in your career!

    • http://n8henrie.com/ Nathan Henrie

      Wow, now that’s a compliment! Thanks for the donation, and glad you found it useful.

  • Jennie Alice

    Thank you very much–this is exactly what I was looking for and it worked like a charm.

    • http://n8henrie.com/ Nathan Henrie

      Happy to have helped :)

      • Kelly Ott

        Your instructions above were a life saver! I did run into one problem that no matter how much googling I have done, I cannot find an answer to. Hoping you can help. The “Show me as” option in the google calendar sets to “busy” when you do an all day event. I have tried to use the “Show me as” as a header with both True and False but neither work. Do you have any idea how to import this field in addition to the ones you listed above?

        • http://n8henrie.com/ Nathan Henrie

          Hmmm, I can’t replicate. I just created a fake calendar with two example all-day events, and they both were set to “available” (instead of “busy”) when I imported to Google Calendar. I have this set as default behavior for the converter, so I’m not sure why they would be defaulting to “busy” for you. Are you sure you have them marked as all day events (with the corresponding column as “true”)?

          • Kelly Ott

            What are you using as your header? I was using “Show me as”. Also, I don’t know what you what you mean by have it set as default behavior for the converter? I am strictly loading an excel spreadsheet from your template into the calendar as a .csv. Sorry if I am being thick.
            I could send you my spreadsheet via email if you would like to look at it. Or possibly invite you to my google calendar . . .
            Thanks for you help!

          • http://n8henrie.com/ Nathan Henrie

            Much of the post above is dedicated to the converter I wrote to convert .csv files to .ics files — I wrote it with default behavior to set all-day events to “not busy.” If you are just uploading the .csv file directly, I do not know if there is a header that can make it work for you. My advice would be to use one of my converters above (the webapp should work independent of what operating system you use) to turn your .csv into an .ics file, then upload that file to Google Calendar. You’re not being thick at all, I just didn’t understand!

          • Kelly Ott

            Thank you so much for your help! I will try the converter and see if that works for me! Great posts.

  • Splarty

    In your google template, you have the Location and Description columns reverse (in comparison to the “headers” that you have in point two.

    Obviously, I will experiment to see which is appropriate, but for future users it could help to correct that.

    Apart from that trivial issue, many thanks!!!!

    • http://n8henrie.com/ Nathan Henrie

      Thanks for the heads up! Under #2: “If you’re using my icsConverter tools, order does not matter.” However, it does matter for other methods, so I’ll fix it. FWIW, the spreadsheet order is correct: Description, then Location.

  • JF

    Seriously….SUCH A HUGE HELP….thanks so, so, so much.

    • http://n8henrie.com/ Nathan Henrie

      Glad to hear it, and happy to help :)

  • ninjabreadman

    Hey Nathan, thanks so much for this! For reference, in Excel (Windows or Mac) you can either use the TRANSPOSE() function or Copy/Paste into a new Sheet with “Paste Special…” and check “Transpose”.

    • http://n8henrie.com/ Nathan Henrie

      Thanks for the input! And glad you liked the post.

  • iprofnet

    Worked great after some tweaking with the picky spreadsheet format. You weren’t kidding about that! Thanks for the awesome and useful tool

    • http://n8henrie.com/ Nathan Henrie

      Yes, very picky — glad to have helped.

  • Groffie

    Yep, just what I was looking for! I have to fix a few calendars so I’ll be donating more than once. Thx for sharing!

    • http://n8henrie.com/ Nathan Henrie

      You’re awesome! Thanks a ton, it means a lot :)

  • Debra Feldman Singer

    Nathan,

    This worked great. iCal can be unfriendly at times. I had done the import to Google, etc. This was much easier. Thanks.

    • http://n8henrie.com/ Nathan Henrie

      Great to hear, glad I could help.

  • sarah

    THANKS! It actually worked for me on my first try downloading to Google Calendar. SO much easier than entering 150+ events individually!! You’re a lifesaver!

    • http://n8henrie.com/ Nathan Henrie

      So glad to hear that it worked :)

  • Shana Nicole

    Thank you so much for this post! I am trying to take an Excel file of school events and make an .ics file for teachers to download into their iPad calendars. I made it through the first few steps — importing to Google cal from Excel, then exporting to .ics — but here’s where I’m stuck. I emailed the .ics file to myself to open on my iPad (I’m using a Windows computer for Excel access), and when I try to open the file on my iPad, I’m having two problems: 1) It doesn’t give me the option to import all the files at once, only individually. & 2) The times are all set at 12:00, even though on Google calendar the times are correct. Any help would be much appreciated! I don’t have much programming experience so I might be a little out of my element here, but I’m usually a pretty quick learner. Thanks :)

    • http://n8henrie.com/ Nathan Henrie

      Huh. It may have something to do with time zones. Can you try using my csv to ics converter instead of going through google calendar? Let me know if that works, if not we’ll go from there.

  • Chrys

    I’m really stuck on the last step. In this thing it just says “open it in calendar app of choice,” but no instructions on how?? I have google calendar open but see no importing or open button or anything like that. So I just did all of this only to not be able to do the last, critical step! Please, please consider adding this part to this otherwise awesome article! I’m so frustrated.

    • http://n8henrie.com/ Nathan Henrie

      Hi Chrys — there are so many calendar apps out there that there’s no way I could provide instructions for all of them. In Google Calendar, go to Settings -> Calendars -> Import Calendar. Glad you found the rest of the article helpful!

  • Stroh

    Great Job. After I finally figured out that 2014 and 2104 aren’t exactly the same year, the whole process worked like a charm Thank you so much.

    • http://n8henrie.com/ Nathan Henrie

      Glad to hear you got it figured out :)

  • GB

    I don’t comment much online but just wanted to say thanks for doing this. A few of my friends and I share season tickets for hockey games and I was able to use your format to export all my games into my calendar. Saved a ton of time, and now that I have the template format, next year will be even faster! Thanks so much.

    • http://n8henrie.com/ Nathan Henrie

      Wonderful use for it! Glad to hear it was helpful.

  • Konpal

    Wow awesome thanks so so much. I was trying since a long time to import my google spreadsheet to the calender but always failed. But one thing, what if i want to repeat the event yearly? I tried adding the colums with the headings but its not happening

    • http://n8henrie.com/ Nathan Henrie

      You’re right, I don’t have that functionality built in yet, although I believe it is supported by the Python module underlying my converter, so I may be able to do that eventually. For now, if there aren’t too many events, my recommendation would be to import the events and manually set them to repeat yearly. If it’s a ton of events that you intend to have repeat, you could potentially make the process easier by duplicating the events in the spreadsheet and having the spreadsheet autofill incremental years for each event. Just a thought.

  • Mazhira

    Thanks for posting this! It has been a great help to me. Just one thing, i think the Private header does do something nowadays. I put it on True, but wasn´t able to share the details of my calander (except if I put all the event one-by-one on ´open/not-private´) with someone else, I just did a trial run and I think I eventually fixed it by putting False underneath the Private header…. Anyway, just so you know. And again, thanks a lot :-)

    • http://n8henrie.com/ Nathan Henrie

      Interesting. Were you creating a new calendar from the events? What app was hosting the calendar? I have several shared calendars and haven’t noticed a problem, but I also haven’t been paying much attention to this field. Thanks for chiming in.

      • Mazhira

        I composed a list of events in Google Spreadsheets and then uploaded it to Google Calendar (both on my Chromebook), which went well. Then I shared the calander, but it showed only busy/available, even though I gave the person permission to see all the details of my calendar. I think this is what happens: if you put TRUE underneath the Private header it will make all your events private(and the events details won;t show even if your calendar setting alow them to be seen), if you put FALSE, the Privacy setting of the event itself will be on DEFAULT. This would make sense, unless you filled in TRUE and were able to share your calendar details afterwards…

        • http://n8henrie.com/ Nathan Henrie

          Interesting, thanks for the information. I’ve been able to replicate the issue — if I list an event as Private, even when a calendar is shared as “see all details,” it appears as just “busy.” However, if the calendar is shared as “can also edit the events,” all details are visible. Thanks for bringing this behavior to my attention.

  • Pile_of_Kyle

    This was fantastic! I just created a 15 week running/workout calendar that I would have had to use…ahem…paper. Thank you so much. Little League and Pee-wee football seasons will be so much better now!!

    • http://n8henrie.com/ Nathan Henrie

      My pleasure :) Good luck to the kiddos!

  • Monée Kidd

    I have a child care center with nearly 100 children who need physicals every 13 months. I try to keep reminder dates in an Excel spreadsheet, but unless I remember to open this file, those reminders don’t get sent out in time. I figured long ago that there must be some way to import all those dates into a calendar program, but I could never find instructions to do it. This has been a life saver! I will definitely pitch in for some Double Stufs

    • http://n8henrie.com/ Nathan Henrie

      Thank you so much for the donation! I’m so glad to hear that you found my little tool useful. It’s been a lot of work to get to this point, but it is so gratifying when I hear stories like yours. Thanks!

  • MP

    So glad I found this! Set up the headers exactly as you described and after way too many failed attempts, this time it worked like a charm! Thankyouthankyouthankyou

    • http://n8henrie.com/ Nathan Henrie

      You’re very welcome!

  • Ladybug

    Thank you so much for this! I needed a simple way to get a marathon training calendar with daily workouts into Google Calendar. BINGO! I really appreciate it.

    • http://n8henrie.com/ Nathan Henrie

      Glad I could help! And thanks for the donation! :)

  • Dennis

    Thanks!

  • Pete

    Awesome! Bookmarking the converter, and this page.

    • http://n8henrie.com/ Nathan Henrie

      Thanks, glad you like it!

  • Norman Marshall

    Thanks so much. Definitely very picky – for a while it was putting in all my lessons as 1 hour slots, not 55 minutes. I’m not even sure what I changed but it was helpful that you had pointed out that it all needs to be byte perfect.

    • http://n8henrie.com/ Nathan Henrie

      It is picky! I would love to build some “fuzzy matching” into the converter to relax some of the stringent formatting requirements, but I haven’t had time to do that yet. Glad you found it helpful in the end!

  • m

    Whenever I try to open a certain file with the converter, it doesn’t work. the “open” button appears the dark blue for pressed down, but nothing happens. It works on your sample file though. I tried making sure all the formatting matched and it does.

    • http://n8henrie.com/ Nathan Henrie

      Hmm, I have not heard of this problem. What browser are you using, and have you tried in a different browser?

  • nicki_powers

    You have no idea how much this helped me. I love you! I love you! I love you!

    • http://n8henrie.com/ Nathan Henrie

      Awesome!

  • Chris

    This is great! A few things I’ve noticed when performing this for Google Cal (forgive me if they are mentioned elsewhere; I’m at work and don’t have time to read thoroughly): 1.) If you add an event to your original .csv table and reupload, previously scheduled events will not duplicate (that’s a good thing), 2.) If you change an event’s dates/times, the event will duplicate, so you’ll have to manually remove the “old” event., 3.) Deleting an event from your original table and reuploading the .csv file will not remove it from Google Cal.

    Must thank you again for sharing your time and efforts.

    • http://n8henrie.com/ Nathan Henrie

      Great info! Thanks for contributing!

  • VS

    Hi Nathan
    Thanks a lot for this. I managed to complete step 6. It saved on the desktop as calender not as a .ics file.

    If i double click that link it doesn’t upload on the calender. If I try to import it doesnt highlight.

    I cant find the email link. I dont mind emailing you my file.

    I used numbers to convert .csv and use mac snow leopard.

    Can you help out mate. It will make my life easy.

    Many thanks again – especially for taking me so far

    VS

  • Ignored Plague

    Worked perfectly!!! Thanks.

  • Avril

    Hi Nathan,
    This seems very interesting and along the lines of what I’m looking for for an academic calendar with yearly recurring events. However, I am also looking for the facility of including document(s) with the events. So for instance there is a task to do before an event happens and a document is attached to the event with instructions for what needs to be completed before the event. Have you come across such functionality?
    Thanks,
    Avril

  • gomez

    mate thanks for the advice!

  • Dan Kelly

    Thank you for breaking this down. I spent years importing .xls files (of events like my children’s games and practices for different sports) into Outlook calendar on a PC. It was so simple and incredibly convenient. I am now solely on a mac and had not had success with the process. Knowing how to work with tables and find and replace in Word and how to work with Excel, I just needed to have the formatting of the csv file so I could import into Google Calendar. Thank you so much. It works perfectly. All of my events are now on my Google Calendar, iCal, and my iPhone.

    • http://n8henrie.com/ Nathan Henrie

      Really glad to have helped. It can be quite a hassle, and I had a really hard time finding thorough instructions (at least when I was first trying to figure it out a few years ago), which is why I made this tutorial in the first place! It’s still not as simple as I’d like, but it seems to have worked well enough for many.

  • Darren Stanley

    Nathan Henrie, I have never met you.. But I love you..

    Thanks for this!

    • http://n8henrie.com/ Nathan Henrie

      Welcome :)

  • 4tracks

    amazing guys works like a charm, now I can add all my NAMM Show events

  • Kiran

    you are the man! seriously, you ARE the man! many thanks, mate.

  • mattpenner

    Thanks, great stuff! I always add my kids’ little league schedules to a Google calendar shared with the family. This really helped me quickly set it up without any typos. :)

    • http://n8henrie.com/ Nathan Henrie

      Awesome! This is exactly the kind of stuff I envision it being useful for. Glad you got it to work!

  • http://www.morningsidemontessori.org K Javier

    Fabulous post! I was able to import what I needed super easily with start and end times! I was wondering, is there a way to add people (email addresses) to be able to view the events (be invited to the events) that you add to the Google Calendar? I am using a MAC desktop, and using Excel on the desktop, converting to CSV, then uploading to Google Calendar. I tried adding a column called “Add Guests”, and I typed in an email address, but this did nothing once imported. Do you have any other suggestions? Or is there a possible solution? – Also, is there a way to assign a COLOR to the event? I tried doing this by assigning another column that says “Event color”, and I typed in “Bold Red” – which is the name of the color that google calendar has, but this also did nothing. Thanks.

    • http://n8henrie.com/ Nathan Henrie

      I’m certainly glad that I could help get you partway there, and you have some great questions! Unfortunately I don’t have any answers. I believe that the iCalendar spec (outlined here) does provide for some of the features you’re looking for and like others have requested to add alarms in batch. However, I haven’t implemented these features into my icsConverterWebapp, and I don’t think Google Calendar has incorporated a way to do this in bulk, i.e. I don’t think there is any header you could add to make it work. Sorry for the disappointing news — if you find I’m mistaken, I would love to hear otherwise! One possible workaround might be to import the events into iCal (instead of Google Calendar), use a great app called CaliBrate that might be able to make some of those changes in batch, then export from iCal to .ics format and upload that to Google Calendar. Again, if you find a better solution, please let me know!

  • Otis

    Hey mate,
    What I’m trying to make is a training program for a fitness selection course that I will complete towards the end, I will be undertaking the selection course in a few years, so over the few years I want to run through the training program as many times as possible. I was going to set all the weeks of the 15 week program out in the spreadsheet and then up load it into Google Calendar in a calendar I can easily delete. The reason for this is that it is extremely hard and many people get injured or burn out and need a break, an so at times it is needed to start again.
    What I need is to be able to do is reset the whole date column to the new start date and have all the following dates in Column B and E add a day to it. Is there a formula to do this or not? I have tried a few different ways but I am still learning Excel.

    Regards

    • http://n8henrie.com/ Nathan Henrie

      Hey there — I’m thinking there is definitely a way to do this, but I’m not totally sure about the format. It’s pretty easy to have a date in A1, then but in B1 the formula “=A1+3″ (without quotes). Here is an example. You can also drag a date downward to auto-fill the spots below with sequential days. The only trick you might need to keep in mind is that if B1 is a formula that depends on A1, if you change or delete A1 you will mess up B1. To prevent this, once you’ve gotten the result you need, you can copy all of a column or paste, then “Edit -> Paste Special -> Paste Values”, which will paste all the numerical values and let you delete or manipulate the A1 value as needed. Make sense?

      • Otis

        Thank you so much, I spent ages looking for programs that could make an .ics file to add to my calendar, but with this way I can do it just as simply and I have more control over it all. Thanks again mate!

        • Otis

          And also thanks for the speedy reply!

  • jenmenke

    Thanks so veddy, VEDDY much! Worked like a charm. :)

    • http://n8henrie.com/ Nathan Henrie

      Great! Glad to hear it!

  • LJ Aguinaga

    Worked Beautifully! Thank you!!

    • http://n8henrie.com Nathan Henrie

      Awesome!

  • Sandra

    Hello Nathan, I am looking for something similar but I need to import a list of tasks (csv format) into the Reminders app (Mavericks), there are 2 very important fields that I need to use, the name of the task (i.e. the description) and the due date. Apparently Reminders uses the same ics file type than Calendar does, but when I tried to import it I got nothing. Any help would be greatly appreciated. Thanks in advance.

  • BlueCat57

    I think I used this last year and it worked. (I think.) This year I am trying to import the .csv file but am receiving the following error message:

    Failed to import events: Unable to process your iCal/CSV file.

    I am trying to import the file directly from Google Drive. Originally I was trying to import the file from a Drive account different from the calendar account. (I received the same message.) I transferred ownership to the calendar account but still get the same message.

    I don’t use Google Drive much so I am not sure if the file was actually transferred to the other Google account or if just the ownership was transferred.

    I’ll keep playing with this (I’m guessing you are pretty busy being a doctor.) and post an update if I discover anything new.

  • BlueCat57

    Well I got it to work, mostly. I downloaded the Google Doc file as an Excel spreadsheet and then converted it to a .csv file and then imported that. On multi-day events (marked as All Day) the last day of the event didn’t import. So a two-day event showed up as only one day. I’ll just go in and manually adjust the events (It would probably have been quicker to manually enter the events to start with since I only had 16 and they were all All Day events, but heck, I like to try new things.) I may do some experimenting like adding a day to multi-day events to fool the import, or try to figure out how to do Repeating events in a spreadsheet. Oh well, it is mostly done. Now my daughter wants to talk Star Trek so I’ve got to go.

    • http://n8henrie.com Nathan Henrie

      Ha, Star Trek talk is pretty important :) Yeah, only a few events at a time is a hassle until you get the pattern down. I’m not sure why your multiday events aren’t working — mine seem to be fine, I just tested a couple. Remember that start and end times will be ignored on an all day event (though I don’t see why that would change anything).

  • Kate

    Thanks! I appreciate the time it took to share with us!

    • Kate

      I bought you a coffee on paypal :)

      • http://n8henrie.com Nathan Henrie

        I can see that! Thanks a ton!

  • http://www.raisinandfig.com Morgan

    HOLYMOLYROCKY thank you!!! Did the webapp approach to getting a CSV file to ICS… YAY ME!!! THANK YOU

    • http://n8henrie.com Nathan Henrie

      Awesome!!

  • Josh

    I work as an assistant teacher and this is great for filling my class schedules for my three different schools. This is the kind of thing that ought to be built into either Numbers or Calendar. Thank you so much.

    • http://n8henrie.com Nathan Henrie

      Great to get this kind of feedback, thanks for taking the time. Glad I could help!

  • Carl

    all I get is erro here ero there every where I try some thing I get erro
    what is the deal ? I made a copy of yours then made a calendar but don’t understand where do I put the import calendar into your sheet ?

  • Carl

    not one thing that you say her has worked . what junk

  • dl

    Thanks for posting this. I was wondering if you knew any way to make the events uploaded to Google Calendar via a csv file to have “Available” as the available scheduling default, instead of “Busy. _ Something one might use with shared calendars?

    • http://n8henrie.com Nathan Henrie

      When I was originally writing it, I didn’t find much of a difference between the two settings when uploaded to Google Calendar. I think I may have hard-coded to default to “private” events for that reason. It has been a few years, so I may try changing it to see if it makes any difference — I’ll let you know if I update the code to allow that setting. Thanks for the feedback!

  • BK

    Thanks so much for taking the time to do this–really helpful. MS2 here, so appreciate your advice as well. One question: have you noticed any issues with the times inputted in the Excel file/.csv being changed by four hours after conversion and importing? Not sure if this is an issue that iCal and Google Calendar have with time zone conversion issues. Hope residency is going well!

  • Renato de Castro

    Hi Nathan, great work! Thanks! I need to put alerts in some of my events, is it possible? I tried some options but did not get to convert the .csv to .ics using your app when i’ve added “Event”.

    • http://n8henrie.com Nathan Henrie

      Hi Renato — unfortunately at this time I don’t yet have support for alarms. However, I’m beginning work on version 2, and I plan on implementing this feature at that time.

  • Mr Williamson

    This was a brilliant and easy way to share a bunch of important dates.
    FYI the date can be DD/MM/YYYY if you are in the UK, and your calendar is also in the UK.
    However, not all multi-day events worked – I think the end date has to be set to the day after a multi-day event finishes. Still, a minor glitch.
    Thanks Nathan!

    • http://n8henrie.com Nathan Henrie

      Glad you found it useful, and thanks for the tip on the UK date formatting!

  • Salil

    Great work, Nathan. Sure does make things easy. I have a quick question though. If i have multiple ical files and want to upload them all at once into Google Calender, is there any easy way to do this? Is it possible to convert multiple ics files into one csv file using some app?

    • http://n8henrie.com Nathan Henrie

      Hi Salil,
      That’s an interesting question, and unfortunately I don’t have a good answer for you. If you find a way to upload the .ics files in batch, I’d really appreciate if you’d report back and let me know!

  • Tilo

    thanks, works great (via your tempalte)

    • http://n8henrie.com Nathan Henrie

      Great, good to hear!

  • Mark

    Thanks so much for this – had been struggling to get my import to work. Definitely a case of giving Google exactly what it’s expecting. Don’t suppose there’s a way of adding alerts too?

    Cheers

    • http://n8henrie.com Nathan Henrie

      Glad it was helpful! No, I don’t think Google supports alerts with .csv uploads, and I haven’t implemented a way to do it (yet) with my apps. You could consider uploading a bunch of events as a separate calendar, then adding a default alert to all events in that calendar…

  • Richard

    Thank you, works a treat (after I manually converted dates to US format and named icsconverter output file explicitly with .ics extension to be recognised by OS X Calendar). Very useful.

    • http://n8henrie.com Nathan Henrie

      Great, glad it helped!

  • Ray

    Hi, great program. I’m in Arizona (Mountain Standard Time Zone) and am trying to create an .ics file of events that are going to be taking place at various times in Eastern Standard Time. What do I put in the Start Time and End Time fields in the excel spreadsheet that would signify the time zone? Or is there another field to add to the spreadsheet that handles the time zone? I’ve looked at a .ics file and there are fields in there such as:
    DTSTART;VALUE=DATE-TIME:20150604T190000
    DTEND;VALUE=DATE-TIME:20150604T220000
    DTSTAMP;VALUE=DATE-TIME:20141216T223827Z

    just not sure how what to put into the excel spreadsheet to get that output in the .ics file. Thanks for any help.

    • http://n8henrie.com Nathan Henrie

      That’s a good question! I haven’t built any support for different time zones into my converters, and I’m not sure how Google handles it, either. It’s a complex issue — for example, do you want those events to show up on your calendar in local time or at their time? (E.g. if it’s going on at 10 central, 8 MST, should it show up on your calendar at 10 or at 8?) This can be especially confusing for events like flights, where if you’re changing timezones you can’t accurately reflect travel time, takeoff time, and landing time all at the same time.

      • Ray

        I agree, it’s a complex issue for sure. I want those events to show up in my calendar in their time. That way, when I’m watching the games either in person on the East Coast, or in webcasts from Arizona, I know what time they are actually starting. I guess one way would be to put in some events in different time zones into google calendar, export to an .ics file, examine what is different and then post-process your created .ics files to make those changes.