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?
The version of it you quoted earlier has the last line missing. It won’t do anything without that. If you click the “Open in Script Editor” under what I posted, you should get the whole thing in Script Editor (after an annoying couple of “Do you want to allow this?” dialogs).
I have this entire thing in the scripter window and it won’t execute. The arrow clicks but it does nothing, as if it’s immediately aborting?
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
Also, I see that you do address the “missing value” here but can you kindly add what’s needed to the other script for the telephone formatting so that’s also covered?
Now if I need to add a second column search (Column G, search for “Messaging” and replace with “Text”) do I need to repeat the entire thing with those changes, or can it be put into the same script?
So I worked on your script to add the second search/replace column and came up with this, which works. Wondering if you see any places where it could be cleaned up a bit?
on main()
script o
property allValues : missing value
end script
-- Copy this entire section to duplicate the search in another column --
tell application "Numbers"
activate
tell table 1 of sheet 1 of document 1
set rowCount to row count
-- Select the whole of column G and get its values.
set selection range to range ("G1:G" & 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 "Messaging") then
set o's allValues's item r to "Text"
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
---------------------------------------------------------------------------
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. One approach would be to have the selection range cover both columns, since they’re adjacent. Within this approach, there are three possible ways to fetch and handle the cell contents.
Just get the values of the selection range’s cells. This would give a flat list of the values, every two values being from the same row and each alternate one being from the same column.
Get the values the selection range’s columns’ cells. This would give a list containing two sublists, each sublist containing the values from one of the columns.
Get the values of the selection range’s rows’ cells. This would give a list of rowCount sublists, each sublist containing the values from one of the rows — although they’d be all the values from that row, not just those in the selected part.
Options 1 and 3 seem to be easiest from the point of view of massaging the edited data into a form where they can be pasted back into the document in one go. They’d also be quite easy to expand if more columns needed to be included. Option 1 seems to be the simplest and fastest in the present case:
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 columns G and H and get the cell values.
-- These are returned as a flat list of the values from
-- G1, H1, G2, H2, G3, H3, … etc.
set selection range to range ("G1: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 * 2) by 2
set gVal to o's allValues's item r
if (gVal contains "Messaging") then
set gVal to "Text"
else if (gVal is missing value) then
set gVal to ""
end if
set o's allValues's item r to gVal & tab -- Append a tab to the G value.
set hVal to o's allValues's item (r + 1)
if (hVal contains "in") then
set hVal to "Incoming"
else if (hVal contains "out") then
set hVal to "Outgoing"
else if (hVal is missing value) then
set hVal to ""
end if
set o's allValues's item (r + 1) to hVal & linefeed -- Append a linefeed to the H value.
end repeat
-- Coerce the list to text with "" delimiters and send it to the clipboard.
set the clipboard to join(o's allValues, "")
-- Paste the result into the selected Numbers columns.
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()
Okay, that was unbelievably fast. To give you some context, doing this process within Applescript itself, for 6500 rows, took 2 min. 50 sec. What you just gave me took… are you ready for this… 3 SECONDS! I am just blown away. Even running both routines separately (what you modified) took 8 seconds, so this is obviously just blazing. The question is, for future use would this always require adjacent columns to work, or could you somehow modify it to work on any identified columns in the script?
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.