4 Replies Latest reply: Nov 15, 2012 8:54 AM by jleach
jleach Level 1 Level 1 (0 points)

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
  • 1. Re: Excel to iCal script to specify calendar?
    Austin Kinsella1 Level 6 Level 6 (11,505 points)

    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



  • 2. Re: Excel to iCal script to specify calendar?
    jleach Level 1 Level 1 (0 points)

    Thanks Ausitn,

     

    That almost worked. When I fist run the sript a new calendar is created with no title.

     

    Then I run it a second time and I can get calendars with my spcified title, and once created it will add events to those calendars each time I run the script.

     

    However, now I get empty duplicate calendars created when I run the script a second time.

     

    So, when I run the script again with new events to be placed in the same calendar "Math", they are placed in the correct calendar "Math" but a whole new set of empty calendars titled "Math2" are created as well as an additional empty unamed calendar.

     

    Also when I specify a Calendar on iCloud the event is palced properly but a second calendar with the same name is created on my mac.

     

    I am thiniking there is reduncancy in the iteration but I am not sure how to simplify to fix this problem.

     

    Any ideas?

     

    This is what I have from you earlier post:

     

    From the line:

     

              if theCalendar is in CalList then

                        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 of theProps, summary:item 3 of theProps}

                                  end tell

                        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

  • 3. Re: Excel to iCal script to specify calendar?
    Austin Kinsella1 Level 6 Level 6 (11,505 points)

    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.

  • 4. Re: Excel to iCal script to specify calendar?
    jleach Level 1 Level 1 (0 points)

    Hey Austin,

     

    Your suggestion still caused duplicate calendars to be created. So I deleted the "try", this allows me to still get events into the specified calendars but does not create a new calendar if one is not already present in my ical.So I will just need to make sure the calendars are all present. This also fixed the icloud issues I mentioned.

     

    I also cleaned up the excel part and removed the dialog box.

     

    Thank you for your help.