Faster Search/Replace In Numbers Columns

Nigel_Garvey · July 11, 2023, 11:24am

Hi pavilion.

It’s good to hear your script’s coming along well. Here’s an illustration (not a complete, tested script) of how a flexible multi-column handler might be arranged for non phone-number columns. Rather than have separate parameters for every piece of text, a list structure could be passed containing all the search/replace texts for the entire range and the handler could simply (!) loop through it. The structure’s explained in the comments.

on main()
	tell application "Numbers"
		activate
		set theTable to document 1's sheet 1's table 1
		set rowCount to theTable's row count
	end tell
	
	-- Assuming each column has its own changes or set of changes to be made,
	-- the idea here is to get a list of lists representing the columns, each column sublist
	-- containing lists of text pairs for the search/replace operations.
	
	-- For instance, for the columns "G" and "H" handled in a previous script.
	set cellRange to "G1:H" & columnCount -- The cell range text may as well be set here.
	set gColumnChanges to {{"Messaging", "Text"}} -- One possible change in column "G".
	set hColumnChanges to {{"in", "Incoming"}, {"out", "Outgoing"}} -- Two in column "H".
	set allColumnChanges to {gColumnChanges, hColumnChanges}
	
	(* -- Or for a single column with only one text replacement defined.
	set cellRange to "X1:X" & columnCount
	set allColumnChanges to {{{"aardvark", "banana"}}}
	*)
	
	doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)
end main

on doMultipleColumns(theTable, rowCount, cellRange, changeCriteria)
	script o
		property cellValues : missing value
	end script
	
	set o's cellValues to selectAndGetValuesFromRange(theTable, cellRange)
	-- There should be the same number of lists in changeCriteria as there are columns to handle!
	set columnCount to (count changeCriteria)
	repeat with r from 1 to rowCount
		-- Get a list of the columnCount values from thisRow.
		set rowValues to o's cellValues's items (r * columnCount - (columnCount - 1)) thru (r * columnCount)
		-- Edit each value as necessary using the relevant search/replace criteria.
		repeat with c from 1 to columnCount
			set cellValue to rowValues's item c
			set relevantCriteria to changeCriteria's item c
			repeat with thisChange in relevantCriteria
				set {searchText, replaceText} to thisChange
				if (cellValue contains searchText) then
					set cellValue to replace_chars(cellValue, searchText, replaceText)
				end if
			end repeat
			set rowValues's item c to cellValue
		end repeat
		-- Coerce the row list to text with a tab delimiter, append a tab at the end,
		-- and store the text in slot r of o's cellValues.
		set o's cellValues's item r to join(rowValues, tab) & tab
	end repeat
	-- Coerce the stored texts to a single linefeed-delimited one and paste.
	set columnText to join(o's cellValues, linefeed)
	pasteIntoNumbers(columnText)
end doMultipleColumns

pavilion · July 11, 2023, 12:37pm

Thanks for this - not sure how much of the prior script needed to be added back, but after doing so as best I could, it’s getting errors on the items (r * columnCount - (columnCount - 1)) thru (r * columnCount) line.

use AppleScript version "2.4" -- OS X 10.10 (Yosemite) or later
use framework "Foundation"
use scripting additions
global columnCount

on main()
	tell application "Numbers"
		activate
		set theTable to document 1's sheet 1's table 1
		set rowCount to theTable's row count
		set columnCount to count of columns in range "G:H" of theTable
	end tell
	
	-- Assuming each column has its own changes or set of changes to be made,
	-- the idea here is to get a list of lists representing the columns, each column sublist
	-- containing lists of text pairs for the search/replace operations.
	
	-- For instance, for the columns "G" and "H" handled in a previous script.
	set cellRange to "G1:H" & columnCount -- The cell range text may as well be set here.
	set gColumnChanges to {{"Messaging", "Text"}} -- One possible change in column "G".
	set hColumnChanges to {{"in", "Incoming"}, {"out", "Outgoing"}} -- Two in column "H".
	set allColumnChanges to {gColumnChanges, hColumnChanges}
	
	(* -- Or for a single column with only one text replacement defined.
	set cellRange to "X1:X" & columnCount
	set allColumnChanges to {{{"aardvark", "banana"}}}
	*)
	
	doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)
