I have a range selected in Apple Numbers - say its “A1:B2”
Its a good range as I can pass the contents to a list and the list to the clipboard and see the values -->{“1”,“2”,“3”,“4”}
I then define another list {“5”,“6”,“7”,“8”} and set the clipboard contents to this new list
When I try paste the clipboard contents to the range using “Tell application “System Events” to keystroke “v” using command down”, nothing happens. I don’t get an error and the logs or messages in AS Editor don’t show anything (my MacBook makes a chirruping sound but that’s it)
I have often used this command in many different cases without any problems but I recently upgraded to Monterey 12.7.2 and I am wondering if the whole System Events suite has been deprecated?
Why don’t you script “Numbers” directly using it’s dictionary like so…
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
property myData : {}
local myWIn, myDoc, mySheet, myTable, myRange, myCells, replaceList
tell application "Numbers"
activate
set myWIn to window 1
set myDoc to document of myWIn
set mySheet to sheet 1 of myDoc
set myTable to table 1 of mySheet
set myRange to selection range of myTable
set myCells to cells of myRange
set myData to value of cells of myRange
set replaceList to {5, 6, 7, 8}
repeat with i from 1 to count myCells
set value of item i of myCells to item i of replaceList
end repeat
end tell
When pasting into a selected range, the pasted item should be a single text representing the selected block. The items in each line (row) should be separated by tabs and lines from each other by returns or linefeeds.
Thanks for the suggestion Robert. The reason is one of speed. I need to zero out the contents of a set of large tables and recursing through every cell will take some time.
We used to be able to create the selection range and paste the contents of a blank list (with the same dimensions as the range) in one shot using the clipboard and a System Events keystroke of Command paste.
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
--property myData : {}
local myWIn, myDoc, mySheet, aTable, myTables, myRange, myCells, replaceList, fnd
tell application "Numbers"
activate
set myWIn to window 1
set myDoc to document of myWIn
set mySheet to sheet 1 of myDoc
set myTables to tables of mySheet
set fnd to false
repeat with aTable in myTables -- find table that is selected if there are more than 1 table
if (count cells of selection range of aTable) ≠ 0 then
set myTable to contents of aTable
set myRange to selection range of aTable
set fnd to true -- table with selection found
exit repeat
--else
--tell me to say "Table " & (name of aTable) & " does not have a selection!"
end if
end repeat
if fnd then -- a table that was selected was found
set myCells to cells of myRange
--set myData to value of cells of myRange
set replaceList to {5, 6, 7, 8}
repeat with i from 1 to count myCells
if (value of item i of myCells) = missing value then
set value of item i of myCells to i --item i of replaceList
else
set value of item i of myCells to missing value
end if
end repeat
set selection range of myTable to myRange
end if
end tell
If I set the contents of the clipboard to “1, 2,”&“\r”&“3,4” and paste it to numbers it will work. I think this is your point.
I could have sworn that a correctly dimensioned list of lists such as {{“1”,“2”},{“3”,“4”}} would also have worked, but I must be thinking of something else.
Thank you for your time and thought Robert, it is much appreciated.
You suggestion does work, but it is cycling through every cell, one at a time. On a small table with a range of A1:G19, I counted 10 Hippopotamuses to complete the last value in G19, and in the reverse of the script with no values in the range, also 10 Hippopotamuses
I was thinking of a much faster process, and I think I may have to generate a formatted text string with carriage returns which can be pasted in one go and save 9 Hippopotamuses!
Here is an attempt that will paste into two columns. I didn’t actually read the last few posts until I was preparing to post this so a bit of a waste but yeah, I think the formatted string is the way to go.
It is easy enough to modify the sed command to have it wrap after three columns, etc… If more flexibility is needed you could probably input a number of columns or use the selection range and get a column count, and then construct the command accordingly.
When writing the script, I entered \t and \n which were automatically replaced with the appropriate spacing as seen below.
set bls to {"5", "6", "7", "8", "15", "16", "17", "18", "25", "26", "27", "28"}
set AppleScript's text item delimiters to tab
set tls to (bls as text) -- & tab
--> 5 6 7 8 15 16 17 18 25 26 27 28
set grid to do shell script "printf " & quoted form of tls & " | sed -E 's/([[:digit:]]+ [[:digit:]]+) /\\1\\
/g' "
set the clipboard to the grid
tell application "Numbers"
set t1 to table 1 of sheet 1 of document 1
tell t1
activate
set selection range of t1 to cell "A1" -- selection does not need to be a range but should be top left
tell application "System Events"
key code 9 using command down -- paste will follow tab/linefeed layout from 'grid'
end tell
end tell
end tell
The regex has a search for one or more digits, followed by a tab, followed by one or more digits followed by another tab. The first three components are grouped and used in the replacement along with a linefeed (substituting for the second tab). Originally I thought I would need to add a tab after the last digit but the script doesn’t seem to require it.
FWIW, I think most applications don’t know what to do with a list on the clipboard.
My apologies Mockman, I was referring back to your post here and realised that I had not replied with my thanks. I am very sorry, I could have sworn that I had, as I really appreciate your time and consideration in reading my question and then suggesting and writing out a reply.
Please accept my sincere and belated thanks for this suggestion. It works for me and I had incorporated it into one of my workflow scripts.
