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()
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.