I am new to Macs and Applescript and am trying to automate a Numbers 9 spreadsheet on which i keep my household accounts. What I want to do is that when i receive a statement I will check off the transactions that match and eventually delete them. Currently I have got to the stage where I want to move items that have been checked to a different area of the spreadsheet.
So far I have got this far and hit a problem
tell application “Numbers”
tell document 1
tell table 1 of sheet 1
global cellcount1, cellcount2
global celltext
global colida, colidb, colidc, colidd, colide, colidf, colidg, colidh
global colidj, colidk, colidl, colidm, colidn, colido, colidp
global cellref, cellref2, cellref3
set cellcount1 to 6
set cellcount2 to 6
set colida to “a”
set colidb to “b”
set colidc to “c”
set colidd to “d”
set colide to “e”
set colidf to “f”
set colidg to “g”
set colidh to “h”
set colidj to “j”
set colidk to “k”
set colidl to “l”
set colidm to “m”
set colidn to “n”
set colido to “o”
set colidp to “p”
clear range “j7:o52”
set value of cell 6 of column “n” to value of cell “e6”
set value of cell 6 of column “o” to value of cell “f6”
set value of cell 6 of column “p” to value of cell “g6”
set cellcount1 to cellcount1 + 1
set cellcount2 to cellcount2 + 1
set cellref to colidh & cellcount1
set celltext to value of cell cellref
if celltext is equal to “X” then
set cellref2 to colidb & cellcount1
set value of cell cellcount2 of column colidk to value of cell cellref2
set cellref2 to colidc & cellcount1
set value of cell cellcount2 of column colidl to value of cell cellref2
set cellref2 to colidd & cellcount1
set value of cell cellcount2 of column colidm to value of cell cellref2
set cellref2 to colide & cellcount1
set value of cell cellcount2 of column colidn to value of cell cellref2
set cellref2 to colidf & cellcount1
set value of cell cellcount2 of column colido to value of cell cellref2
set cellref2 to colida & cellcount1
set value of cell cellcount2 of column colidj to value of cell cellref2
end if
end tell
end tell
end tell
This works fine until the statement before the end if is reached when I get an error “error “Numbers got an error: AppleEvent handler failed.” number -10000”. I am assuming this is because the cells in column a contain dates in the UK DD/MM/YY format. All the other columns contain either text or numerical values
Any help on this would be gratefully received.
I would also like to know if there is a source somewhere that I can find all the Applescript Syntax. Currently I am relying on what i have learnt from other Applescripts and a 2008 guide I found on a website. I may be missing something but I can’t get any syntax from the Applescript Editor Help
Hi ElBeardo and welcome to both applescript and the mac. You can find the basic applescript syntax by choosing “Show applescript language guide” under the help menu of the “Applescript Editor” application. That will be the syntax that works with most any application.
In addition to the basic syntax, each application has their own specific applescript syntax so the language guide will only help to a point. You need to also see the specific applescript syntax for each application. We call that the applescript dictionary for an application. To see an application’s applescript dictionary choose “Open Dictionary” under the File menu in Applescript Editor.
I don’t personally use Pages so I can’t help with your specific Pages issue.
tell application "Numbers"
tell document 1
tell table 1 of sheet 1
(*
Defining these variables as global is useless
global cellcount1, cellcount2
global celltext
global colida, colidb, colidc, colidd, colide, colidf, colidg, colidh
global colidj, colidk, colidl, colidm, colidn, colido, colidp
global cellref, cellref2, cellref3
*)
set cellcount1 to 6
set cellcount2 to 6
(*
Describing columns by their index is more efficient.
It allows calculations upon them and it allows the use of loops.
*)
set colida to 1 --"a"
(*
set colidb to 2 --"b"
set colidc to 3 --"c"
set colidd to 4 --"d"
*)
set colide to 5 --"e"
set colidf to 6 --"f"
set colidg to 7 --"g"
set colidh to 8 --"h"
set colidj to 10 --"j"
(*
set colidk to 11 --"k"
set colidl to 12 --"l"
set colidm to 13 --"m"
*)
set colidn to 14 --"n"
(*
set colido to 15 --"o"
set colidp to 16 --"p"
*)
clear range "j7:o52"
(*
set value of cell 6 of column colidn to value of cell "e6"
set value of cell 6 of column colido to value of cell "f6"
set value of cell 6 of column colidp to value of cell "g6"
*)
set off7 to colidn - colide
tell row cellcount1
repeat with c from colide to colidg
set value of cell (c + off7) to value of cell c
end repeat
end tell
set cellcount1 to cellcount1 + 1
set cellcount2 to cellcount2 + 1
(*
set cellref to name of cell colidh of row cellcount1
set celltext to value of cell cellref
*)
set celltext to value of cell colidh of row cellcount1
if celltext is equal to "X" then
(*
--set cellref2 to colida & cellcount1
set value of cell cellcount2 of column colidj to value of cell cellcount1 of column colida
--set cellref2 to colidb & cellcount1
set value of cell cellcount2 of column colidk to value of cell cellcount1 of column colidb
--set cellref2 to colidc & cellcount1
set value of cell cellcount2 of column colidl to value of cell cellcount1 of column colidc
--set cellref2 to colidd & cellcount1
set value of cell cellcount2 of column colidm to value of cell cellcount1 of column colidd
--set cellref2 to colide & cellcount1
set value of cell cellcount2 of column colidn to value of cell cellcount1 of column colide
--set cellref2 to colidf & cellcount1
set value of cell cellcount2 of column colido to value of cell cellcount1 of column colidf
*)
set off7 to colidj - colida
repeat with c from colida to colidf
set maybe to value of cell cellcount1 of column c
(*
Trickery required by an odd feature of the pair Numbers + AppleScript
*)
if class of maybe is date then set maybe to maybe as text
set value of cell cellcount2 of column (c + off7) to maybe
end repeat
end if
end tell -- table 1 of sheet 1
end tell -- Document 1
end tell -- Numbers
With a second pass of cleaning, it become :
tell application "Numbers"
tell document 1
tell table 1 of sheet 1
set cellcount1 to 6
set cellcount2 to 6
set colida to 1 --"a"
set colide to 5 --"e"
set colidf to 6 --"f"
set colidg to 7 --"g"
set colidh to 8 --"h"
set colidj to 10 --"j"
set colidn to 14 --"n"
clear range "j7:o52"
set off7 to colidn - colide
tell row cellcount1
repeat with c from colide to colidg
set value of cell (c + off7) to value of cell c
end repeat
end tell
set cellcount1 to cellcount1 + 1
set cellcount2 to cellcount2 + 1
if value of cell colidh of row cellcount1 is "X" then
set off7 to colidj - colida
repeat with c from colida to colidf
set maybe to value of cell cellcount1 of column c
(*
Trickery required by an odd feature of the pair Numbers + AppleScript
*)
if class of maybe is date then set maybe to maybe as text
set value of cell cellcount2 of column (c + off7) to maybe
end repeat
end if
end tell -- table 1 of sheet 1
end tell -- Document 1
end tell -- Numbers
I forgot that in countries where the decimal separator is comma, we must also coerce floating values as text before filling a cell with them so the script would be better edited this way :
tell application "Numbers"
tell document 1
tell table 1 of sheet 1
set cellcount1 to 6
set cellcount2 to 6
set colida to 1 --"a"
set colide to 5 --"e"
set colidf to 6 --"f"
set colidg to 7 --"g"
set colidh to 8 --"h"
set colidj to 10 --"j"
set colidn to 14 --"n"
clear range "j7:o52"
set off7 to colidn - colide
tell row cellcount1
repeat with c from colide to colidg
set value of cell (c + off7) to value of cell c
end repeat
end tell
set cellcount1 to cellcount1 + 1
set cellcount2 to cellcount2 + 1
if value of cell colidh of row cellcount1 is "X" then
set off7 to colidj - colida
repeat with c from colida to colidf
set value of cell cellcount2 of column (c + off7) to (value of cell cellcount1 of column c) as text
end repeat
end if
end tell -- table 1 of sheet 1
end tell -- Document 1
end tell -- Numbers
That’s right. If your date cells are formatted as dates in Numbers, their values are returned to a script as AppleScript date objects. However, Numbers only accepts date input in one of the text formats it understands, so you can’t set the value of one date cell directly to that of another. Yvan’s scripts coerce the date object from the source cell to text and set the value of the destination cell to the result. If you were immediately to get the value of the destination cell, it too would be returned as an AppleScript date object. And even though the cell’s value was set to a date string in your computer’s local format, the value displayed in Numbers will be in the short-date format to which the cell’s been formatted!
The difference between the input and output formats isn’t a principle of AppleScript. It’s one of those application developer inconsistencies against which AppleScripters constantly have to be on their guard.
Nigel is perfectly right when he wrote about “designers inconsistencies” but, I assume that the OP’s problem is not to know which did a bad work but how to use the app as it exists.
As the OP asked about dates I didn’t thought to an other drawback.
In Numbers there is no date object and there is no time object. There are only date_time objects.
Using :
set value of cell r2 of column c2 to (value of cell r1 of column c1) as text
when the original cell contain a date time value isn’t sufficient.
We must code :
set value of cell r2 of column c2 to ((value of cell r1 of column c1) - (time to GMT)) as text
I add that I hope that Apple will understand soon that ‘time to GMT’ is obsolete and that the correct spelling is ‘time to UTF’.
Sorry to take so long to reply but these last few days I seem, unusually, to have spent a lot of time away from home so I didn’t get round to looking at replies properly until this morning.
Thank you all very much I am overwhelmed by the number of replies, the information from Hank and Fistoprince on where to find user guides, that has been most helpful.
Nigel and Yvan your information has proved to be most enlightening and illuminating and I have learnt a lot about Applescript in a very short time, especially Yvan’s corrections to my original script. Other languages I have used would only work if the variables were declared beforehand hence the list of Globals and having worked with Excel for over 20 years cell references have always been a1, b1, etc.
I have run Yvan’s script against my spreadsheet and it worked, there are however a few points which I wonder if I could trouble you to clarify.
I am assuming the “clear” statement fills the range of cells with nulls and and removes all formatting, is there an alternative which doesn’t clear the formatting? The reason for asking is point 2.
One of the cells moved across contained null, but the copied cell now contains 0.0, is there anything I can do about this. Thinking about this while typing I suppose if there is a “format” statement I can apply it to columns and specify if it is numeric or text?
The date field copied across originally was “18/06/08” the recipient cell contains " 18 June 2008 01:00:00" am I correct that this is now a text field? If I now want to show this cell as 18/06/08 again, is there a "format’ statement I can use or do I have to write some script to split this text field up into its component parts?
A bit of both actually, as ElBeardo’s trying to learn AppleScript and his first project is for an application with a particularly inept scripting implementation. The purpose of my post was to explain how and why your script(s) solved his problem of not being able to set a cell’s value to a date already held in another ” and to reassure him that it’s a Numbers peculiarity rather than an AppleScript one.
There are quite a few people in the GMT time zone who’ll disagree with you there.
The actual problem with ‘time to GMT’ is that it returns the time to GMT for the date on which the script’s run, not for the date to which it’s applied. Since the time’s not required here, it would be better to get the date’s ‘date string’ rather than coercing the whole date/time to text.
set value of cell r2 of column c2 to date string of (get value of cell r1 of column c1)
You can set the value of each of the cells to an empty string (“”), but it takes a long time if there are a lot of them!
This is another of Numbers’s peculiarities. The values of both empty cells and of cells containing the number 0.0 are returned to AppleScript as 0.0! In my own Numbers home accounts scripts, I get round this by changing each 0.0 value returned to “” ” but this only works because none of the scripts ever needs to set a cell’s value to the number 0.0
¨
It does look as though the date-as-text is being written to a cell which hasn’t previously been formatted as a date field. You can set its format retrospectively by script to ‘date and time’, but then (on my machne) it shows “18/06/2008 01:00”. It’s best to format the destination cells by hand, using the Inspector, when you intially set up the document.
It is amazing how having one little piece of information can solve a lot of the difficulties you experience. I spent a long time yesterday trying to detect if cells were empty by testing for null or space without success, eventually I set the cells concerned to space manually and my detection then worked fine. From what you are saying I assume that 0 was being presented to Applescript from the empty cell and that is why my test failed.
I have found that if I go into Inspector and format the cell that has “18 June 2008 01:00:00” in it I can then get it to appear as 18/06/08 again.
I am, at last, starting to feel comfortable with what I am doing, I don’t suppose it is the ideal solution but I am part way to automating my spreadsheet the way I want to
