Numbers to Ical date problem

I’ve just been fiddling around trying to get a script to add events to ical from a numbers spreadsheet. I’ve been able to create an event from the details in Numbers. Now I’ve run into two problems.
1- I don’t know how to add a repeat statement in order to enter all the lines in the spreadsheet.
2. The vent startdate doesn’t work it adds the default time from a date as 2:00 am instead of what is in the start time field.

I know it’s probably something very easy and stupid. So please any suggestions.

Here is a copy of the numbers file I’m using:

https://files.me.com/amabbett/lpmsp8.numbers.zip

Here is a basic copy of the script :

tell application "Numbers"
	open ":Users:amabbett:Desktop:Trials:Hours 2011-2012.numbers"
	tell sheet 1 of document 1
		tell table 1
			set thenumbersdate to value of cell 3 of column 1 as date
			set thenumberstitre to value of cell 3 of column 8 as string
			set thenumbersadresse to value of cell 3 of column 9
			set thenumberDuree to value of cell 3 of column 5
			set theCalender to value of cell 3 of column 17
			set theNumberHeureDebut to cell 3 of column 4
		end tell
	end tell
end tell
----------------
tell application "iCal"
	tell calendar theCalender
		make new event at end with properties {summary:thenumberstitre, location:thenumbersadresse, start date:thenumbersdate, allday event:false, end date:thenumbersdate + thenumberDuree * minutes}
		set theEvent to first event
		set theEventID to uid of theEvent
	end tell
end tell
----------------
on computeValue(val)
	if val = missing value then return emptyString
	return val
end computeValue
----------------

Thanks for any help.

Model: Macbook Air-2010
Browser: Safari 534.48.3
Operating System: Mac OS X (10.7)

I can’t check because I’m too busy but I guess that you need to edit like that :


 tell table 1
           set thenumbersdate to value of cell 3 of column 1 
           set thenumberstitre to value of cell 3 of column 8 as string
           set thenumbersadresse to value of cell 3 of column 9 as string
           set thenumberDuree to value of cell 3 of column 5 
           set theCalender to value of cell 3 of column 17 
           set theNumberHeureDebut to cell 3 of column 4 
       end tell

Oops
there is a more serious problem

It’s clear that the cell 3 of column 1 contains a date time
What is stored in cell 3 of column 5 ?
Maybe the kind of data may full the calculation done later.

More, if I remember well, the two parameters of kind date must be passed as text.

Yvan KOENIG (VALLAURIS, France) mardi 27 septembre 2011 19:18:43

Merci Yvan,
In column 5 is the length of time of the event “durée” in minutes. This gives me the end of the event. Everything works fine except that because the numbers cell automatically adds a time to a full date, despite me taking it off in the cell format the evnt is created at that time instead of adding a time to the date from column 4. Est-ce que ça aide?

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

It’s not necessary to use my timezone handlers (which are quite slow) on ‘thenumbersdate’ itself, since we know it represents the GMT time at local midnight on the date in question. We only have to know if we’re west or east of GMT to be able to make a very simple adjustment.

The problem is that the cells in Alan’s “HeureDebut” column are also formatted as dates, with only the times being displayed in the spreadsheet. AppleScript, though, receives the full date/time values from these, with the times also shifted to reflect GMT. The dates are those on which the times were entered into the spreadsheet, not necessarily the same as those in the “Date” column, so separate adjustments are needed which can only be done through a full timezone conversion process ” unless, of course, Alan would be willing to reformat his “Heure” columns as text, in which case the script would only need to parse text representing a local time.

Hello Nigel

When we type a date in a cell, Numbers doesn’t include the current time.
It inserts the time value : 12:00:00 AM so it displays something like 2011/09/28 12:00:00 AM

I never understood why it uses this time format because my machine is set to use the 24 hours format and, when I insert the formula =TODAY(), it returns 2011/09/28 00:00:00

Given that, your set of handlers return the clean wanted value, 2011/09/28 00:00:00.

I’m reluctant to urge somebody to define the cells as text ones because I never know the way their tables are filled.
If they import datas thru CSV or TSV files, the format is the Automatic one and changing is is an extraneous task.

Yvan KOENIG (VALLAURIS, France) mercredi 28 septembre 2011 14:40:30

Hi, Yvan.

We may be talking at cross-purposes.

If we type a date into a Numbers cell which has a date or automatic format, and we don’t include a time, Numbers defaults the time to midnight. If we type in a time but don’t include a date, Numbers defaults the date to the current date. What we see in the cell and the format in which we see it depend on the cell’s settings in the Inspector window.

In Alan’s Numbers document, which I downloaded this morning, the “Date”, “HeureDebut”, and “HeureFin” columns are all formatted as (in English) “Date and Time”. The “Date” column displays only the date parts of the values and the other two only the time parts.

The values returned to AppleScript are date objects whose values are the GMT equivalents of the displayed date/times. The differences between the AppleScript date/times and the displayed date/times depend on the time zone to which the machine’s set and the times of the year in which the dates occur. As you say, my time zone handlers can take care of that, although they’re less than instantaneous when there are a lot of dates to handle.

I’ve now realised my suggestion that Alan change the “HeureDebut” format to text is no good, because the date values are used in the formulae for the “Durée” cells.

There’s alway GUI Scripting, I suppose…

Edit: If the script’s only going to be used in France, we already know what the time differences will be and can hard-wire them into the script.

It would require changes to the script according to the period Daylight Time Saving active or not.

This morning, I missed the link to the spreadsheet.

I wrote that I’m reluctant to that but if the OP agree, the easiest tip maybe to insert a column on the right of the table with the formula :

