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)?
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.
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.
@chrillek. There’s actually two ways to look at this. I have modified the keyboard shortcuts for many apps and for me using menu items is a more reliable alternative. With my script, a German-speaking user would have to change Edit and Paste to the corresponding German words, but this doesn’t strike me as much of a burden. Regardless, this seems a matter of personal preference with no single correct solution.
Well, given that the keyboard shortcut works in all locales without modification … And people in another locale will have to figure out what “Edit” and “Paste” mean in theirs. Which is not too complicated, but it quickly gets ugly with less common commands. Not everyone here is working in an English environment, I guess.
Apart from that: I modified my JavaScript variant to work more like your code (i.e. run a single replace on the whole column values as a single string). Still not fast: Needs about 7 seconds for 6000 non-empty entries in a column, whereas your script runs in less than 2 seconds. All that on an 4-year-old MacbookPro with Intel-CPU under Ventura. Interesting.
I think we can shave more time off (especially in my case where the sheet has many thousands of rows) by using the other “fix” method of simply adding a single comma after the first data cell and then deleting it after pasting, rather than adding a special ASCII character to every cell in the entire column.
If you can make that change to the script, I will report back on the execution times of both with my very large sheet. I’m very interested to see what difference it will make!
p.s. how are you timing your scripts? is this something the Apple scripter can do, or are you using some add-in?
My modified script that incorporates chrillek’s suggestion is included below. Most forum members use the free Script Geek app to time scripts. It can be found here.
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
if item 1 of theValues is missing value then set item 1 of theValues to space
set item 1 of theValues to item 1 of theValues & ","
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) $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 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
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set firstValue to value of cell (theColumn & "1")
set value of cell (theColumn & "1") to text 1 thru -2 of firstValue
end tell
end formatColumn
Running into this issue after testing, please see attached sheet, which is how the column actually looks with several blank cells above the data. Presumably you need to to specify that the remove comma operation occur only on the same cell you placed it in previously.
it’s interesting that in both versions you need to actually insert an “” in every blank cell to avoid the “null” reference in the sheet itself after pasting it back in. I assume there’s no workaround to avoid doing that.
Also, I downloaded the Script Debugger as you suggested to time the execution, but it immediately threw up an error on the “set the clipboard” line : ““set the clipboard to” is ambiguous because it is imported from scripting additions and scripting additions.” but it runs in Apple’s scripter with no problems so I assume it’s just a best practices kind of warning. How do I configure SD to just run the script without being so particular, so that I can time it without stopping for these checks?
@pavilion. The suggested app for running timing tests is Script Geek, not Script Debugger.
I ran a timing tests with Script Geek with a spreadsheet that contained 6144 rows, and the script that used a comma to prevent cell splitting took 1.227 second and the script that used an unbreaking space to prevent cell splitting took 1.064 second.
I’ve modified my script to address the issue you note. It works by inserting a space in cell 1. I tested this on the Test Sheet spreadsheet in your post, and everything worked fine.
The operation that replaces null with “” is necessary.
Thanks for the correction on the app. So I just ran both scripts and like you, the one with the comma took slightly longer, but in my case with also around the same 6,000 rows, it still took 7.049 seconds, nowhere close to your 1.064 sec. Curious what data you have in your sample (numbers only or also with text) because this test was only on the one column with mixed text and numbers, and why it would make such a big difference, though as I may have mentioned, I am getting an “updating spreadsheet” notice from Numbers that indicates something of a slowdown.
Running either script on only numbers (6,000 rows) takes about the same time, so there’s no difference for me in execution time as relates to the data. So to perform both columns, it takes roughly 15 seconds. How are you getting this done in less than 2?!?
I am on a 2017 iMac with 32GB of memory and I reproduced your exact data in a new sheet 6,000 rows and my best time is 5.2 seconds, so clearly you’ve got an awesome machine that makes me jealous
Bottom line is the first version of the script with the “” is the best, and your help has been greatly appreciated!
On this script from @Nigel_Garvey that replaces text in-script and then pastes it back to the original columns, the process time on my machine from start to finish (including the paste time) is around 1.6 seconds. Try the attached 6,000 rows on your machine and you’ll be even faster.
What I don’t understand is why so much of the execution time of your script on my machine is bogged down by the paste process, when the same thing (although with slightly less cell data in the first column) takes many seconds longer, to the point where Numbers throws up an updating spreadsheet notice and grays out the sheet.
In other words, I can understand that the actual text processing times for both scripts are different because they are doing different things. But once the text gets back onto the clipboard, I would expect the pasting of 6,000 cells into one or two columns would take roughly the same time to complete. And yet, this script’s pasting is around 5x faster on even my slower machine, though with twice the memory of yours.
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()
There are two differences between my script and Nigel’s as regards the paste portion of our scripts:
My script uses the paste command, and Nigel’s script uses the paste and match style command.
My script pastes to a column, and Nigels script pastes to a selection.
Another difference is that my script incorporated a 200 millisecond dealy, and Nigel’s script did not.
None of the above would account for the timing differences you are seeing. I’ve modified my script to work as Nigel’s script does with respect to the above items, and I ran timing tests on both scripts. They both took about 400 milliseconds.
I do not know why my script takes 7 seconds to run on your computer.
use framework "Foundation"
use scripting additions
formatColumn("A")
on formatColumn(theColumn)
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set selection range to range (theColumn & "1:" & theColumn & (row count))
set theValues to value of cells of selection range
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) $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)
tell application "System Events" to tell process "Numbers"
set frontmost to true
click menu item "Paste and Match Style" of menu "Edit" of menu bar 1
end tell
end formatColumn