end main

on doMultipleColumns(theTable, rowCount, cellRange, changeCriteria)
	script o
		property cellValues : missing value
	end script
	
	set o's cellValues to selectAndGetValuesFromRange(theTable, cellRange)
	-- There should be the same number of lists in changeCriteria as there are columns to handle!
	set columnCount to (count changeCriteria)
	repeat with r from 1 to rowCount
		-- Get a list of the columnCount values from thisRow.
		set rowValues to o's cellValues's items (r * columnCount - (columnCount - 1)) thru (r * columnCount)
		-- Edit each value as necessary using the relevant search/replace criteria.
		repeat with c from 1 to columnCount
			set cellValue to rowValues's item c
			set relevantCriteria to changeCriteria's item c
			repeat with thisChange in relevantCriteria
				set {searchText, replaceText} to thisChange
				if (cellValue contains searchText) then
					set cellValue to replace_chars(cellValue, searchText, replaceText)
				end if
			end repeat
			set rowValues's item c to cellValue
		end repeat
		-- Coerce the row list to text with a tab delimiter, append a tab at the end,
		-- and store the text in slot r of o's cellValues.
		set o's cellValues's item r to join(rowValues, tab) & tab
	end repeat
	-- Coerce the stored texts to a single linefeed-delimited one and paste.
	set columnText to join(o's cellValues, linefeed)
	pasteIntoNumbers(columnText)
end doMultipleColumns

on replace_chars(this_text, search_string, replacement_string)
	set AppleScript's text item delimiters to the search_string
	set the item_list to every text item of this_text
	set AppleScript's text item delimiters to the replacement_string
	set this_text to the item_list as string
	set AppleScript's text item delimiters to ""
	return this_text
end replace_chars

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

on selectAndGetValuesFromRange(theTable, cellRange)
	tell application "Numbers"
		set theTable's selection range to theTable's range cellRange
		return theTable's selection range's cells's formatted value
	end tell
end selectAndGetValuesFromRange

on pasteIntoNumbers(txt)
	set the clipboard to txt
	tell application "System Events"
		set frontmost of application process "Numbers" to true
		keystroke "v" using {shift down, option down, command down}
	end tell
	delay 1 -- Allow time for the paste to complete. (Adjust if/as necessary.)
end pasteIntoNumbers

main()

Nigel_Garvey · July 11, 2023, 1:40pm

Believe it or not, that’s because in main(), I used ‘columnCount’ in the range text setting instead of ‘rowCount’! But fixing that’s shown up that the text edits aren’t going as planned. I’m looking into it now ….

pavilion · July 11, 2023, 2:00pm

Still not clear why columnCount needs to be declared and rowCount doesn’t.

Nigel_Garvey · July 11, 2023, 2:05pm

OK. Tested this time.

on main()
	tell application "Numbers"
		activate
		set theTable to document 1's sheet 1's table 1
		set rowCount to theTable's row count
	end tell
	
	-- Assuming each column has its own changes or set of changes to be made,
	-- the idea here is to get a list of lists representing the columns, each column sublist
	-- containing lists of text pairs for the search/replace operations.
	
	-- For instance, for the columns "G" and "H" handled in a previous script.
	set cellRange to "G1:H" & rowCount -- The cell range text may as well be set here.
	set gColumnChanges to {{"Messaging", "Text"}} -- One possible change in column "G".
	set hColumnChanges to {{"in", "Incoming"}, {"out", "Outgoing"}} -- Two in column "H".
	set allColumnChanges to {gColumnChanges, hColumnChanges}
	
	(* -- Or for a single column with only one text replacement defined.
	set cellRange to "X1:X" & rowCount
	set allColumnChanges to {{{"aardvark", "banana"}}}
	*)
	
	doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)
end main

