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?
In other words, can the script be built for more flexibility that can search only one column or any combination of multiple columns?
Even if this part of the code is restricted to adjacent columns, could the other code that does it column by column be incorporated, perhaps by an opening dialog that asks if the process is on adjacent columns (button A) or separate columns (button B), and then run whichever code is called?
Probably, but it’s past my bed time. 
Meanwhile, here for your amusement is a development which reformats phone numbers in column B (assuming the spreadsheets are the same as those in your other topic) as well as handling the stuff in columns G and H:
use AppleScript version "2.4" -- OS X 10.10 (Yosemite) or later
use framework "Foundation"
use scripting additions
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 B to H and get the *formatted* cell values.
-- These are returned as a flat list of the values from
-- B1, C1, D1, E1, F1, G1, H1, B2, C2, D3, E2, F2, G2, H2, … etc.
set selection range to range ("B1:H" & rowCount)
set o's allValues to formatted value of cells of selection range
end tell
end tell
-- Set up a regex object and replacement template to deal with phone numbers.
set phoneRegexPattern to "(?m)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})$"
set replacementTemplate to current application's class "NSString"'s ¬
stringWithString:("($1)" & character id 160 & "$2-$3")
set phoneRegex to current application's class "NSRegularExpression"'s ¬
regularExpressionWithPattern:(phoneRegexPattern) options:(0) |error|:(missing value)
-- Edit the values in-script.
repeat with r from 1 to rowCount
-- Get the cell values from our big list a row's worth at a time.
set rowValues to o's allValues's items (r * 7 - 6) thru (r * 7) -- 7 columns (B-H)/row.
repeat with c from 1 to 7
set cellValue to rowValues's item c
if (cellValue is missing value) then -- ANY instance of missing value.
set rowValues's item c to ""
else if (c = 1) then -- Formatted value from column "B".
set cellValue to (current application's class "NSString"'s stringWithString:(cellValue))
set cellValue to (phoneRegex's stringByReplacingMatchesInString:(cellValue) options:(0) ¬
range:({0, cellValue's |length|()}) withTemplate:(replacementTemplate))
set rowValues's item c to cellValue as text
else if ((c = 6) and (cellValue contains "Messaging")) then -- Ditto column "G".
set rowValues's item c to "Text"
else if (c = 7) then -- Ditto column "H".
if (cellValue contains "in") then
set rowValues's item c to "Incoming"
else if (cellValue contains "out") then
set rowValues's item c to "Outgoing"
end if
end if
end repeat
-- Store this row's values back in the big list as a tab-delimited text.
set o's allValues's item r to join(rowValues, tab)
end repeat
-- Coerce the stored rows from the big list to a single text using
-- linefeed delimiters and put the result on the clipboard.
set the clipboard to join(o's allValues's items 1 thru rowCount, linefeed)
-- 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()
Well, good night and thanks. Would love to see the search/replace script separated out from the phone formatting for the meantime so that I could learn how you’re doing everything, and then merge them after it works on both column configurations.
It also occurs to me that the complication of a dialog box choice is unnecessary, since the code can obviously detect on its own if the target columns are adjacent.
So effectively we just need, I think, an IF statement that says, if the target columns are alphabetically or numerically together, then run this code that combines the operation into one, and if not, run the other code that does it in two, or more steps.
The other thing that would make it much easier to manage is to put the column identifiers and the search/replace terms into variables at the top of the script so that they are easy to find and change without having to dig into the code.
I suppose in that case, then the code can know the targets are adjacent by merely detecting that the variable value is either just one letter or number, or if it’s a range of columns separated by a colon.
I’m offering the following JavaScript script. All relevant parameters can be set as constants at the top of the function (see comments). It works ok in that it changes phone numbers in the format xxxxxxxxxx and xxx.xxx.xxxx in the given columns to (xxx) xxx-xxx.
It does not work ok in two aspects:
- First, the header of the first column gets changed to the title of the second column. I have no f*g idea why. The clipboard looks fine just before pasting, but the table does not after pasting
- Second, a comma gets appended to the column header. That is necessary to force Number to interpret the clipboard contents as strings, not as numbers. Ah, well.
/* Anonymous, autoexecuting function to do the work */
(() => {
/* Set up some constants:
- the columns to search/replace in 'columnIndices' ('A' is 0, 'B' is 1 etc)
- the 'app' constant (like the tell block in AS)
- the table to work with
- the regular expression to find values that have to be modified
- the regular expression to match the phone numbers
- the replacement string to change the phone numbers
- and the 'currentApplication' which is needed (?) to work with the clipboard
*/
const columnIndices = [0,2]; // Select columns A and B
const app = Application("Numbers");
const table = app.documents[0].sheets[0].tables[0]; //in 1st doc: 1st table's 2nd sheet
const testRE = /^[0-9.]{10,12}$/
const dateRE = /^(\d{3})\.?(\d{3})\.?(\d{4})$/;
const replaceString = "($1) $2-$3";
const ca = Application.currentApplication();
ca.includeStandardAdditions = true;
/* Loop over the columns in 'columnIndices */
columnIndices.forEach(c => {
const currentCol = table.columns[c];
/* Get all values for the current column. That is an 'Array' in JavaScript */
const vals = currentCol.cells.value();
/* Loop over the values and create a new 'Array' containing
- the unmodified originals for any undefined/empty values or those not matching the 'test' condition
- the corrected phone numbers
*/
const newVals = vals.map(v => {
if (!v || ! testRE.test(v)) return v;
const nv = (v+"").replace(dateRE, replaceString);
return nv;
});
/* Set the clipboard to a newline separated string. Append a comma to the
first line to stop Numbers from treating the phone numbers as strings
*/
ca.setTheClipboardTo(newVals.join('\n').replace('\n',',\n'));
app.activate();
/* The the selection for the table */
table.selectionRange = currentCol;
delay(0.3);
/* Send the "paste without format" keystroke */
const se = Application('System Events');
se.keystroke('v',{using: ["shift down", "option down", "command down"]});
delay(0.3);
/* fix the first row */
currentCol.cells[0].value = newVals[0];
})
})()
Here’s another version that’s hopefully easier to understand and develop further. It allows the user to choose from the columns the script knows how to handle and initially fetches all the values from the table as a list of lists, each sublist containing the values from one of the rows.
use AppleScript version "2.4" -- OS X 10.10 (Yosemite) or later
use framework "Foundation"
use scripting additions
on main()
-- Choose from the letters of the columns this script can handle.
set columnsToCheck to (choose from list {"B", "G", "H"} with prompt ¬
"Choose which columns to check:" with multiple selections allowed)
if (columnsToCheck = false) then error number -128 -- "Cancel" button.
-- Note the first and last to set the selection range below.
tell columnsToCheck to set {firstCol, lastCol} to {its beginning, its end}
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 B to H.
set selection range to range (firstCol & "1:" & lastCol & rowCount)
-- Get every value in the table as a list of lists of row values.
set o's allValues to formatted value of cells of rows
end tell
end tell
-- Finished with the column letters. Get the corresponding numbers.
repeat with this in columnsToCheck
set this's contents to (offset of this in "ABCDEFGHIJKLMNOPQRSTUVWXYZ")
end repeat
tell columnsToCheck to set {firstCol, lastCol} to {its beginning, its end}
-- Set up a regex object and replacement template to deal with phone numbers.
set phoneRegexPattern to "(?m)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})$"
set replacementTemplate to current application's class "NSString"'s ¬
stringWithString:("($1)" & character id 160 & "$2-$3")
set phoneRegex to current application's class "NSRegularExpression"'s ¬
regularExpressionWithPattern:(phoneRegexPattern) options:(0) |error|:(missing value)
-- Edit the values in-script a row at a time.
repeat with r from 1 to rowCount
set rowValues to o's allValues's item r
-- It's only necessary to edit values from columns in the selection range.
repeat with columnNumber from firstCol to lastCol
set cellValue to rowValues's item columnNumber
-- Replace a missing value in ANY column with "".
-- Otherwise, if the value's from a column the user chose, deal with it appropriately.
if (cellValue is missing value) then
set rowValues's item columnNumber to ""
else if (columnNumber is in columnsToCheck) then
if (columnNumber = 2) then -- Column "B": phone numbers.
set cellValue to (current application's class "NSString"'s stringWithString:(cellValue))
set cellValue to (phoneRegex's stringByReplacingMatchesInString:(cellValue) options:(0) ¬
range:({0, cellValue's |length|()}) withTemplate:(replacementTemplate))
set rowValues's item columnNumber to cellValue as text
else if ((columnNumber = 7) and (cellValue contains "Messaging")) then -- Column "G".
set rowValues's item columnNumber to "Text"
else if (columnNumber = 8) then -- Column "H".
if (cellValue contains "in") then
set rowValues's item columnNumber to "Incoming"
else if (cellValue contains "out") then
set rowValues's item columnNumber to "Outgoing"
end if
end if
end if
end repeat
-- Store the relevant part of this row back into the big list as a tab-delimited text.
set o's allValues's item r to join(rowValues's items firstCol thru lastCol, tab)
end repeat
-- Coerce the stored row texts from the big list to a single text
-- using linefeed delimiters and put the result onto the clipboard.
set the clipboard to join(o's allValues, linefeed)
-- 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()
Ultimately, it is nonsense to search and replace it on Numbers.
Acquires all cell values from the Numbers, performs all processing on the array (list), writes the result in CSV, opened with Numbers, puts it in a tab -separated text, puts it in a clipboard, and paste it into Numbers. The fastest process.
I’ve worked with your modified version for a bit now and finally understand what you’re doing, which is essentially bridging all un-adjacent columns into a single operation, but this actually works much slower (very much) because you’re unnecessarily copying/pasting all the data in between those columns as well, which chokes the operation to a snail’s pace.
So, for example, if the target columns are A and Z, you’re literally processing every cell in between, when the brilliance of your original script was to only work on the affected columns, and make it even faster by grouping them if (and only if) they are adjacent.
The most efficient way for me is to have the script loop the code and copy/process/paste each target column and/or adjacent column pair in sequence. This way, only the affected data gets passed from and back to Numbers.
So at the top of the script, you might effectively something like this, which the code would then process in the listed order one at a time, with each line containing either a single column or column range, with a set of search/replace terms listed beside it.
Column (“G:H”) “In”, “Incoming”; “Out”, “Outgoing”
Column (“N”). “Messaging”, “Text”
The execution time with this bulk column method is, with lots of cells, on an order of magnitude slower because of all the data processing (especially pasting) for cells that are never otherwise needed. The beauty of your original script was that it performed an entire column in less than 2 seconds, and when combined with an adjacent column, in 3 seconds because of that efficiency. The latest script is taking in some cases close to a minute to complete.
So, because of the need for flexibility, I am now back to the first script that gets duplicated in large chunks for each column. The more elegant approach would be to have only one iteration of the code cycle through the columns as listed, using array variables to define the search/replace terms for each one.
The core of the code is awesome, it’s just this flexibility for multiple columns that can be processed in sequence (rather than in range) that is lacking.
This has separate handlers for column B and columns G/H. Also handlers containing parts of the code that can be reused if further expansion’s required. You’ll have to write your own column-specific code for any such expansion, though.
use AppleScript version "2.4" -- OS X 10.10 (Yosemite) or later
use framework "Foundation"
use scripting additions
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
doColumnB(theTable, rowCount)
doColumnsGAndH(theTable, rowCount)
end main
on doColumnB(theTable, rowCount)
set bValues to selectAndGetValuesFromRange(theTable, "B1:B" & rowCount)
-- Handle the values as a single (tab & linefeed)-delimited text.
-- (The tab's to make Numbers not take spaces for column separators.)
set bText to join(bValues, tab & linefeed)
set bText to current application's class "NSMutableString"'s stringWithString:(bText)
tell bText to replaceOccurrencesOfString:("missing value") withString:("") options:(0) range:({0, its |length|()})
tell bText to replaceOccurrencesOfString:("(?m)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})(?=\\t$)") withString:("($1) $2-$3") ¬
options:(current application's NSRegularExpressionSearch) range:({0, its |length|()})
pasteIntoNumbers(bText as text)
end doColumnB
on doColumnsGAndH(theTable, rowCount)
script o
property ghValues : missing value
end script
set o's ghValues to selectAndGetValuesFromRange(theTable, "G1:H" & rowCount)
-- Edit the values individually.
repeat with r from 1 to rowCount
set {gValue, hValue} to o's ghValues's items (r + r - 1) thru (r + r)
if (gValue is missing value) then
set gValue to ""
else if (gValue contains "Messaging") then
set gValue to "Text"
end if
if (hValue is missing value) then
set hValue to ""
else if (hValue contains "in") then
set hValue to "Incoming"
else if (hValue contains "out") then
set hValue to "Outgoing"
end if
-- Store each pair as a tab-joined text in a reused slot in o's ghValues.
set o's ghValues's item r to gValue & tab & hValue
end repeat
-- Coerce the stored texts to a single linefeed-delimited one and paste.
set ghText to join(o's ghValues's items 1 thru rowCount, linefeed)
pasteIntoNumbers(ghText)
end doColumnsGAndH
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()
Thanks so much Nigel!
So I have taken your script and tried to make the needed changes, but it’s not quite there yet… would really appreciate you looking it over and making the necessary efficiency improvements.
First. on the phone formatting code, since there are two separate columns that need it (F & I) and since both use the exact same code, can that code be put into its own handler with its own column references, in order to avoid what I’ve done here, which is to just copy and paste the entire routine twice? Also p.s., I added the one line inserting the (character id 160) to avoid the Numbers delimiter problem, so now it works great. Also, the last cell in the column is not getting formatted for some reason. Is it possible the defined range is not going to the very end?
I’m just now noticing your comment in the script about addressing the same Numbers import issue, but the code as originally posted didn’t fix that, so I added the (character id 160) line which did. Does that mean the tab code you mention may be superfluous and can be removed?
Handle the values as a single (tab & linefeed)-delimited text.
(The tab’s to make Numbers not take spaces for column separators.)
Second, in the search/replace text example you provided, it only does the adjacent range (G and H) which is great for that purpose, but since I also have a separate column (D) I’ve tried to modify it underneath, but it’s not working.
Ideally, if there was some way to isolate the column letter and search/replace terms from the code itself, then there would not be the need to repeat it each time, if that’s possible to do?
Thanks again.
use AppleScript version "2.4" -- OS X 10.10 (Yosemite) or later
use framework "Foundation"
use scripting additions
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
doColumnF(theTable, rowCount)
doColumnI(theTable, rowCount)
doColumnsGAndH(theTable, rowCount)
doColumnD(theTable, rowCount)
end main
on doColumnF(theTable, rowCount)
set bValues to selectAndGetValuesFromRange(theTable, "F1:F" & rowCount)
-- Handle the values as a single (tab & linefeed)-delimited text.
-- (The tab's to make Numbers not take spaces for column separators.)
set bText to join(bValues, tab & linefeed)
set bText to current application's class "NSMutableString"'s stringWithString:(bText)
tell bText to replaceOccurrencesOfString:("(?m)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})(?=\\t$)") withString:("($1) $2-$3") ¬
options:(current application's NSRegularExpressionSearch) range:({0, its |length|()})
tell bText to replaceOccurrencesOfString:("missing value") withString:("") options:(0) range:({0, its |length|()})
tell bText to replaceOccurrencesOfString:space withString:(character id 160) options:(0) range:({0, its |length|()})
pasteIntoNumbers(bText as text)
end doColumnF
on doColumnI(theTable, rowCount)
set bValues to selectAndGetValuesFromRange(theTable, "I1:I" & rowCount)
-- Handle the values as a single (tab & linefeed)-delimited text.
-- (The tab's to make Numbers not take spaces for column separators.)
set bText to join(bValues, tab & linefeed)
set bText to current application's class "NSMutableString"'s stringWithString:(bText)
tell bText to replaceOccurrencesOfString:("(?m)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})(?=\\t$)") withString:("($1) $2-$3") ¬
options:(current application's NSRegularExpressionSearch) range:({0, its |length|()})
tell bText to replaceOccurrencesOfString:("missing value") withString:("") options:(0) range:({0, its |length|()})
tell bText to replaceOccurrencesOfString:space withString:(character id 160) options:(0) range:({0, its |length|()})
pasteIntoNumbers(bText as text)
end doColumnI
on doColumnsGAndH(theTable, rowCount)
script o
property ghValues : missing value
end script
set o's ghValues to selectAndGetValuesFromRange(theTable, "G1:H" & rowCount)
-- Edit the values individually.
repeat with r from 1 to rowCount
set {gValue, hValue} to o's ghValues's items (r + r - 1) thru (r + r)
if (gValue is missing value) then
set gValue to ""
else if (gValue contains "Messaging") then
set gValue to "Text"
end if
if (hValue is missing value) then
set hValue to ""
else if (hValue contains "in") then
set hValue to "Incoming"
else if (hValue contains "out") then
set hValue to "Outgoing"
end if
-- Store each pair as a tab-joined text in a reused slot in o's ghValues.
set o's ghValues's item r to gValue & tab & hValue
end repeat
-- Coerce the stored texts to a single linefeed-delimited one and paste.
set ghText to join(o's ghValues's items 1 thru rowCount, linefeed)
pasteIntoNumbers(ghText)
end doColumnsGAndH
on doColumnD(theTable, rowCount)
script o
property ghValues : missing value
end script
set o's ghValues to selectAndGetValuesFromRange(theTable, "D1:D" & rowCount)
-- Edit the values individually.
repeat with r from 1 to rowCount
set {gValue, hValue} to o's ghValues's items (r + r - 1) thru (r + r)
if (gValue is missing value) then
set gValue to ""
else if (gValue contains "Monday") then
set gValue to "Sunday"
end if
-- Store each pair as a tab-joined text in a reused slot in o's ghValues.
set o's ghValues's item r to gValue & tab & hValue
end repeat
-- Coerce the stored texts to a single linefeed-delimited one and paste.
set ghText to join(o's ghValues's items 1 thru rowCount, linefeed)
pasteIntoNumbers(ghText)
end doColumnD
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()
Hi pavilion.
Here’s a slight reworking of your adaptation. It’s not been tested as my test document’s still set up for columns B, G, and H. But I’ve looked at it very hard.
Your doColumnF() and doColumnI() handlers now just fetch the values from their relevant columns and pass them to a common doPhoneStuff() handler. (Feel free to change the handler names if you don’t like them!) doColumnD()'s repeat only needs to span rowCount items (as opposed to doColumnsGAndH()'s rowCount * 2), so it’s a bit simpler.
By the way, I see you’re posting your AppleScript code indented by 4 spaces to get a window for it. If instead you put groups of three backticks on separate lines above and below it, the forum software will render it with a button that can be clicked to open it in people’s default script editors, eg.:
```
AppleScript code here.
```
Anyway. See how this goes:
use AppleScript version "2.4" -- OS X 10.10 (Yosemite) or later
use framework "Foundation"
use scripting additions
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
doPhoneColumn("F", theTable, rowCount)
doPhoneColumn("I", theTable, rowCount)
doColumnsGAndH(theTable, rowCount)
doSingleColumn("D", "Monday", "Sunday", theTable, rowCount)
end main
on doPhoneColumn(columnLetter, theTable, rowCount)
set cellRange to columnLetter & "1:" & columnLetter & rowCount
set columnValues to selectAndGetValuesFromRange(theTable, cellRange)
-- Handle phone column values as a single (tab & linefeed)-delimited text.
-- (The tab's to make Numbers not take spaces for column separators.)
set columnText to join(columnValues, 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|()})
tell columnText to replaceOccurrencesOfString:space withString:(character id 160) options:(0) range:({0, its |length|()})
pasteIntoNumbers(columnText as text)
end doPhoneColumn
on doColumnsGAndH(theTable, rowCount)
script o
property ghValues : missing value
end script
set o's ghValues to selectAndGetValuesFromRange(theTable, "G1:H" & rowCount)
-- Edit the values individually.
repeat with r from 1 to rowCount
set {gValue, hValue} to o's ghValues's items (r + r - 1) thru (r + r)
if (gValue is missing value) then
set gValue to ""
else if (gValue contains "Messaging") then
set gValue to "Text"
end if
if (hValue is missing value) then
set hValue to ""
else if (hValue contains "in") then
set hValue to "Incoming"
else if (hValue contains "out") then
set hValue to "Outgoing"
end if
-- Store each pair as a tab-joined text in a reused slot in o's ghValues.
set o's ghValues's item r to (gValue & tab) & (hValue & tab) -- NEW: TAB AT THE END AS WELL.
end repeat
-- Coerce the stored texts to a single linefeed-delimited one and paste.
set ghText to join(o's ghValues's items 1 thru rowCount, linefeed)
pasteIntoNumbers(ghText)
end doColumnsGAndH
on doSingleColumn(columnLetter, searchText, replacementText, theTable, rowCount)
script o
property columnValues : missing value
end script
set cellRange to columnLetter & "1:" & columnLetter & rowCount
set o's columnValues to selectAndGetValuesFromRange(theTable, cellRange)
-- Edit the values individually.
repeat with r from 1 to rowCount
set cellValue to o's columnValues's item r
if (cellValue is missing value) then
set cellValue to ""
else if (cellValue contains searchText) then
set cellValue to replacementText
end if
-- Append a tab and store the item back in o's columnValues.
set o's columnValues's item r to cellValue & tab
end repeat
-- Coerce the stored texts to a single linefeed-delimited one and paste.
set columnText to join(o's columnValues, linefeed)
pasteIntoNumbers(columnText)
end doSingleColumn
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()
Ok, wow, so everything works great (haven’t yet looked through the script, just ran it to test) but so far:
- The last values in Columns F & I are not getting reformatted.
UPDATE: This is another weird Numbers thing, because if I add a blank row underneath the last one, it formats those cells fine so it has to do with being the last record on the sheet. Still need a scripting solution that doesn’t leave me with that blank row but at least knowing this might help with that.
-
The single column D code (set dValue to o’s ghValues’s item r) is showing this error: error “Can’t get item 1 of ghValues.” number -1728 from item 1 of ghValues.
UPDATE: Fixed, just needed to change to dValues.
-
Do we still need this (“The tab’s to make Numbers not take spaces for column separators.”) if we’re using the (character id 160)?
Ah. That was probably because the regex was looking out for the tab in the (tab & linefeed) inserted between the entries. There’s no tab at the end of the last line, of course, so the regex wasn’t matching it. I’ve edited the script in my post above to make the tab match optional. Hopefully that’ll fix the problem.
I should have seen that. ghValues should be dValues in that handler. Now also fixed.
It’s probably not necessary to have both. If the tab works to preserve the single column during pasting, I’d be inclined to ditch the (character id 160) line. It would be one line fewer in the code, the ordinary spaces in the entries would be preserved, and the tab’s consistent with the tabs inserted or appended in the other handlers. But that’s just my aesthetic sense. 
That fixed the formatting on the last line, thanks!
Column D now works great as well. I assume that for future single columns, I must still duplicate the entire handler and customize the search terms, with no way around that. I had envisioned a single handler where the search terms and column letters could be stored in the On doColumnX() code at the top, so that all the columns would use the same single code block, and adding future columns would only require an additional On doColumnX() and call, similar to the way you’ve done the phone code.
When I remove the (character id 160) line the formatting goes to the next column, so that needs to stay. Can/should we take out the tabs or just leave them as is?
UPDATE: Just re-read your thoughts on consistency for having the tabs in the other handlers, so I’ll leave them in as you suggest.
For single columns with just one text to replace, you could use this instead of the doColumnD() handler:
on doSingleColumn(columnLetter, searchText, replacementText, theTable, rowCount)
script o
property cellValues : missing value
end script
set cellRange to columnLetter & "1:" & columnLetter & rowCount
set o's cellValues to selectAndGetValuesFromRange(theTable, cellRange)
-- Edit the values individually.
repeat with r from 1 to rowCount
set cellValue to o's cellValues's item r
if (cellValue is missing value) then
set cellValue to ""
else if (cellValue contains searchText) then
set cellValue to replacementText
end if
-- Append a tab and store the item back in o's cellValues.
set o's cellValues's item r to cellValue & 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 doSingleColumn
To call it for column “D”, you’d use:
doSingleColumn("D", "Monday", "Sunday", theTable, rowCount)
I’ve not been able to reproduce this. On my system, the presence of the inserted tabs is enough to prevent spaces in the cell values from being interpreted as field separators. However, I have noticed this morning that if the last cell in a phone number column is empty, it gets filled with the value from the first cell in that column. 
I’m not able to explain this at all, but it’s fixed by putting a tab after the last value too, which also fixes the last-cell-not-edited bug without the need to make matching the tab optional in the regex. I’ve updated the script above.
Hi - I haven’t looked into your earlier single column comment/code yet because I wanted to button down the tab issue, so when I run the latest revised script (Message 41) as-is, I’m getting an error on this line:
set columnText to join(columnValues, tab & linefeed) & tab – NEW: TAB AT THE END AS WELL
Error: The variable columnValues is not defined." number -2753 from columnValues
Looks like that was a carry-over from the earlier rev when you had:
on doPhoneStuff(columnValues)
UPDATE: Fixed it myself by changing it to cellValues. I’m starting to get the hang of this!
I assume, once this is fixed, that I want to see what happens if I remove the (character id 160) line?