Skip navigation

Applescript: Export Numbers to Calendar

533 Views 8 Replies Latest reply: Nov 16, 2012 2:37 AM by VictorAB RSS
VictorAB Calculating status...
Currently Being Moderated
Nov 15, 2012 2:35 AM

Hello!! I'm new with Applescript.

 

I'd like to export my schedule created with Numbers to my iCal. But i don't know how i can automatize this task.

 

This is an example of my numbers file:

 

(This file have hidden rows because this file include more people. Applescript can filter for my name?)

 

Captura de pantalla 2012-11-15 a la(s) 11.33.16.png

 

Thanks a lot for your help and for teaching me this

Numbers, Mac OS X (10.7.2)
  • Pierre L. Level 4 Level 4 (3,860 points)
    Currently Being Moderated
    Nov 15, 2012 5:20 AM (in response to VictorAB)

    Could you give an example of the way you would like the schedule created with Numbers to display in iCal.

  • Pierre L. Level 4 Level 4 (3,860 points)
    Currently Being Moderated
    Nov 15, 2012 9:02 AM (in response to VictorAB)

    Another question: am I right to assume the cells of your Numbers document with values such as "02/11/2012", "14:00" and "23:00" are in Text format, and not in Automatic or Date and Time format?

  • Jacques Rioux Level 4 Level 4 (1,925 points)
    Currently Being Moderated
    Nov 15, 2012 11:41 AM (in response to VictorAB)

    Hi.

     

    This script works if the format of cells that contains the date or time is a Date format.

    If the format is Automatic, this script will work if Numbers recognizes the text as a date or as a time.

     

     

    Try this :

    ---------------

    set currTable to my getCurrentTable()
    if currTable is false then return
    set timeToGmt to (time to GMT)
    set tStart to false
    tell application "Numbers"
          repeat with i in (get cells of column 1 of currTable whose its value is "Victor")
                set thisRow to row of i
                set xRow to row ((address of thisRow) - 1) of currTable -- get the row before this row (to get the date)
                repeat with thisCell in (get cells of thisRow whose its value is not 0 and its value is not "Off")
                      set tTime to value of thisCell
                      if class of tTime is date then
                            set tStart to not tStart
                            set tTime to time of (tTime - timeToGmt) -- get the time
                            if tStart then -- get the start date
                                  set col to address of column of thisCell
                                  set startDate to (value of cell col of xRow) - timeToGmt
                                  set time of startDate to tTime
                            else --  make the end date
                                  copy startDate to endDate
                                  set time of endDate to tTime
                                  my make_IcalEvent(startDate, endDate)
                            end if
                      end if
                end repeat
          end repeat
    end tell
    
    on make_IcalEvent(sDate, eDate)
          tell application "iCal"
                make new event at end of events of calendar "Work" with properties {allday event:false, start date:sDate, end date:eDate, summary:"Work"}
          end tell
    end make_IcalEvent
    
    on getCurrentTable()
          tell application "Numbers"
                set tTables to (tables of sheets of front document whose its selection range is not missing value)
                repeat with t in tTables -- t is a list of tables of a sheet
                      if contents of t is not {} then return (get item 1 of t) --  return the current table
                end repeat
                -- no selection
                set x to name of tables of sheets of front document
                if (count x) = 1 and (count items of list 1 of x) = 1 then return table 1 of sheet 1 of front document
          end tell
          activate
          display dialog "Select any cell in the active table."
          return false
    end getCurrentTable
    

     

    I put "Work" as the name of the iCal calendar , you need to change the calendar name in the script according to your calendar in iCal.

  • Pierre L. Level 4 Level 4 (3,860 points)
    Currently Being Moderated
    Nov 15, 2012 3:50 PM (in response to Pierre L.)

    Maybe you might want to try an alternative solution:

     

    set calendarName to "Test"

     

    set theEvents to {}

    tell application "Numbers"

              set theSchedule to table 1 of sheet 1 of front document

     

              set theDateCells to {}

              get cells of theSchedule whose format is date and time

              repeat with thisCell in result

                        set thisValue to value of thisCell

                        if (class of thisValue is date) and (time of thisValue is 0) then copy thisCell to the end of theDateCells

              end repeat

     

              repeat with k from 1 to (count theDateCells) by 2

                        set thisCell to item k of theDateCells

                        set theDate to value of thisCell

                        set C to address of column of thisCell

                        set R to address of row of thisCell

                        repeat until value of first cell of row R of theSchedule is "VICTOR"

                                  set R to R + 1

                        end repeat

                        set theStartTime to value of cell C of row R of theSchedule

                        set theEndTime to value of cell (C + 1) of row R of theSchedule

                        copy {theDate, theStartTime, theEndTime} to the end of theEvents

              end repeat

    end tell

     

    repeat with thisEvent in theEvents

              if class of item 2 of thisEvent is date then

                        set theDateString to date string of item 1 of thisEvent

                        set theStartDate to date (theDateString & space & time string of item 2 of thisEvent)

                        set theEndDate to date (theDateString & space & time string of item 3 of thisEvent)

                        tell application "Calendar"

                                  tell calendar calendarName to make new event with properties ¬

                                            {summary:"Work", start date:theStartDate, end date:theEndDate}

                        end tell

              end if

    end repeat

  • Jacques Rioux Level 4 Level 4 (1,925 points)
    Currently Being Moderated
    Nov 16, 2012 2:22 AM (in response to VictorAB)

    Hi,

     

    VictorAB wrote:

     

    Jacques, that's work almost perfect i detect a little wrong exported dates.

     

    Always in the hour range between 14:00 and 23:00, the script create an event with hours 00:00 and 00:00

     

    This script can jump between tables? or i need to clic manually on everyone.

     

     

     

    Now the script processes all tables in all sheets.

     

    I found the problem, it's the dates that are in the period of the Daylight Saving Time.

    Example : the date of the first 23:00  is  "28/07/2012 01:00" , when the script subtract the Time to GMT to this date, the time is 0.

     

    Now, the script checks each date (cell value and the current date) and adjusts the subtraction according to that date occurs during daylight savings time or not.

     

    I add the possibility that the end time ends at midnight or later. in this case, as the end time is smaller than the start time, the script add a day to the date.

    -------------------------

    set isDST to (my getDST(current date, missing value)) is not 0
    set timeToGmt to (time to GMT)
    set tStart to false
    tell application "Numbers"
          repeat with tSheet in (get sheets of front document)
                repeat with currTable in (get tables of tSheet)
                      repeat with i in (get cells of column 1 of currTable whose its value is "Victor")
                            set thisRow to row of i
                            set xRow to row ((address of thisRow) - 1) of currTable -- get the row before this row (to get the date)                    
                            repeat with thisCell in (get cells of thisRow whose its value is not 0 and its value is not "Off")
                                  set tTime to value of thisCell
                                  if class of tTime is date then
                                        set tStart to not tStart
                                        set r to my getDST(tTime, isDST)
                                        set tTime to time of ((tTime - timeToGmt) - r) -- get the time
                                        if tStart then -- get the start date
                                              set col to address of column of thisCell
                                              set startDate to (value of cell col of xRow)
                                              if class of startDate is date then
                                                    set r to my getDST(startDate, isDST)
                                                    set startDate to (startDate - timeToGmt) - r
                                                    set time of startDate to tTime
                                              end if
                                        else if class of startDate is date then --  make the end date
                                              copy startDate to endDate
                                              if (tTime < (time of startDate)) then set day of endDate to (day of endDate) + 1
                                              set time of endDate to tTime
                                              my make_IcalEvent(startDate, endDate)
                                        end if
                                  end if
                            end repeat
                      end repeat
                end repeat
          end repeat
    end tell
    
    on make_IcalEvent(sDate, eDate)
          tell application "iCal"
                make new event at end of events of calendar "Work" with properties {allday event:false, start date:sDate, end date:eDate, summary:"Work"}
          end tell
    end make_IcalEvent
    
    on getDST(thisDate, tDST)
          tell thisDate to "" & its seconds & " " & its minutes & " " & its hours & " " & its day & " " & ((its month as integer) - 1) & " " & its year
          set r to (do shell script "/usr/bin/perl -e 'use Time::Local;my @arr=split(\" \", \"" & the result & "\");my $d = timelocal(@arr); print ((localtime($d))[8])'") as integer
          if tDST is missing value then return r
          if tDST then return (r * 3600) - 3600
          return (r * 3600)
    end getDST
    

Actions

More Like This

  • Retrieving data ...

Bookmarked By (1)

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.