Trying to format a plain text phone number of 2125551212 to (212) 555-1212 but the script keeps telling me that this is an invalid cell format. Any ideas on how to do this?
Unless we are limited only to the default format choices? Rather amazing that Numbers does not have any way to natively format a telephone number from the default choices, because it looks like there’s no way to script the custom format.
set the format of column "F" to "(###) ###-####"
We are limited only to the default format choices. The custom formats are not accessible to AppleScript.
You can create and remember custom format for your Numbers.app manually. Name it “PhoneNumbers” for example, then use it manually every time you need it.
This new custom format will not be accessible to AppleScript.
Here is how to create custom “PhoneNumbers” format: How does one format "phone numbers" using… - Apple Community
Yeah, I know I can do that but the chore is that I have hundreds of files I need to do this to and was hoping to use Applescript. What about doing it manually by parsing the number and adding the () and - where they would go and then populating the cell with the new value? Is that doable in Applescript, and how would I achieve that for an entire column?
The following is a proof-of-concept script that works on cell A1 only. It does what you want but would be slow and ponderous if edited to work on every cell in a column. If the cell does not contain 10 characters, the script does nothing.
tell application "Numbers"
tell table 1 of sheet 1 of document 1
try
set format of cell "A1" to text
set theValue to value of cell "A1"
if (count theValue) = 10 then
set theValue to "(" & text 1 thru 3 of theValue & ")" & " " & text 4 thru 6 of theValue & "-" & text 7 thru 10 of theValue
delay 0.2 -- may not be necessary
my formatTheCell(theValue)
end if
end try
end tell
end tell
on formatTheCell(theValue)
set the clipboard to theValue
tell application "System Events" to tell process "Numbers"
set frontmost to true
click menu item "Paste" of menu "Edit" of menu bar 1
end tell
end formatTheCell
If this seems usable, the cells in the desired column could be gotten with the following and the script would be modified to loop through each cell.
set theCells to every cell in column 1 whose value > ""
So performing this in 6,000 or more cells in a column will take forever, I’m gathering. Such a shame that you can’t perform a custom format via script like Excel can do. Maybe there’s a macro program that would perform the formating on each open spreadsheet.
I’ve modified my script to work on every cell in column 1. Just be sure to test this on a copy. I don’t think this would work reliably on a column of a spreadsheet with 6000 cells.
tell application "Numbers"
set theCells to (every cell in column 1 of table 1 of sheet 1 of document 1)
repeat with aCell in theCells
try
set format of aCell to text
set theValue to value of aCell
if (count theValue) = 10 then
set theValue to "(" & text 1 thru 3 of theValue & ")" & " " & text 4 thru 6 of theValue & "-" & text 7 thru 10 of theValue
formatTheCell(theValue) of me
end if
end try
end repeat
end tell
on formatTheCell(theValue)
set the clipboard to theValue
tell application "System Events" to tell process "Numbers"
set frontmost to true
click menu item "Paste" of menu "Edit" of menu bar 1
end tell
delay 0.1 -- test different values
end formatTheCell
You need GUI scripting to do this in Numbers.app. Because the custom format is not scriptable in other way here.
The workaround:
- Create for your Numbers.app custom PhoneNumbers fornat. (manually, only once)
- Open next spreadsheet (normal scripting)
- Select the column (normal scripting)
- Open Inspector (GUI scripting)
- Select Cell tab in it (GUI scripting)
- Select PhoneNumbers fornat (GUI scripting)
- Open next spreadsheet (normal scripting)…
Is that terribly difficult to do, the GUI scripting from within applescript itself?
Presumably the same method could be used to check the Alternating Row Colors option in the inspector.
I’m using Numbers 5.1. Has any subsequent version introduced any sophisticated and super advanced features such as copy/paste or fill down?
If the app did have such barely conceivable features, you could use a blank column to format the text with a formula and then dump the value of the results back into the original column.
An example formula:
tell application "Numbers"
set t1 to table 1 of sheet 1 of document 1
set vForm to "=\"(\" & LEFT(B1,3) & \") \" & MID(B1,4,3) & \"−\" & RIGHT(B1,4)"
-- "(" & LEFT(B1,3) & ") " & MID(B1,4,3) & "−" & RIGHT(B1,4)
set value of cell 1 of column "C" of t1 to vForm
end tell
Unfortunately, at least in my version, you would have to craft a specific formula for each cell. Or maybe there is a way to substitute the row number for each ‘B1’ in the formula.
There is a Cocoa framework project which wraps “libphonenumber.js”.
And…we can call it from AppleScript.
use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "objc-libphonenumber" --https://github.com/ksuther/objc-libphonenumber
use scripting additions
set pFormtter to current application's libphonenumberFormatter's alloc()'s init()
pFormtter's setCountryCode:"US"
pFormtter's setAlwaysUseInternationalFormat:true
set aRes to (pFormtter's stringForObjectValue:"2125551212") as string
--> "+1 212-555-1212"
set bRes to (pFormtter's stringForObjectValue:"+44 020 1234 5678") as string
--> "+44 20 1234 5678"
set cRes to (pFormtter's stringForObjectValue:"2125551212") as string
--> "+1 212-555-1212"
set dRes to (pFormtter's stringForObjectValue:"(212) 555-1212") as string
--> "+1 212-555-1212"
In addition to the GUI scripting method, you can use regular scripting:
WARNING: try this script on the duplicate of your .numbers file, because it will be rewritten (if uncooment the last export code line)
Something like this:
set tempFolder to (path to temporary items folder from user domain) as text
tell application "Numbers"
set docName to name of document 1
set numbersFileName to file of document 1
set CSVExportFileName to tempFolder & docName & ".csv"
tell application "Numbers" to export document 1 to file CSVExportFileName as CSV
end tell
set oldText to quoted form of (read file CSVExportFileName)
set posixPath to quoted form of (POSIX path of CSVExportFileName)
do shell script "echo " & oldText & " | sed -E 's/\\(?([0-9]{3})\\)?([0-9]{3})?([0-9]{4})/(\\1) \\2-\\3/g' " & posixPath
set newText to result
write_to_file(newText, CSVExportFileName, false)
tell application "Numbers"
close document 1 saving no
set newDoc to open file CSVExportFileName
-- export newDoc to file numbersFileName as Numbers 09
end tell
on write_to_file(|data|, target, append)
try
set open_target_file to open for access file target with write permission
if append is false then set eof of open_target_file to 0
write |data| to open_target_file starting at eof
close access open_target_file
return true
on error
try
close access file target
end try
return false
end try
end write_to_file
NOTE: the sed command will search & replace every sequentional 10 digits to phone number format
@KniazidisR beat me to it, but still…
A brute force approach:
sed command that changes the numbersNot elegant but probably fast(er then AppleScript)
Well there’s a fill down effect by dragging the initial cell down, and I presume there’s a corresponding menu command, but I doubt applescript has access to it.
Please forgive my ignorance but how exactly do I run this within an executable script for the column G on a numbers spreadsheet, and what/how do I need to install from GitHub and where does it get saved?
Seems like the GUI scripting method is the most elegant. Do you have any experience with this to show me how it’s done? Because I assume once learned, then nearly everything in the format panel is open to me for scripting.
Is the idea behind GUI scripting that you have to identify specific objects for applescript to basically click, so it’s like a really dumb version of a macro program, or is it something else entirely and not very easy to learn how to do?
Exactly. And not robust.
But if all I’m looking to do is select a predefined custom number format from the menu, or click the checkbox next to “Alternating Row Colors”, is there a simpler way to do that? I’ve been reading a lot about Keyboard Maestro and am wondering if that can interface with my existing script to accomplish this?
Oh man, imagine how long that would take! What about the idea suggested below of using a formula and filling down, is that doable?