Q: Import data from Excel to iCal?

This is an old one, but might help. It is intended to run as a "droplet" - copy it to a Script Editor window, save it as an application on the desktop, then drop the spreadsheet on it. You could modify it to suit your needs.

--AK Mar 2005

--make iCal events from a dropped spreadsheet

--assume  events in first sheet

--one event per row

--1st row is header with labels Title,Start,End (any order, gaps acceptable)

--also process Location and Description cols if present

--prompt for calendar to get the events

--could use some error handling

on open DroppedFile

          tell application "Microsoft Excel"

  open DroppedFile

                    set UsedCells to value of used range of sheet 1

  quit --remove this if Excel sholud stay open

          end tell

          set SummaryCol to FindInList("Title", item 1 of UsedCells)

          set StartCol to FindInList("Start", item 1 of UsedCells)

          set EndCol to FindInList("End", item 1 of UsedCells)

          tell application "iCal"

                    set CalList to title of every calendar

          end tell

          set Chosen to choose from list CalList with prompt "Choose calender for the new events"

          if Chosen is not false then

                    set TheCalendar to FindInList(Chosen, CalList)

                    if (SummaryCol > 0) and (StartCol > 0) and (EndCol > 0) then --REQUIRE Title, Start and End

                              set DescripCol to FindInList("Description", item 1 of UsedCells) --look for additional items

                              set LocCol to FindInList("Location", item 1 of UsedCells)

                              if (count of item 1 of UsedCells) > 1 then --there are some data rows

                                        set UsedCells to rest of UsedCells

                                        repeat with AnEvent in UsedCells

                                                  set theSummary to (item SummaryCol of AnEvent)

                                                  set TheStart to (item StartCol of AnEvent)

                                                  set TheEnd to (item EndCol of AnEvent)

                                                  tell application "iCal"

                                                            tell calendar TheCalendar

                                                                      set CalEvent to make new event at end of events with properties {summary:theSummary, start date:TheStart, end date:TheEnd}

                                                                      if LocCol > 0 then set location of CalEvent to item LocCol of AnEvent

                                                                      if DescripCol > 0 then set description of CalEvent to item DescripCol of AnEvent

                                                            end tell --calendar

                                                  end tell --iCal

                                        end repeat --AnEvent

                              end if --are datarows

                    else

                              display dialog "Spreadsheet must have Title, Start, and End columns" with icon stop

                    end if --missing headers

          end if --no calendar selected

end open

on FindInList(Needle, HayStack)

          set FoundAt to 0

          if Needle is in HayStack then

                    repeat with FoundAt from 1 to count of HayStack

                              if Needle is item FoundAt of HayStack then exit repeat

                    end repeat

          end if

          return FoundAt

end FindInList

As a date/time, as I recall.

OK, I've just tried it. In Excel I set the format of the start and end to Date, and selected 3/14/01 13:30 in the list, then put in 07/02/11 12:00 and 07/02/11 14:30 in the cells and it made the event for next Saturday, July 2nd from noon to 14:30.

Hi Austin,

I really, really hope you can help. I know very little about AppleScript and am going nuts trying to modify the above script (to work as a Droplet App) to work with the Excel spreadsheet I have - I've pretty much wasted 5 hours before giving up and deciding to beg for your help.

