I have this simple script that searches/replaces text values in a column, and it works fine. However, it takes a significant amount of time to run for even only a few thousand cells.
Is there anything I can do to speed up that process? For example, is there any way to invoke the native search/replace function within Numbers that does the same thing in mere seconds instead of literally going from cell to cell all the way down, or is there a better way to script this?
repeat with x in column "H"'s cells
tell x to if its value contains "in" then set its value to "Incoming"
tell x to if its value contains "out" then set its value to "Outgoing"
end repeat
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set inCells to a reference to (cells of column "H" whose value contains "in")
set outCells to a reference to (cells of column "H" whose value contains "out")
set value of inCells to "Incomming"
set value of outCells to "Outgoing"
end tell
Thanks for that, and it definitely runs faster, but still taking 1:32 for each file, of which I have hundreds. Any other ideas you might have to cut down that time further, or is the physical cell iteration process, one by one, unavoidable?
That script is not iterating „one by one“. It gets all matching cells in two lists and then updates these. I don’t know if a faster solution is possible with AppleScript.
Well that explains why it’s faster than my version, but it’s quite amazing that it still takes this long when the same process natively in Numbers takes 2 seconds!
Would it make any difference if some of the cells were blank, meaning to say, that it skipped those cells when creating the lists?
I was hoping that would improve the timing but it’s the same as before. I noticed that the entire delay is due to the “Spreadsheet updating” that shows the cells in gray and puts the cursor in busy mode, and assumed your code to ignore the application responses would deal with that, but i guess not.
How many rows do you have per column? The fact is that the command WHOSE with a certain large number of rows starts to “slow down”. The repeat loop inside the tell block is even worse.
It is better to break the work with rows into several pieces. The speed should improve. Here is an example of working with 2 pieces:
Â
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set value of cells of range "H1:H200" whose value contains "in" to "Incomming"
set value of cells of range "H1:H200" whose value contains "out" to "Outgoing"
set value of cells of range "H201:H400" whose value contains "in" to "Incomming"
set value of cells of range "H201:H400" whose value contains "out" to "Outgoing"
end tell
So here is what I used and it was a MASSIVE improvement, going from 1:55 to only :20. So that’s awesome. Question is, I noticed you used this code instead of the prior one below where you loaded the range into a variable. Should I try that as well broken up?
set (value of cells of range "H1:H1000" whose value contains "in") to "Incomming"
set (value of cells of range "H1:H1000" whose value contains "out") to "Outgoing"
set (value of cells of range "H1001:H2000" whose value contains "in") to "Incomming"
set (value of cells of range "H1001:H2000" whose value contains "out") to "Outgoing"
set (value of cells of range "H2001:H3000" whose value contains "in") to "Incomming"
set (value of cells of range "H2001:H3000" whose value contains "out") to "Outgoing"
set (value of cells of range "H3001:H4000" whose value contains "in") to "Incomming"
set (value of cells of range "H3001:H4000" whose value contains "out") to "Outgoing"
set (value of cells of range "H4001:H5000" whose value contains "in") to "Incomming"
set (value of cells of range "H4001:H5000" whose value contains "out") to "Outgoing"
set (value of cells of range "H5001:H6000" whose value contains "in") to "Incomming"
set (value of cells of range "H5001:H6000" whose value contains "out") to "Outgoing"
set inCells to a reference to (cells of column "H" whose value contains "in")
set outCells to a reference to (cells of column "H" whose value contains "out")
set value of inCells to "Ingoing"
set value of outCells to "Outgoing"
Also, the other issue is that each spreadsheet has a different number of rows, so can you modify this to dynamically split the known number of rows into smaller chunks, with a variable set to the chunk number so that I can then control it without having individually change the actual row ranges, which I would never know in advance?
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set piece to 1000
set rowCount to row count
set C to 0
repeat
try
set R to C + 1
set C to C + piece
set RC to "H" & R & ":H" & C -- the next range
set value of cells of range RC whose value contains "in" to "Incomming"
set value of cells of range RC whose value contains "out" to "Outgoing"
if C = rowCount then exit repeat
on error
set RC to "H" & R & ":H" & rowCount -- the rest range
set value of cells of range RC whose value contains "in" to "Incomming"
set value of cells of range RC whose value contains "out" to "Outgoing"
exit repeat
end try
end repeat
end tell
The script processes per 1000 rows. Let’s say you have 6200 lines. Then you should have for the last piece: 6001-6200.
The normal part of the TRY-block gives 6001-7000 for the 7th piece. Since there is no such range (>6200), an error is thrown. This error is caught by the ON ERROR part of the TRY block and fixes 6001-7000 to 6001-6200.
This is quite fast in with Numbers 10.1 in Mojave:
on main()
script o
property allValues : missing value
end script
tell application "Numbers"
activate
tell table 1 of sheet 1 of document 1
set rowCount to row count
-- Select the whole of column H and get its values.
set selection range to range ("H1:H" & rowCount)
set o's allValues to value of cells of selection range
end tell
end tell
-- Edit the values in-script.
repeat with r from 1 to rowCount
set this to o's allValues's item r
if (this contains "in") then
set o's allValues's item r to "Incoming"
else if (this contains "out") then
set o's allValues's item r to "Outgoing"
else if (this is missing value) then
set o's allValues's item r to ""
end if
end repeat
-- Coerce the list to text with linefeed delimiters and send it to the clipboard.
set the clipboard to join(o's allValues, linefeed)
-- Paste the result into the selected Numbers column.
tell application "System Events"
set frontmost of application process "Numbers" to true -- Probably overkill.
keystroke "v" using {shift down, option down, command down}
end tell
end main
on join(lst, delim)
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to delim
set txt to lst as text
set AppleScript's text item delimiters to astid
return txt
end join
main()
Well. It won’t do anything if you leave out the last line, of course. Apart from that, the assumptions are that you want to change values in column “H” of table 1 of sheet 1 of document 1 (as in KniazidisR’s scripts, which appear to work for you), that Numbers scripting is broadly similar on both our systems, and that GUI scripting is enabled on yours.
So I circled back here to try your script on the search/replace, and I now have GUI enabled but when the try to run it, nothing happens. I press the arrow and there’s no error or anything, but it doesn’t say the script is running or anything. What could I be doing wrong?
Apart from that, the assumptions are that you want to change values in column “H” of table 1 of sheet 1 of document 1 (as in KniazidisR’s scripts, which appear to work for you), that Numbers scripting is broadly similar on both our systems, and that GUI scripting is enabled on yours.