Appreciate your input tremendously. After testing this again my spreadsheet the issue is not the 123.456.7890 number format at all because when I isolate all the numbers from the column it works as expected. The problem is the text, and a simply test will show it to you. Please run the script on a column of two cells with text that contains more than one word, e.g. A1- Text Message, A2 = Data Message and then you’ll see what I’m experiencing on the full column. But what made this harder to understand is that once the column contains both types, the numbers and the multi-word text, then ALL the entries are formatted badly, not just the text.
OK, here’s the issue. the 123.456.7890 format in the second column gets processed perfectly by your script when there is not text in other cells (or more specifically, text with a space between words). Once there are those types of cells in the column, the whole thing gets formatted badly.
As a test, run your script on a column of cells with two words in them and you’ll see exactly what I mean.
UPDATE: That’s not exactly right either. I am uploading a very simple test spreadsheet, if you could kindly run the script on each of the columns and let me know if you can makes heads or tails of what’s going on!
Test Sheet.zip (265.8 KB)
You’ll see the first Column A of only numbers works fine, but the second two columns of only text and mixed text and numbers do not.
I’d been under the impression up till now that each column contained its own type of data and that column “B” only contained US-style telephone numbers.
If a cell has a space in it, the text is going to be split into separate cells when pasted back, in the same way the reformatted phone numbers were before the space in them was replaced with a non-break space. An extra line needs to be inserted into peavine’s script, just before the clipboard’s set, to replace every space in the entire text with a non-break space:
set theString to (theString's stringByReplacingOccurrencesOfString:space withString:(character id 160) options:1024 range:{0, theString's |length|()})
But I don’t know why normal spaces are confusing Numbers here.
1 Like
Just a wild guess: Numbers is tearing pasted data as it is a text file that is told to read. Therefore, it tries to find a „separator“ like comma, semicolon or tab. In their absence, it considers a space to be the separator. Adding a comma to the end of the first line fixes this behavior. I think.
Hi @chrillek.
Yes. I think you’re right. I’ve just been testing with my multi-column script here and it works perfectly if I change the non-break spaces back to ordinary spaces because it uses tabs as the column separators.
One more thing: Appending a comma to the first line before setting the clipboard values seems to fix all the issues. And it’s bloody simple to fix this first line after pasting…
So, here’s my final take on this problem in JavaScript. It works just fine with the sample CSV posted by @pavilion here Format Telephone Number in Numbers - #91 by pavilion
The only grievance I have are the completely useless calls to display(). That is, they’d be useless if Apple’s stuff were working sanely.
/* 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 C
const app = Application("Numbers");
const table = app.documents[0].sheets[0].tables[0]; //in 1st doc: 1st table's 1st sheet
const testRE = /^[0-9.]{10,12}$/; //rows of 10 to 12 characters only containing digits and dots
const dateRE = /^(\d{3})\.?(\d{3})\.?(\d{4})$/; //phone numbers with and without dots
const replaceString = "($1) $2-$3"; // correctly formatted phone numbers
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();
/* Set 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];
})
})()
I’m sure some kind soul can recreate this in AppleScript if they need it.
@pavilion. Thanks for the test spreadsheet, which allowed me to reproduce the issue.
I tested Nigel’s suggestion, which seemed to fix the issue with my script. Also, I found a line of my script that could cause a problem (not related to the current issue), and I have fixed that also. So, my suggestion for testing is:
use framework "Foundation"
use scripting additions
-- formatColumn("A")
-- delay 5 -- test lower values
-- formatColumn("B")
-- delay 5 -- test different values
formatColumn("C")
on formatColumn(theColumn)
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set format of column theColumn to text
set theValues to value of every cell in column theColumn
set selection range to range (theColumn & "1:" & theColumn & "1")
end tell
set theArray to current application's NSArray's arrayWithArray:theValues
set theString to theArray's componentsJoinedByString:linefeed
set thePattern to "(?m)^(\\d{3})\\.?(\\d{3})\\.?(\\d{4})$"
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:("($1)" & character id 160 & "$2-$3") options:1024 range:{0, theString's |length|()})
set thePattern to "(?m)^<null>$"
set theString to (theString's stringByReplacingOccurrencesOfString:thePattern withString:"" options:1024 range:{0, theString's |length|()})
set theString to (theString's stringByReplacingOccurrencesOfString:space withString:(character id 160) options:1024 range:{0, theString's |length|()})
set the clipboard to (theString as text)
delay 0.2 -- test different values
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 formatColumn
Works like an absolute charm! So appreciative for all your help (as well as @chrillek) and hope others in need of the same solution will find this super useful.
p.s. I assume there’s nothing left to tweak in the script to make it any faster?
Sounds right, and was something I also saw earlier that when you add a comma after the first cell, it formats more reliably. Thanks for the great suggestion that got this working right!
Would doing this in @peavine’s script make it run any measurably faster, do you think? Can we try that out and see?
Why would adding more code make it run faster? And why is speed so important? Did you test the script I posted for speed? I have hunch that it might be faster than the AS stuff, but I’m not sure.
My sense from your comment was that adding the comma at the start and then removing it later would make the script more efficient and thus run faster, which is just something I assume is always better than slower, so I thought it might make sense to compare how the two versions run side by side.
@pavilion. I don’t know of any way to make the code itself faster. If you are formatting multiple columns at once, you could try reducing or eliminating the 5-second delays. And, if the columns are adjacent, you could rewrite the script to format adjacent columns in one pass of the handler, although that might be more work than it’s worth.
BTW, I ran a timing test and it takes my script 350 milliseconds to format column C of your test spreadsheet. Of this amount, only 15 milliseconds is spent formatting data, and the rest is attributable to Numbers doing its work. Unless you take an entirely different approach, I don’t know of a way to make Numbers faster.
I thought that I’d explained it:
Obviously not. To spell it out: If one does not append a comma to the first row, the rows are broken into separate columns at the spaces. Thus, (123) 456-7890 becomes -123 in the first column and 456-7890 in the 2nd one.
Appending the comma to the first row prevents this (apparently Numbers then “thinks” that the comma is a separator). However, this comma is literally appended to the first row’s content when the clipboard is pasted. That’s not nice. So, my code removes this comma from the first row after pasting.
That certainly does not speed up the whole thing. But frankly, I do not really care about speed. “Correct is better than fast”, or “First make it run correctly, then optimize if necessary”. If the script runs in less than 2 seconds (as you wrote), you can run it about 20 times per minute, or 9600 times in an 8-hour day. We’re discussing the topic now for the fourth day, and in that time the script could have run about 38,400 times (assuming a working day of eight hours). Is that “fast enough”? Are there even 38,400 files to run it on, with 6000 rows each (amounting to about 230 million phone numbers, if there’s only one column with those per file)?
And did you try my script, timing it?
I’m trying to figure out how to do that since it’s not in Applescript that I can tell 
Copy/paste into Script editor, set the language selector in the upper right corner to “JavaScript” and run. But don’t bother, the AS version is about one order of magnitude faster. I’ll work on my stuff.
I was not aware you could do that within the scripter. Thanks so much for you help!
Just a short note: Clicking on a menu item only works in the locale you are using. Bluntly: A German “Numbers” doesn’t have an “Edit” menu. But the keyboard shortcuts are always the same, so that’s a safer bet to paste the clipboard.
Absolutely makes sense.