Hey,
I'm trying to import date from en Excel file to iCal. I have a column with dates, one with start times, one with the location and one with the events.
Is there an app to help me? Perhaps some Automator actions? I've never worked with Applescript before though...
Mac OS X (10.6.7)
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 openDroppedFile
tell application "Microsoft Excel"
openDroppedFile
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 (itemSummaryCol of AnEvent)
set TheStart to (itemStartCol 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
Thanks for that.
But how do I format the Start and End date and time in the spreadsheet?
As a date/time, as I recall.
I've tried formatting as ddmmyyyy/hhmmss and mmddyyyy/hhmmss, but it doesn't accept those.
I can upload all the dates and events though, but it adds them at midnight. All I need is the right time now.
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.
That worked, thanks a lot!!
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!
I'm delighted you found it, 'cos without a copy of Office 2011 I would have been struggling. Don't be too free with the beer offers - I've cousins in Chicago (who hasn't!) that I might pop over to visit.
Import data from Excel to iCal?
