I would like to copy and Paste Formula Results for a range of cells in Numbers, but the following script fails. I know I can set the value of cells, however I am trying to use this on ranges of cells. Any help would be appreciated.
tell application "Numbers" to tell document "Untitled"
set active sheet to sheet 1
tell sheet 1 to tell table 1
clear range "2:3"
set value of cell "B3" to 3
set value of cell "C3" to "=B3+1"
set selection range to range "B3:C3"
tell application "System Events" to tell (first process whose title is "Numbers")
keystroke "c" using {command down}
end tell
set selection range to range "E3:E3"
tell application "System Events" to tell (first process whose title is "Numbers")
keystroke "v" using {command down, shift down}
end tell
end tell
end tell
Model: Mac Mini
AppleScript: SE 2.7 AS 2.4
Browser: Safari 8.0.3
Operating System: Mac OS X (10.8)
When you try to copy then paste, the spreadsheet is not at front so the script doesn’t copy into it.
Run it again as it is then paste manually in a blank textEdit document to see what is in the clipboard.
After that, edit your script as :
tell application "Numbers" to tell document "Sans titre"
set active sheet to sheet 1
tell sheet 1 to tell table 1
clear range "2:3"
set value of cell "B3" to 3
set value of cell "C3" to "=B3+1"
set selection range to range "B3:C3"
tell application "System Events" to tell (first process whose title is "Numbers")
set frontmost to true # ADDED
keystroke "c" using {command down}
end tell
delay 0.5 # ADDED
set selection range to range "E3:E3"
tell application "System Events" to tell (first process whose title is "Numbers")
set frontmost to true # ADDED
keystroke "v" using {command down, shift down}
end tell
end tell
end tell
and run it.
This time it will work.
The set frontmost to true which I inserted before the paste instruction is not absolutely required.
If you are curious, you may remove the delay instruction.
Here, without it the clipboard is not pasted in the range E2:E3 but in a new table.
It’s the kind of behavior which push some Applescript experts to hate GUIscripting
Back to the script.
After running the edited version, look at the contents of the cell E3.
You will not find a formula but the result of the one inserted in C3.
I know that it’s not what you hoped but you can’t change the behavior of the couple Numbers + AppleScript.
As I am quite sure that your question is just using a sample code, I can’t give you a response matching your real needs.
When I face this kind of needs, I want to fill columns and I use the fill down submenu item.
For an easy use, I gave a shortcut to this submenu item thru the Keyboard system preference pane.
Issuing a shortcut is easier than triggering a submenu item which is location dependant.
Yvan KOENIG (VALLAURIS, France) vendredi 6 mars 2015 15:04:07
Thanks Yvan, you solved my dilemma. You nailed it and I’ve run into that before, but did not recall it for the new script I am writing. I spent a half day trying, but nothing worked. I could do it manually which frustrated me even more. I enjoy your posts as I am a Numbers guy and have used a number of your scripts. Thanks again.
Regards,
Terry