on doMultipleColumns(theTable, rowCount, cellRange, changeCriteria)
	script o
		property cellValues : missing value
	end script
	
	set o's cellValues to selectAndGetValuesFromRange(theTable, cellRange)
	-- There should be the same number of lists in changeCriteria as there are columns to handle!
	set columnCount to (count changeCriteria)
	repeat with r from 1 to rowCount
		-- Get a list of the columnCount values from thisRow.
		set rowValues to o's cellValues's items (r * columnCount - (columnCount - 1)) thru (r * columnCount)
		-- Edit each value as necessary using the relevant search/replace criteria.
		repeat with c from 1 to columnCount
			set cellValue to rowValues's item c
			if (cellValue is missing value) then
				set cellValue to ""
			else
				set relevantCriteria to changeCriteria's item c
				repeat with thisChange in relevantCriteria
					set {searchText, replaceText} to thisChange
					if (cellValue contains searchText) then
						set cellValue to replace_chars(cellValue, searchText, replaceText)
					end if
				end repeat
			end if
			set rowValues's item c to cellValue
		end repeat
		-- Coerce the row list to text with a tab delimiter, append a tab at the end,
		-- and store the text in slot r of o's cellValues.
		set o's cellValues's item r to join(rowValues, tab) & tab
	end repeat
	-- Coerce the stored texts to a single linefeed-delimited one and paste.
	set columnText to join(o's cellValues's items 1 thru r, linefeed)
	pasteIntoNumbers(columnText)
end doMultipleColumns

on replace_chars(this_text, search_string, replacement_string)
	set astid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to the search_string
	set the item_list to every text item of this_text
	set AppleScript's text item delimiters to the replacement_string
	set this_text to the item_list as text
	set AppleScript's text item delimiters to astid
	return this_text
end replace_chars

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

on selectAndGetValuesFromRange(theTable, cellRange)
	tell application "Numbers"
		set theTable's selection range to theTable's range cellRange
		return theTable's selection range's cells's formatted value
	end tell
end selectAndGetValuesFromRange

on pasteIntoNumbers(txt)
	set the clipboard to txt
	tell application "System Events"
		set frontmost of application process "Numbers" to true
		keystroke "v" using {shift down, option down, command down}
	end tell
	delay 1 -- Allow time for the paste to complete. (Adjust if/as necessary.)
end pasteIntoNumbers

main()

pavilion · July 11, 2023, 11:47pm

Just tested the script as posted (the column range line set that’s now commented out) and it works like a charm!

I then tested the single column variant by using your sample code and repeating the doMultipleColumns command in two separate blocks as shown below, which I assume is how it should look when replacing text in each column separately, but as you can see when you test it yourself, the results in the cells are mangled.

I also put another doMultipleColumns line in the earlier column range block so that, in effect, each search/replace style has its own doMultipleColumns command. Not sure this is the best way to do this, so please review and improve as you see fit.

Also, what do you mean by this comment: “-- The cell range text may as well be set here.” What cell range text are you referring to?

	set cellRange to "G1:G" & rowCount
	set allColumnChanges to {{"Messaging", "Text"}}
	doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)
	
	set cellRange to "H1:H" & rowCount
	set allColumnChanges to {{"in", "Incoming"}, {"out", "Outgoing"}}
	doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)

on main()
	tell application "Numbers"
		activate
		set theTable to document 1's sheet 1's table 1
		set rowCount to theTable's row count
	end tell
	
	-- Assuming each column has its own changes or set of changes to be made,
	-- the idea here is to get a list of lists representing the columns, each column sublist
	-- containing lists of text pairs for the search/replace operations.
	
	-- For instance, for the columns "G" and "H" handled in a previous script.
	
	--set cellRange to "G1:H" & rowCount -- The cell range text may as well be set here.
	--set gColumnChanges to {{"Messaging", "Text"}} -- One possible change in column "G".
	--set hColumnChanges to {{"in", "Incoming"}, {"out", "Outgoing"}} -- Two in column "H".
	--set allColumnChanges to {gColumnChanges, hColumnChanges}
	--doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)
	
	(* -- Or for a single column with only one text replacement defined.
	set cellRange to "X1:X" & rowCount
	set allColumnChanges to {{{"aardvark", "banana"}}}
	*)
	
	set cellRange to "G1:G" & rowCount
	set allColumnChanges to {{"Messaging", "Text"}}
	doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)
	
	set cellRange to "H1:H" & rowCount
	set allColumnChanges to {{"in", "Incoming"}}
	doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)
	
