Alas Nigel
It’s not Time to Gmt but Time to Gmt + DaylightSavingsTime
Your algorithm calculate time to gmt but can’t calculate the DST value.
This is why, in my late piece of code which I repeat here, I ask Numbers to do the job for me.
--{code}
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
repeat with r from 2 to 4
tell cell ("B" & r)
set {maybe, maybef} to {value, format}
end tell
if class of maybe is date then
set maybe2 to my shorten_date(maybe)
tell cell ("D" & r)
set value to (maybe2 as text)
set maybe2 to hours of (get value)
end tell
set maybe to maybe - (maybe2 * 3600)
end if -- class of.
tell cell ("D" & r)
set {value, format} to {maybe as text, maybef}
end tell
end repeat
end tell
on shorten_date(une_date)
return date (short date string of une_date)
end shorten_date
--{code}
As far as I know, it will fail only if the date to treat is the limit between summer_time and winter_time.
But for sure, I just tested it here in France. I don’t wish to fool my system with a wrong location setting.
Yvan KOENIG (VALLAURIS, France) mercredi 13 juillet 2011 15:22:40
Hi, Yvan.
My script does produce the right results. Tested with a range of dates throughout the year in my time zone (closest city Coventry), yours (closest city Nice), and USA Pacific (closest city LA). Tested with both the pre-exisiting dates in my spreadsheet and dates entered while the computer was set to the other time zones.
Your script writes a value into another cell in the spreadsheet, which is undesirable. It errors for me because that cell is explicitly formatted as text in my spreadsheet and therefore returns text instead of an AppleScript date. In situations where it doesn’t error, it’ll give the wrong result west of the Meridian because the AppleScript date in the original cell will then be sometime during the day before the displayed date and the final result will be 24 hours out.
I got my script finished to add a row to a Numbers table. It turned out that all I need with the dates was the date that is in the first cell of the last row of the table (as text), and the date retrieved from the curl call (also text). All I needed to do was see if they were the same - no date arithmetic at all.
AppleScript’s 2 main weak points are dates and file system paths. Those seem to take up 90 percent of the time of just about any project.
Anyway, I figured that the only way to “make” a range in Numbers is to slap together the string “A:B” by hand. Unbelievable but apparently true.
The Fill Down works beautifully with GUI scripting. Again, this is a menu item so it remains a mystery why it isn’t a command in the dictionary. Seems to be a 1:1 correspondence with a method in the app, but who am I to say.
logToConsole given theMessage:"TIAARealEstatePriceToNumbers Executed."
if weekday of (current date) is in {Sunday, Monday} then return --stock market closed
logToConsole given theMessage:"Weekday Identified."
set {tiaaFund, tiaaPrice, tiaaDate} to getTIAARealEstateAcctInfo()
set TIAAWorkbookName to "NewTIAARealEstate.numbers"
tell application "Finder" to set TIAAPath to "Snow Leopard:Users:johnnylundy:Desktop:NewTIAARealEstate.numbers"
tell application "Numbers"
activate
open TIAAPath
tell document "NewTIAARealEstate" to tell sheet "Real Estate Sheet" to tell table "Table 1"
set LastDate to the value of the first cell of the last row
if LastDate is equal to tiaaDate then
tell me to logToConsole given theMessage:"Fund info for date retrieved from TIAA (" & tiaaDate & ") is already in spreadsheet - new row not added."
else
add row below last row
set NewRow to last row
tell first cell of NewRow
set its value to tiaaDate
set its alignment to right
end tell
set value of second cell of NewRow to tiaaPrice
set nextToLastRow to row ((address of NewRow) - 1)
set RangeStart to name of third cell of nextToLastRow
set RangeEnd to name of 8th cell of NewRow
set selection range to range (RangeStart & ":" & RangeEnd)
-- Do the Fill Down
tell application "System Events" to tell process "Numbers" to tell menu bar 1 to ¬
tell menu bar item 5 to tell menu 1 to tell menu item 10 to tell menu 1 to click menu item 2
end if
end tell --table1
end tell --application "Numbers"
to getTIAARealEstateAcctInfo()
set ATID to (a reference to AppleScript's text item delimiters)
set tiaaURL to "https://www3.tiaa-cref.org/ddata/DownloadData?f81=1009&days=1&dldformat=1&submit=submit&maxsize=999"
set tiaaResult to do shell script "curl -k -w 10 " & quoted form of tiaaURL
set savedDelims to contents of ATID
set contents of ATID to {","}
set {theFund, thePrice, rawDate} to text items of tiaaResult
set contents of ATID to return
set theDate to first text item of rawDate
set contents of ATID to savedDelims
return {theFund, thePrice, theDate}
end getTIAARealEstateAcctInfo
to logToConsole given theMessage:theMessage
do shell script "logger -t getDailyTIAAQuote" & space & quoted form of theMessage
end logToConsole
I use every days this kind of instruction :
set the selected range to range (name of cell rowNum1 of column colNum1 &":"& name of cell rowNum2 of column colNum2)
Yvan KOENIG (VALLAURIS, France) mardi 6 septembre 2011 00:01:28
I understand, but I guess I was thinking they would have some nicer syntax, such as
set table1's range to (MyCell57 thru MyCell99)
since there is already a syntax like that.