Hello,
I updated to Mojave this week and now my script has a strange behavior with the keystroke in Numbers. When I copy a range of cells from one table to another table, it does not copy it to the right place. By debugging the script (copy from one cell to another in loop), I realized that the script runs too fast and paste it into the wrong cells. By putting a delay, the script works. Before the update, everything was functional on High Sierra with any delay. 
Thank you.
set sheetNameCoins to {"pièce 1 cent", "pièce 5 cents", "pièce 10 cents", "pièce 25 cents", "pièce 50 cents", "pièce 1 dollar", "pièce 2 dollars"}
on _copy()
tell application "System Events"
tell process "Numbers"
set frontmost to true
keystroke "c" using {command down}
-- Need delay on Mojave unlike High Sierra.
delay 0.5
end tell
end tell
end _copy
on _paste()
tell application "System Events"
tell process "Numbers"
set frontmost to true
keystroke "v" using {command down, option down, shift down}
-- Need delay on Mojave unlike High Sierra. If not delay, the copy is made in wrong cell.
delay 1
end tell
end tell
end _paste
(* Numbers document for coins/banknotes collection
For "synthèse pièces CAN", copy all value of column A (Year)
and column E (Value) of each denomination sheet
to the table "Data". About 1000 lines
*)
tell application "Numbers"
activate
tell document 1
set destRowIndex to 1
-- delete all rows. Necessary if coins are removed from collection.
tell table "Data" of sheet "synthèse pièces CAN"
set lastRow to row count - 1
delete (rows 1 through lastRow)
end tell
repeat with thisSheet in sheetNameCoins
-- copy/paste Year column
tell table 1 of sheet (thisSheet)
set sourceRowCount to row count - 1
set myRange to ("A2:A" & sourceRowCount)
set the selection range to range myRange
my _copy()
end tell
tell table "Data" of sheet "synthèse pièces CAN"
set selection range to cell ("A" & destRowIndex)
my _paste()
end tell
-- copy/paste Value column
tell table 1 of sheet thisSheet
set sourceRowCount to row count - 1
set myRange to ("E2:E" & sourceRowCount)
set the selection range to range myRange
my _copy()
end tell
tell table "Data" of sheet "synthèse pièces CAN"
set selection range to cell ("B" & destRowIndex)
my _paste()
add row below last row
set destRowIndex to destRowIndex + sourceRowCount - 1
end tell
end repeat
(* For "synthèse billets YUG", copy all value of column A (Year)
and column I (Value) of all tables on the sheet "billets - Yougoslavie"
to the table "Data". About 50 lines
*)
-- delete all rows. Necessary if banknotes are removed from collection.
tell table "Data" of sheet "synthèse billets YUG"
set lastRow to row count - 1
delete (rows 1 through lastRow)
end tell
set destRowIndex to 1
repeat with i from 1 to count of table of sheet "billets - Yougoslavie"
-- copy/paste Year column
tell table (i) of sheet "billets - Yougoslavie"
set sourceRowCount to row count - 1
set myRange to ("A2:A" & sourceRowCount)
set the selection range to range myRange
my _copy()
end tell
tell table "Data" of sheet "synthèse billets YUG"
set selection range to cell ("A" & destRowIndex)
my _paste()
end tell
-- copy/paste Value column
tell table (i) of sheet "billets - Yougoslavie"
set sourceRowCount to row count - 1
set myRange to ("I2:I" & sourceRowCount)
set the selection range to range myRange
my _copy()
end tell
tell table "Data" of sheet "synthèse billets YUG"
set selection range to cell ("B" & destRowIndex)
my _paste()
add row below last row
set destRowIndex to destRowIndex + sourceRowCount - 1
end tell
end repeat
end tell
end tell
Hi. Welcome to MacScripter.
Sorry you don’t seem to have received a reply before now, but it’s getting near Christmas and it takes a long time both to work out what your script’s supposed to do (you haven’t said and it’s not commented) and then to set up a test document on which to try it.
There is a of lot of view switching and copy/pasting going on in the script. It would remove the timing problem, and possibly speed things up, if you used Numbers’s own scriptable features instead, as below. But it may depend on what the cell values are. I’ve assumed here that they’re text and/or numbers.
set sheetNameCoins to {"pièce 1 cent", "pièce 5 cents", "pièce 10 cents", "pièce 25 cents", "pièce 50 cents", "pièce 1 dollar", "pièce 2 dollars"}
tell application "Numbers"
activate
tell document 1
-- Collect the values in the cells of columns "A" and "E" of the first tables of the sheets with the above names.
set columnAValues to {}
set otherColumnValues to {}
repeat with thisSheet in sheetNameCoins
tell table 1 of sheet thisSheet
set columnAValues to columnAValues & value of cells 2 thru -2 of column "A"
set otherColumnValues to otherColumnValues & value of cells 2 thru -2 of column "E"
end tell
end repeat
-- Insert the values into columns "A" and "B" respectively of table "Data" of sheet "synthèse pièces CAN".
set rowTotal to (count columnAValues)
tell table "Data" of sheet "synthèse pièces CAN"
set row count to rowTotal + 1
repeat with i from 1 to rowTotal
set value of cell i of column "A" to item i of columnAValues
set value of cell i of column "B" to item i of otherColumnValues
end repeat
end tell
-- Collect the values in the cells of columns "A" and "I" of every table of sheet "billets - Yougoslavie".
set columnAValues to {}
set otherColumnValues to {}
repeat with thisTable in tables of sheet "billets - Yougoslavie"
tell thisTable
set columnAValues to columnAValues & value of cells 2 thru -2 of column "A"
set otherColumnValues to otherColumnValues & value of cells 2 thru -2 of column "I"
end tell
end repeat
-- Insert the values into columns "A" and "B" respectively of table "Data" of sheet "synthèse pièces CAN".
set rowTotal to (count columnAValues)
tell table "Data" of sheet "synthèse billets YUG"
set row count to rowTotal + 1
repeat with i from 1 to rowTotal
set value of cell i of column "A" to item i of columnAValues
set value of cell i of column "B" to item i of otherColumnValues
end repeat
end tell
end tell
end tell
Hi Nigel,
thank you for your reply. Sorry for not commenting my code, I added a few lines.
The Numbers file is for my coins/banknotes collection.
What the script does for the two “synthèse” sheets, for example “synthèse pièces CAN”, from table on each sheet in the sheetNameCoins list, copy the A column that contains a Year, the E column that contains the Value of a coin and copy them into a “Data” table on the “synthèse” sheet. From this Data table, I have another table that is a pivot table to give me the sum of value, by year, regardless of the denomination of the coin.
I quickly tested the script before leaving at work. It works well, but with close to 1000 line to copy, it’s slow. Is there a “ScreenUpdating = False” (like Excel VBA) to not see the steps of each line in Numbers to speed up the script ?
Have a nice day.
Éric
Mmm. Yes. With a thousand lines, setting each cell value individually would be even slower than all those copy/paste operations.
Here’s a hybrid you may like to try. I don’t know if it’ll be any better. It gets the values from the cells using Numbers’s scripting implementation, massages them into text to put on the clipboard, and performs just one paste operation in each “Data” table, using your handler. I’ve commented out the delay, which works for me — but then I don’t have nearly a thousand entries to process!
main()
-- Code in a handler to keep all the variables local and therefore non-persistent.
on main()
set sheetNameCoins to {"pièce 1 cent", "pièce 5 cents", "pièce 10 cents", "pièce 25 cents", "pièce 50 cents", "pièce 1 dollar", "pièce 2 dollars"}
-- Script object by which to reference list variables, for speed.
script o
property columnAValues : missing value
property otherColumnValues : missing value
property syntheseRows : {}
end script
-- Preset AppleScript's text item delimiters to a linefeed.
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to linefeed
tell application "Numbers" to activate
-- Collect the values of the cells in columns "A" and "E" of the first table in each sheet of the front Numbers document which has one of the above names.
repeat with thisSheet in sheetNameCoins
-- Get the values in columns "A" and "E" in the first table of this sheet.
tell application "Numbers"
tell table 1 of sheet thisSheet of document 1
set o's columnAValues to value of cells 2 thru -2 of column "A"
set o's otherColumnValues to value of cells 2 thru -2 of column "E"
end tell
end tell
-- Join values from the same rows into single texts and append to the row collector list. It's best to enquote text values in Numbers 5.3.
repeat with i from 1 to (count o's columnAValues)
set aValue to item i of o's columnAValues
if (aValue's class is text) then set aValue to quote & aValue & quote
set otherValue to item i of o's otherColumnValues
if (otherValue's class is text) then set otherValue to quote & otherValue & quote
set end of o's syntheseRows to aValue & tab & otherValue
end repeat
end repeat
-- Coerce the collected rows to a single text (delimiter = linefeed) and place it on the clipboard.
set the clipboard to o's syntheseRows as text
-- Prepare table "Data" of sheet "synthèse pièces CAN" and paste the clipboard contents into it.
set rowTotal to length of o's syntheseRows
tell application "Numbers"
tell table "Data" of sheet "synthèse pièces CAN" of document 1
set row count to rowTotal + 1
set selection range to range ("A1:B" & rowTotal)
end tell
end tell
my _paste()
-- Similarly collect the values in columns "A" and "I" of every table in sheet "billets - Yougoslavie".
set o's syntheseRows to {}
tell application "Numbers" to set billetsTables to tables of sheet "billets - Yougoslavie" of document 1
repeat with thisTable in billetsTables
tell application "Numbers"
tell thisTable
set o's columnAValues to value of cells 2 thru -2 of column "A"
set o's otherColumnValues to value of cells 2 thru -2 of column "I"
end tell
end tell
-- Join values from the same rows into single texts as above and append to the row collector list.
repeat with i from 1 to (count o's columnAValues)
set aValue to item i of o's columnAValues
if (aValue's class is text) then set aValue to quote & aValue & quote
set otherValue to item i of o's otherColumnValues
if (otherValue's class is text) then set otherValue to quote & otherValue & quote
set end of o's syntheseRows to aValue & tab & otherValue
end repeat
end repeat
-- Coerce the collected rows to a single text (delimiter = linefeed) and place it on the clipboard.
set the clipboard to o's syntheseRows as text
-- Reset the TIDs to their original value.
set AppleScript's text item delimiters to astid
-- Prepare table "Data" of sheet "synthèse pièces CAN" and paste the clipboard contents into it.
set rowTotal to (length of o's syntheseRows)
tell application "Numbers"
tell table "Data" of sheet "synthèse billets YUG" of document 1
set row count to rowTotal + 1
set selection range to range ("A1:B" & rowTotal)
end tell
end tell
my _paste()
end main
on _paste()
tell application "System Events"
tell process "Numbers"
set frontmost to true
keystroke "v" using {command down, option down, shift down}
-- Need delay on Mojave unlike High Sierra. If not delay, the copy is made in wrong cell.
-- delay 1 -- uncomment and adjust as necessary.
end tell
end tell
end _paste
Hello Nigel ,
Work well and fast!
It’s possible to set TIDs on tab delimiter? The first use, “synthèse pièces CAN”, it’s ok, pasted with tab delimiter. The second, “synthèse billets YUG”, the delimiter is the comma. The locale on my iMac is French Canada and the decimal for number is comma. I can change with the popup in Numbers, but I am curious for why the first is ok but not the second.
Éric
Hi Éric.
I noticed that all sorts of characters were being used as delimiters when I was writing the script. When text is pasted into Numbers 2.3 on older systems, only tabs are used to separate cell values in the same row. But with Numbers 5.3 in Mojave, commas and spaces and other characters are treated as delimiters too. I tried enquoting text values to see if that would “fix” them, as in CSV text, and it did the trick. But I excluded number values from this because I didn’t want them to be mistaken for strings. However, in the light of your decimal comma problem, I’ve now tried enquoting numbers too and it seems to be OK. As long as the cells receiving them are formatted for numbers, they keep their format and the numbers are displayed accordingly. I don’t how to set up decimal commas without going into System Preferences, but I suspect it would work for you too.
Try changing both instances of this in the script above …
repeat with i from 1 to (count o's columnAValues)
set aValue to item i of o's columnAValues
if (aValue's class is text) then set aValue to quote & aValue & quote
set otherValue to item i of o's otherColumnValues
if (otherValue's class is text) then set otherValue to quote & otherValue & quote
set end of o's syntheseRows to aValue & tab & otherValue
end repeat
… to this:
repeat with i from 1 to (count o's columnAValues)
set aValue to quote & item i of o's columnAValues & quote
set otherValue to quote & item i of o's otherColumnValues & quote
set end of o's syntheseRows to aValue & tab & otherValue
end repeat
If it doesn’t work for you, I’ll have another think about it tomorrow (GMT).
Hi Nigel,
With enquoting numbers, all is OK now. 
Thank you for your time and help.
Éric.