end main

on doMultipleColumns(theTable, rowCount, cellRange, changeCriteria)
	script o
		property cellValues : missing value
	end script
	
	set o's cellValues to selectAndGetValuesFromRange(theTable, cellRange)
	-- There should be the same number of lists in changeCriteria as there are columns to handle!
	set columnCount to (count changeCriteria)
	repeat with r from 1 to rowCount
		-- Get a list of the columnCount values from thisRow.
		set rowValues to o's cellValues's items (r * columnCount - (columnCount - 1)) thru (r * columnCount)
		-- Edit each value as necessary using the relevant search/replace criteria.
		repeat with c from 1 to columnCount
			set cellValue to rowValues's item c
			if (cellValue is missing value) then
				set cellValue to ""
			else
				set relevantCriteria to changeCriteria's item c
				repeat with thisChange in relevantCriteria
					set {searchText, replaceText} to thisChange
					if (cellValue contains searchText) then
						set cellValue to replace_chars(cellValue, searchText, replaceText)
					end if
				end repeat
			end if
			set rowValues's item c to cellValue
		end repeat
		-- Coerce the row list to text with a tab delimiter, append a tab at the end,
		-- and store the text in slot r of o's cellValues.
		set o's cellValues's item r to join(rowValues, tab) & tab
	end repeat
	-- Coerce the stored texts to a single linefeed-delimited one and paste.
	set columnText to join(o's cellValues's items 1 thru r, linefeed)
	pasteIntoNumbers(columnText)
end doMultipleColumns

on replace_chars(this_text, search_string, replacement_string)
	set astid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to the search_string
	set the item_list to every text item of this_text
	set AppleScript's text item delimiters to the replacement_string
	set this_text to the item_list as text
	set AppleScript's text item delimiters to astid
	return this_text
end replace_chars

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

on selectAndGetValuesFromRange(theTable, cellRange)
	tell application "Numbers"
		set theTable's selection range to theTable's range cellRange
		return theTable's selection range's cells's formatted value
	end tell
end selectAndGetValuesFromRange

on pasteIntoNumbers(txt)
	set the clipboard to txt
	tell application "System Events"
		set frontmost of application process "Numbers" to true
		keystroke "v" using {shift down, option down, command down}
	end tell
	delay 1 -- Allow time for the paste to complete. (Adjust if/as necessary.)
end pasteIntoNumbers

main()

Nigel_Garvey · July 12, 2023, 7:46am

pavilion:

I then tested the single column variant by using your sample code and repeating the doMultipleColumns command in two separate blocks as shown below, which I assume is how it should look when replacing text in each column separately, but as you can see when you test it yourself, the results in the cells are mangled.

[…]
	set cellRange to "G1:G" & rowCount
	set allColumnChanges to {{"Messaging", "Text"}}
	doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)
	
	set cellRange to "H1:H" & rowCount
	set allColumnChanges to {{"in", "Incoming"}, {"out", "Outgoing"}}
	doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)

Hi pavilion.

allColumnChanges in the above examples should be wrapped another set of braces:

set cellRange to "G1:G" & rowCount
set allColumnChanges to {{{"Messaging", "Text"}}}
doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)

set cellRange to "H1:H" & rowCount
set allColumnChanges to {{{"in", "Incoming"}, {"out", "Outgoing"}}}
doMultipleColumns(theTable, rowCount, cellRange, allColumnChanges)

The structure is:
a. a list
b. containing one or more sublists, one for each column,
c. each column sublist containing one or more subsublists, one for each substitution specified for that column,
d. each substitution subsublist containing the search and replace texts for that substitution.

It’s a bit confusing a first, but pretty flexible. Hope it makes sense.

Er — the cell range text set in the line containing the comment. The text indicating the range of cells to be treated. In the earlier version of the script I was looking at when I put this one together, this setting was done in the individual do…() handlers. Here for convenience, it’s specified in the main() handler at the top of the script, along with the changes to be made, and passed as a parameter to any other handler that may need it.

