Duration is very difficult to use in a script.
The result is linked to the cell’s format.
If you defined it to display only minutes,
if the cell contain the value 20m, it will extract the value 20.
If you defined it to display hours and minutes,
if a cell contains the value 20m, it will display 0h 20m and AppleScript will extract the value 0.333333333
The only way to be sure of what we would extract is to use an extraneous column with the formula
=DUREE.SUPPRESSION(E)2460 (in French)
=STRIPDURATION(E)2460 (In English)
I used column S (19) for this formula.
When I run :
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
tell row 3
set thenumbersdate to value of cell 1
set thenumberstitre to value of cell 8 as string
set thenumbersadresse to value of cell 9 as string
set thenumberDuree to value of cell 19
set theCalender to value of cell 17
set theNumberHeureDebut to cell 4
end tell
end tell
thenumbersdate + (thenumberDuree * minutes)
The log is :
tell application “Numbers”
get value of cell 1 of row 3 of table 1 of sheet 1 of document 1
→ date “mercredi 28 septembre 2011 02:00:00”
get value of cell 8 of row 3 of table 1 of sheet 1 of document 1
→ “titre”
get value of cell 9 of row 3 of table 1 of sheet 1 of document 1
→ “adresse”
get value of cell 19 of row 3 of table 1 of sheet 1 of document 1
→ 20.0
get value of cell 17 of row 3 of table 1 of sheet 1 of document 1
→ “calender”
get cell 4 of row 3 of table 1 of sheet 1 of document 1
→ cell “D3” of table “Tableau 1” of sheet “Feuille 1” of document “Sans titre”
end tell
Résultat :
date “mercredi 28 septembre 2011 02:20:00”
Explanation :
In Numbers there is no date object but date_time ones.
The cell A1 is defined to display the date but no time.
I typed 2011/09/28
For the app it’s 2011/09/28 00:00:00
But, as I described here some times ago, when AppleScript extracts the value, the app pass it the UTC (obsolete GMT) 2011/09/28 02:00:00 because I am in France with the daylight savings time active.
I guess that it’s what may fool your script.
Here is an enhanced version using a set of handlers delivered here by Nigel Garvey some times ago.
set myTimeZone to (do shell script ("/usr/bin/perl -le 'print( readlink(\"/etc/localtime\") =~m{zoneinfo/(.*)} )' ")) -- Perl code by Mark J. Reed.
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
tell row 3
set thenumbersdate to value of cell 1
set thenumberstitre to value of cell 8 as string
set thenumbersadresse to value of cell 9 as string
set thenumberDuree to value of cell 19
set theCalender to value of cell 17
set theNumberHeureDebut to cell 4
end tell
end tell
set thenumbersdate to my TZtoGMT(thenumbersdate, myTimeZone)
log thenumbersdate
set end_date to thenumbersdate + (thenumberDuree * minutes)
--=====
(* Convert an ISO-format date string to an AppleScript date. *)
on isotToDate(isot)
set n to (text 1 thru 8 of isot) as integer
set ASDate to (current date)
tell ASDate to set {day, year, its month, day} to {1, n div 10000, n mod 10000 div 100, n mod 100}
if ((count isot) > 8) then
set n to (text 10 thru 15 of isot) as integer
set ASDate's time to n div 10000 * hours + n mod 10000 div 100 * minutes + n mod 100
end if
return ASDate
end isotToDate
(* Transpose an AppleScript date/time from the given time zone to GMT. *)
on TZtoGMT(TZDate, TZ)
-- The difference between TZDate when it's local and the GMT date we want is usually
-- the same as the difference between the local date when TZDate is GMT and TZDate itself .
set GMTDate to TZDate - (GMTtoTZ(TZDate, TZ) - TZDate)
-- . but not around the time the clocks go forward. If the GMT obtained doesn't reciprocate to TZDate,
-- shift to a nearby local date where the above DOES work, get a new GMT, unshift it by the same amount.
set testDate to GMTtoTZ(GMTDate, TZ)
if (testDate is not TZDate) then
if (GMTDate > testDate) then -- "Clocks forward" is towards GMT.
set shift to GMTDate - testDate
else -- "Clocks forward" is away from GMT.
set shift to -days
end if
set nearbyDate to TZDate + shift
set GMTDate to nearbyDate - (GMTtoTZ(nearbyDate, TZ) - nearbyDate) - shift
end if
return GMTDate
end TZtoGMT
(* Transpose an AppleScript date/time from GMT to the given time zone. *)
on GMTtoTZ(GMTDate, TZ)
-- Subtract date "Thursday 1 January 1970 00:00:00" from the GMT date. Result in seconds, as text.
copy GMTDate to date19700101
tell date19700101 to set {year, its month, day, time} to {1970, 1, 1, 0}
set eraTime to (GMTDate - date19700101)
if (eraTime > 99999999) then
set eraTime to (eraTime div 100000000 as text) & text 2 thru 9 of (100000000 + eraTime mod 100000000 as integer as text)
else if (eraTime < -99999999) then
set eraTime to (eraTime div 100000000 as text) & text 3 thru 10 of (-100000000 + eraTime mod 100000000 as integer as text)
else
set eraTime to eraTime as text
end if
return isotToDate(do shell script ("TZ='" & TZ & "' /bin/date -r " & eraTime & " +%Y%m%dT%H%M%S"))
end GMTtoTZ
The log would be :
tell current application
do shell script "/usr/bin/perl -le ‘print( readlink("/etc/localtime") =~m{zoneinfo/(.*)} )’ "
→ “Europe/Paris”
end tell
tell application “Numbers”
get value of cell 1 of row 3 of table 1 of sheet 1 of document 1
→ date “mercredi 28 septembre 2011 02:00:00”
get value of cell 8 of row 3 of table 1 of sheet 1 of document 1
→ “titre”
get value of cell 9 of row 3 of table 1 of sheet 1 of document 1
→ “adresse”
get value of cell 19 of row 3 of table 1 of sheet 1 of document 1
→ 20.0
get value of cell 17 of row 3 of table 1 of sheet 1 of document 1
→ “calender”
get cell 4 of row 3 of table 1 of sheet 1 of document 1
→ cell “D3” of table “Tableau 1” of sheet “Feuille 1” of document “Sans titre”
end tell
tell current application
do shell script “TZ=‘Europe/Paris’ /bin/date -r 1317175200 +%Y%m%dT%H%M%S”
→ “20110928T040000”
current date
→ date “mercredi 28 septembre 2011 10:36:36”
do shell script “TZ=‘Europe/Paris’ /bin/date -r 1317168000 +%Y%m%dT%H%M%S”
→ “20110928T020000”
current date
→ date “mercredi 28 septembre 2011 10:36:36”
(date mercredi 28 septembre 2011 00:00:00)
end tell
Résultat :
date “mercredi 28 septembre 2011 00:20:00”
So I guess that your calendar will be correct.
Yvan KOENIG (VALLAURIS, France) mercredi 28 septembre 2011 10:39:37
