Skip navigation

Excel to iCal script to specify calendar?

712 Views 4 Replies Latest reply: Nov 15, 2012 8:54 AM by jleach RSS
jleach Level 1 Level 1 (0 points)
Currently Being Moderated
Nov 14, 2012 5:08 AM

Hello,

 

I am trying to make a script that will schedule all day ical events from an excel file. My excel file has three columns: Date, Calendar, and Description.

 

DateCalendarDescription
20121114Sciencemidterm
20121114MathFinal Exam

 

 

So far I can get the allday event added with the description but I am having toruble specifying the Calendar for the new event.

 

If the calendar exsits it is supposed to add the event to that calendar and if the calendar does not exist it should create a new calendar with the specified title, right now everthing just gets put into a new calendar with no title. Any help would be really appreciated.

 

 

 

tell application "Microsoft Excel"

          set theWkbk to active workbook

          set theWS to active sheet of theWkbk

          set theRows to rows of (get used range of theWS)

          set the item_count to the number of items in theRows

 

          set propslist to {}

 

          repeat with i from 1 to the item_count

                    set this_item to item i of theRows

                    set v to value of this_item

 

                    if i is 1 then

                              set AppleScript's text item delimiters to return

                              display dialog ("Hi, it's assumed that your columns headings are in order: Start Date, Time, Title, and Description.  Your headings are: " & return & v) buttons {"Cancel", "OK"} default button 2

                              set AppleScript's text item delimiters to ""

 

                    else

                              set v to value of row i of (get used range of theWS)

 

                              set theDate to item 1 of item 1 of v

                              set theCalendar to text item 2 of item 1 of v

                              set theDescription to text item 3 of item 1 of v

 

                              set theEventProps to {theDate, theCalendar, theDescription}

                              set the end of propslist to theEventProps

 

 

                    end if

          end repeat

end tell

 

tell application "iCal"

activate

          set CalList to title of every calendar

          if theCalendar is in CalList then

                    repeat with CalNo from 1 to count of CalList

                              if theCalendar is item CalNo of CalList then exit repeat

                    end repeat

          else

                    set NewOne to make new calendar at end of calendars with properties {title:theCalendar}

                    set CalNo to 1 + (count of CalList)

          end if

 

          tell calendar CalNo

                    repeat with theProps in propslist

                              set theEvent to make new event at end of events with properties {allday event:true, start date:item 1 of theProps, summary:item 3 of theProps}

                    end repeat

          end tell

end tell

iCal, Mac OS X (10.7.5), AppleScript
  • Austin Kinsella1 Level 6 Level 6 (11,505 points)
    Currently Being Moderated
    Nov 14, 2012 9:14 AM (in response to jleach)

    Your program logic does not match what I think you are trying to do.

    At this line:

    if theCalendar is in CalList then

    theCalendar has the value from the last row of your spreadsheet entries - or possibly no value. Then, after trying to identify the calendar, you iterate through all rows adding to this last calendar. So this code should be inside the iteration. It should also be simpler:

     

    repeat with theProps in propslist

         set theCalendar to item 2 of theProps

         try

              event 1 of calendar theCalendar --force an error if it doesn't exist

         on error

              make new calendar at end of calendars with properties {title:theCalendar}

         end

         tell calendar theCalendar

              make new event at end of events with properties {allday event:true, start date:item 1 of theProps, summary:item 3 of theProps}

         end

    end repeat



    Mac OS X (10.6.8), 4GB RAM; 500GB HD
  • Austin Kinsella1 Level 6 Level 6 (11,505 points)
    Currently Being Moderated
    Nov 14, 2012 3:56 PM (in response to jleach)

    tell application "iCal"

         repeat with theProps in propslist

              set theCalendar to item 2 of theProps

              try

                   event 1 of calendar theCalendar --force an error if it doesn't exist

              on error

                   make new calendar at end of calendars with properties {title:theCalendar}

              end try

              tell calendar theCalendar

                   make new event at end of events with properties {allday event:true, start date:item 1 oftheProps, summary:item 3 of theProps}

               end tell

        end repeat

    end tell

     

    Try that. I can't help you with the iCloud issue.

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.