pavilion · July 12, 2023, 12:47pm

Hi Nigel - after your terrific explanation the extra braces makes total sense, and that one small change made all the difference. Thanks so much!

I removed that one comment referring to a prior incarnation of the script in order to save others from also wondering what it meant, and I made one more change to the code, besides renaming some of the handlers to make it even easier to follow, which is:

I split the main() handler into two, singleColumnSearch() and multipleColumnSearch() so that both sets of code can always remain uncommented (at my age I find it hard to work with commented code shown in gray - yes I know I can change that color, but still my eyes usually gloss over comments when I am not specifically looking for them) and then all that needs to be commented out, if necessary, is one of the two lines at top.

The only problem I am having is, that I wanted to separate out this one common set of lines setting up the sheet and row count, since that shouldn’t be necessary to repeat twice, but it’s not getting called correctly inside either singleColumnSearch() or multipleColumnSearch().

If you could look at that, and generally bless or improve the remainder of the script as shown below, then I think we’re ready to lock this one down and call it a major success!

tell application "Numbers"
		activate
		set theTable to document 1's sheet 1's table 1
		set rowCount to theTable's row count
end tell

singleColumnSearch()
--multipleColumnSearch()

tell application "Numbers"
	activate
	set theTable to document 1's sheet 1's table 1
	set rowCount to theTable's row count
end tell

on singleColumnSearch()
	set cellRange to "G1:G" & rowCount
	set allColumnChanges to {{{"Messaging", "Text"}}}
	searchReplace(theTable, rowCount, cellRange, allColumnChanges)
	
	set cellRange to "H1:H" & rowCount
	set allColumnChanges to {{{"in", "Incoming"}, {"out", "Outgoing"}}}
	searchReplace(theTable, rowCount, cellRange, allColumnChanges)
end singleColumnSearch

on multipleColumnSearch()
	-- Assuming each column has its own changes or set of changes to be made,
	-- the idea here is to get a list of lists representing the columns, each column sublist
	-- containing lists of text pairs for the search/replace operations.	
	set cellRange to "G1:H" & rowCount
	set gColumnChanges to {{"Messaging", "Text"}} -- One possible change in column "G".
	set hColumnChanges to {{"in", "Incoming"}, {"out", "Outgoing"}} -- Two in column "H".
	set allColumnChanges to {gColumnChanges, hColumnChanges}
	searchReplace(theTable, rowCount, cellRange, allColumnChanges)
end multipleColumnSearch

on searchReplace(theTable, rowCount, cellRange, changeCriteria)
	script o
		property cellValues : missing value
	end script
	
	set o's cellValues to selectAndGetValuesFromRange(theTable, cellRange)
	-- There should be the same number of lists in changeCriteria as there are columns to handle!
	set columnCount to (count changeCriteria)
	repeat with r from 1 to rowCount
		-- Get a list of the columnCount values from thisRow.
		set rowValues to o's cellValues's items (r * columnCount - (columnCount - 1)) thru (r * columnCount)
		-- Edit each value as necessary using the relevant search/replace criteria.
		repeat with c from 1 to columnCount
			set cellValue to rowValues's item c
			if (cellValue is missing value) then
				set cellValue to ""
			else
				set relevantCriteria to changeCriteria's item c
				repeat with thisChange in relevantCriteria
					set {searchText, replaceText} to thisChange
					if (cellValue contains searchText) then
						set cellValue to replaceText(cellValue, searchText, replaceText)
					end if
				end repeat
			end if
			set rowValues's item c to cellValue
		end repeat
		-- Coerce the row list to text with a tab delimiter, append a tab at the end,
		-- and store the text in slot r of o's cellValues.
		set o's cellValues's item r to join(rowValues, tab) & tab
	end repeat
	-- Coerce the stored texts to a single linefeed-delimited one and paste.
	set columnText to join(o's cellValues's items 1 thru r, linefeed)
	pasteIntoNumbers(columnText)
end searchReplace