We use a spreadsheet for work that we update every 5.5 weeks or so and reload into our Calendars (I'm a teacher and each upload reflects new term dates).  My columns across the top are: Start Date,Time,Title and Description. All events are all day events, so there is no end time (online teaching...we really have all 24 hours in a day to complete a task!). No events are recurring and each new calendar import has only about 35 entries or so, max.  I can send you a sample spreadsheet if that helps.

We work in a Windows environment, but 3 of us are MAC people and I would certainly be the hero of my group if I could get your script to work for importing our team spreadsheet into iCal. I am using Excel 2010 for Mac, iCal 4.0.4, and Snow Leopard (although ideally would like something that works on Lion too as I am considering the upgrade soon).

Thank you in advance for any help you can give me. It is most appreicated.

If you send me the sample spreadsheet I'll take a look.

Thank you. It is sitting in a DropBox folder and here is the link to it: http://dl.dropbox.com/u/233270/term%20cal.xlsx.  Sorry for my ignorance, but for the life of me I could not figure out how to email you directly from this forum.

Your help is very needed and very appreciated!

Alisa

You would look up my email in my profile and send the spreadsheet from mail, but anyway I have it.

I've made a couple of changes so that it doesn't look for end dates, and makes the events all day. I've also fixed a bug in the calendar selector. If you don't want to have to choose the calendar each time, read the comments and make the changes.

Good luck!

--AK Mar 2005
--make iCal events from a dropped spreadsheet
--assume  events in first sheet
--one event per row
--1st row is header with labels Title,Start,End (any order, gaps acceptable)
--also process Location and Description cols if present
--prompt for calendar to get the events
--could use some error handling
--updated for Alisa Sep 2011
-- - removed end date stuff
-- - make the new events all day
-- - fixed bug in calendar selection
-- - instructions on omitting the prompt for a calendar
on open DroppedFile
     tell application "Microsoft Excel"
          open DroppedFile
          set UsedCells to value of used range of sheet 1
          --quit --remove this if Excel sholud stay open
     end tell
     set SummaryCol to FindInList("Title", item 1 of UsedCells)
     set StartCol to FindInList("Start Date", item 1 of UsedCells)
     -- ********************
     -- to avoid being prompted each time for a calendar:
     -- 1) remove the line "end if --no calendar selected" just before end open
     -- 2) remove the line "if Chosen is not false then" just after the next line of asterisks
     -- 3) remove the words "FindInList(item 1 of Chosen, CalList)" on the line after the line just removed and replace 
     --    them with the name of the calendar in quotation marks so it reads eg
     --    set TheCalendar to "Timetable"
     -- 4) remove all the stuff between the two rows of asterisks
     tell application "iCal"
          set CalList to title of every calendar
     end tell
     set Chosen to choose from list CalList with prompt "Choose calender for the new events"
     -- *********************
     if Chosen is not false then
          set TheCalendar to FindInList(item 1 of Chosen, CalList)
          if (SummaryCol > 0) and (StartCol > 0) then --REQUIRE Title, Start 
               set DescripCol to FindInList("Description", item 1 of UsedCells) --look for additional items
               set LocCol to FindInList("Location", item 1 of UsedCells)
               if (count of item 1 of UsedCells) > 1 then --there are some data rows
                    set UsedCells to rest of UsedCells
                    repeat with AnEvent in UsedCells
                         set theSummary to (item SummaryCol of AnEvent)
                         set TheStart to (item StartCol of AnEvent)
                         --set TheEnd to (item EndCol of AnEvent)
                         tell application "iCal"
                              tell calendar TheCalendar
                                   set CalEvent to make new event at end of events with properties {summary:theSummary, start date:TheStart, allday event:true}
                                   if LocCol > 0 then set location of CalEvent to item LocCol of AnEvent
                                   if DescripCol > 0 then set description of CalEvent to item DescripCol of AnEvent
                              end tell --calendar
                         end tell --iCal
                    end repeat --AnEvent
               end if --are datarows
          else
               display dialog "Spreadsheet must have Title and Start columns" with icon stop
          end if --missing headers
     end if --no calendar selected
end open

on FindInList(Needle, HayStack)
     set FoundAt to 0
     if Needle is in HayStack then
          repeat with FoundAt from 1 to count of HayStack
               if Needle is item FoundAt of HayStack then exit repeat
          end repeat
     end if
     return FoundAt
end FindInList

Hmm. Something wrong with the paste there

Your email actually shows up as "private" under your profile, hence my confusion about how to email you! Ok so I copied and pasted in AS, tried to save as an Application to my desktop, and got the following:

Syntax Error.

Expected end of line, etc. but found identifier.

When that error comes up, this is hightlighted (just the word range in this part):

set UsedCells to value of used range of sheet 1

Thoughts?  And thank you again, I cannot say that enough.

Oops - it used to be visible, but times change.

What version of Excel are you using?

Office 2011 for Mac. And I actually just realized my error. When I cut and pasted into AS, I did not hit enter and leave a blank line after your script.  For some reason when I did that, it worked perfectly!

Thank you!!! I owe you one...should you ever get to Chicago, the beer is on me!