=“”&JOUR(A)&“/”&MOIS(A)&“/”&ANNEE(A)
[ =“”&MONTH(A)&“/”&DAY(A)&“/”&YEAR(A) for U.S. settings ]

The script would become :


tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
   tell row 3
       set thenumbersdate to value of cell 18
       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 end_Date to (date thenumbersdate) + thenumberDuree * minutes

There is a problem in the table.
If the start_time isn’t inserted the same day that the end_time, the formula will return a wrong result.

I would replace the formula :
=F2-D2
by
=ARRONDI((TEMPSVAL(F2)-TEMPSVAL(D2))2460;0)
[ =ROUND((TIMEVALUE(F2)-TIMEVALUE(D2))2460,0) in English ]
Two benefits :
(1) no more problem linked to the insertion time
(2) no more problem with the duration format. The cells will contain standard integers

Yvan KOENIG (VALLAURIS, France) mercredi 28 septembre 2011 16:57:33

I noticed that. We’ll just have to hope Alan doesn’t work past midnight! :wink:

Here’s a script which assumes it’s only going to be used in the Central European time zone:


-- Transpose a GMT date/time to a European time zone.
on GMT2ET(GMTDate, STHoursAhead)
	set m to GMTDate's month as integer
	
	if (m is 3) or (m is 10) then
		-- If the month is March or October, get the last second of the month.
		copy GMTDate to endOfMonth
		tell endOfMonth to set {day, time} to {31, days - 1}
		-- . and round down to the clock-change point at 01:00 (GMT) on the last Sunday of the month.
		set knownSunday1am to «data isot323030302D30312D30325430313A30303A3030» as date
		set clockChange to endOfMonth - (endOfMonth - knownSunday1am) mod weeks
		-- If the GMT date >= the result, pretend it's in the following month.
		if (GMTDate ≥ clockChange) then set m to m + 1
	end if
	
	return GMTDate - (STHoursAhead + (((m > 3) and (m < 11)) as integer)) * hours
end GMT2ET

set STHoursAhead to 1 -- Central European Standard Time is one hour ahead of GMT.
set docPath to (path to desktop as Unicode text) & "Trials:Hours_2011-2012v2.numbers"

-- Open the Numbers document and get the number of the last occupied row in it, assuming that the last occupied row contains a "Date" entry.
tell application "Numbers"
	set thisDoc to (open file docPath)
	set thisTable to table 1 of sheet 1 of thisDoc
	set lastOccupiedRow to address of row of (last cell of column "A" of thisTable whose value is not 0.0)
end tell

-- This repeat assumes that the rows to be parsed are rows 2 to (last occupied row), with no gaps or omissions in the occupied range.
repeat with rowNumber from 2 to lastOccupiedRow
	tell application "Numbers"
		tell row rowNumber of thisTable
			set thenumbersdate to value of cell 1 -- GMT date/time (displaying local date only)
			set thenumberstitre to value of cell 8 -- Text (automatic)
			set thenumbersadresse to value of cell 9 -- Text (automatic)
			set thenumberDuree to value of cell 5 -- Minutes (duration) as real
			set thecalendar to value of cell 17 -- Text (currency!)
			set theNumberHeureDebut to value of cell 4 -- GMT date (displaying local time only)
		end tell
	end tell
	
	-- Get the required start date/time, correcting for timezone shift.
	set startDate to GMT2ET(thenumbersdate, STHoursAhead) + (time of GMT2ET(theNumberHeureDebut, STHoursAhead))
	-- Derive the end date/time from the start date and the duration.
	set endDate to startDate + (thenumberDuree * minutes)
	
	-- Create the event in the named calendar.
	tell application "iCal"
		set theEventID to uid of (make new event at end of events of calendar thecalendar with properties {summary:thenumberstitre, location:thenumbersadresse, start date:startDate, allday event:false, end date:endDate})
	end tell
end repeat

Thanks everyone. Just been reading all the replies. The advantage for myself and Yvan is that I’m using the script here in France. I have to add the correct start and finish times otherwise one of the columns I use for accounting purposes is messed up, so I do check it. For the length I set the cell to only minutes and incorporated that into the script. I feel probably the only solution is to add a column with the formula to the end of the sheet. But I feel that this should really have been something addressed in Numbers, maybe I’m expecting to much. In Bento we have a currency field, but in Filemaker there’s none. I don’t understand why apple just doesn’t do the same things across all programs. But enough ranting. Thanks again everyone et merci Yvan (Est-ce que je vais utiliser numbers en français?- pas encore peut-être plus tard mais d’avantage grâce à vous.

It seems that I was not fully awake when I posted my late message.

To convert the date into a ‘clean’ string, we may use the simple formula
=“”&A
It’s simpler than the 1st one and mainly, it doesn’t create problems if we switch from a French System to an English one.

At this time I’m too tired but I will study the late Nigel’s message tomorrow. There is always something to learn in Nigel’s answers.

Yvan KOENIG (VALLAURIS, France) mercredi 28 septembre 2011 22:34:42

You may use the formula which I posted to calculate “Durée”. It returns an integer and your accounting formula accept it with no problem.
If it was for my own use, I would just insert a 18th column with the formula =“” & A to get a stripped date_string which would be imported easily by the script.

Don’t worry. If I’m at ease with Numbers it’s because I took time to answer questions asked by users in the Apple’s discussion forums and because from time to time I ask Nigel to help me :wink:

Yvan KOENIG (VALLAURIS, France) mercredi 28 septembre 2011 22:41:52