on replaceText(this_text, search_string, replacement_string)
	set astid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to the search_string
	set the item_list to every text item of this_text
	set AppleScript's text item delimiters to the replacement_string
	set this_text to the item_list as text
	set AppleScript's text item delimiters to astid
	return this_text
end replaceText

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

on selectAndGetValuesFromRange(theTable, cellRange)
	tell application "Numbers"
		set theTable's selection range to theTable's range cellRange
		return theTable's selection range's cells's formatted value
	end tell
end selectAndGetValuesFromRange

on pasteIntoNumbers(txt)
	set the clipboard to txt
	tell application "System Events"
		set frontmost of application process "Numbers" to true
		keystroke "v" using {shift down, option down, command down}
	end tell
	delay 1 -- Allow time for the paste to complete. (Adjust if/as necessary.)
end pasteIntoNumbers

Also, can I assume that any other things that need to be done to the sheet (i.e. formatting columns and cells and such) would simply go inside that main() handler either above or below the existing lines that performs he search/replace?

So, for the benefit of anyone else following this, let me just explain again what we’re doing here. Numbers does not allow search/replace on selected rows, columns, cells - it’s the entire sheet or nothing, which is frankly insane. Moreover, even if it did, that wouldn’t help solve the problem of repeating such a task through multiple sheets, which this script does both elegantly and quickly.

The main difference between this rev and all the earlier posts is, that in order to have the most flexibility in both the target range and search terms, that required digging through all the main code to customize as needed. Here, what @Nigel_Garvey has brilliantly done is incorporate all of the user-input values inside the small main() section on top, leaving the rest of the code invulnerable to newbies messing it up

And as to why the need for separate code to handle both adjoining columns and individual columns, it’s all about speed, which matters most when the sheets are very large or when there are multiple sheets being processed. So my suggestion is to always take the multiple columns route when that is feasible, and only use the separate columns code when it is not.

Nigel_Garvey · July 12, 2023, 1:35pm

Hi pavilion,

The multipleColumnSearch() handler in the script I wrote is so named for historical reasons. It can actually handle single columns as well. So there shouldn’t be a need for a singleColumnSearch() handler now. You do need to reinstate the formatPhone() handler though, and any calls to it, as it works on a different principle, editing an entire column at once rather than examining individual cells.

pavilion · July 12, 2023, 1:53pm

Understood and agreed.

I just split them up because for me it’s a little easier to have my sample search/replace lines always uncommented, which I obviously can’t do when only calling one type of search or the other. Meaning, that I have to always comment/uncomment multiple lines each time the script runs.

By having them put into two calls that use the same multipleColumnSearch() handler, I can just comment out the call I don’t need up top, and leave everything else uncommented. Not sure this makes sense to a pro like you, but for me it’s easier to work that way.

In that case, can this code be made to work outside of the two handlers so that I don’t need to repeat it both times?

tell application "Numbers"
	activate
	set theTable to document 1's sheet 1's table 1
	set rowCount to theTable's row count
end tell

UPDATE: This seems to do the trick:

use AppleScript version "2.4" -- OS X 10.10 (Yosemite) or later
use framework "Foundation"
use scripting additions
global theTable, rowCount

tell application "Numbers"
	activate
	set theTable to the document 1's sheet 1's table 1
	set rowCount to theTable's row count
end tell

singleColumnSearch()
--multipleColumnSearch()
--formatPhone("F", theTable, rowCount)
--formatPhone("I", theTable, rowCount)

on singleColumnSearch()
	set cellRange to "G1:G" & rowCount
	set allColumnChanges to {{{"Messaging", "Text"}}}
	searchReplace(theTable, rowCount, cellRange, allColumnChanges)
	set cellRange to "H1:H" & rowCount
	set allColumnChanges to {{{"in", "Incoming"}, {"out", "Outgoing"}}}
	searchReplace(theTable, rowCount, cellRange, allColumnChanges)
end singleColumnSearch

on multipleColumnSearch()
	-- Assuming each column has its own changes or set of changes to be made,
	-- the idea here is to get a list of lists representing the columns, each column sublist
	-- containing lists of text pairs for the search/replace operations.	
	set cellRange to "G1:H" & rowCount
	set gColumnChanges to {{"Messaging", "Text"}} -- One possible change in column "G".
	set hColumnChanges to {{"in", "Incoming"}, {"out", "Outgoing"}} -- Two in column "H".
	set allColumnChanges to {gColumnChanges, hColumnChanges}
	searchReplace(theTable, rowCount, cellRange, allColumnChanges)
end multipleColumnSearch

on searchReplace(theTable, rowCount, cellRange, changeCriteria)
	script o
		property cellValues : missing value
	end script
	
	set o's cellValues to selectAndGetValuesFromRange(theTable, cellRange)
	-- There should be the same number of lists in changeCriteria as there are columns to handle!
	set columnCount to (count changeCriteria)
	repeat with r from 1 to rowCount
		-- Get a list of the columnCount values from thisRow.
		set rowValues to o's cellValues's items (r * columnCount - (columnCount - 1)) thru (r * columnCount)
		-- Edit each value as necessary using the relevant search/replace criteria.
		repeat with c from 1 to columnCount
			set cellValue to rowValues's item c
			if (cellValue is missing value) then
				set cellValue to ""
			else
				set relevantCriteria to changeCriteria's item c
				repeat with thisChange in relevantCriteria
					set {searchText, replaceText} to thisChange
					if (cellValue contains searchText) then
						set cellValue to replaceText(cellValue, searchText, replaceText)
					end if
				end repeat
			end if
			set rowValues's item c to cellValue
		end repeat
		-- Coerce the row list to text with a tab delimiter, append a tab at the end,
		-- and store the text in slot r of o's cellValues.
		set o's cellValues's item r to join(rowValues, tab) & tab
	end repeat
	-- Coerce the stored texts to a single linefeed-delimited one and paste.
	set columnText to join(o's cellValues's items 1 thru r, linefeed)
	pasteIntoNumbers(columnText)
end searchReplace

on formatPhone(columnLetter, theTable, rowCount)
	set cellRange to columnLetter & "1:" & columnLetter & rowCount
	set cellValues to selectAndGetValuesFromRange(theTable, cellRange)
	set columnText to join(cellValues, tab & linefeed) & tab -- NEW: TAB AT THE END AS WELL.
	set columnText to current application's class "NSMutableString"'s stringWithString:(columnText)
	tell columnText to replaceOccurrencesOfString:("(?m)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})(?=\\t$)") withString:("($1) $2-$3") ¬
		options:(current application's NSRegularExpressionSearch) range:({0, its |length|()})
	tell columnText to replaceOccurrencesOfString:("missing value") withString:("") options:(0) range:({0, its |length|()})
	pasteIntoNumbers(columnText as text)
end formatPhone

on replaceText(this_text, search_string, replacement_string)
	set astid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to the search_string
	set the item_list to every text item of this_text
	set AppleScript's text item delimiters to the replacement_string
	set this_text to the item_list as text
	set AppleScript's text item delimiters to astid
	return this_text
end replaceText

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

on selectAndGetValuesFromRange(theTable, cellRange)
	tell application "Numbers"
		set theTable's selection range to theTable's range cellRange
		return theTable's selection range's cells's formatted value
	end tell
end selectAndGetValuesFromRange

on pasteIntoNumbers(txt)
	set the clipboard to txt
	tell application "System Events"
		set frontmost of application process "Numbers" to true
		keystroke "v" using {shift down, option down, command down}
	end tell
	delay 1 -- Allow time for the paste to complete. (Adjust if/as necessary.)
end pasteIntoNumbers

Nigel_Garvey · July 13, 2023, 8:08am

“Pro”'s rather overstating things. But otherwise yes. If the script’s for your own use and the main part now does what’s required, how you arrange the input is entirely up to you.

Looks good.

pavilion · July 13, 2023, 1:47pm

Really do appreciate everything and hope others who land here make great use of it as well!

pavilion · July 13, 2023, 2:03pm

Now onto the rest of the script, a question for which I’ve created a new post, if you have a chance to read it.