Unless… we use this method for this and also the search and replace we worked on, so that all the heavy lifting was done on the text file at one time, so I would then be saving the time of running the search replace within the original file. Could you incorporate that code here and I can see how long all of that would take?
If I wasn’t clear, what I meant was, use the shell command to also perform the search and replace instead of doing that inside applescript. So this would be at the top of the script, and I assume would leave me with a new spreadsheet that I would then script the remainder of the easy formatting, like column widths and such, so can you just put in one line setting the column width of A to 100 so I know where to insert the remaining formatting commands? And the script should specify it’s being done in all open spreadsheets.
WOW!! i just ran the script and it made those changes and opened the new file in less than 2 SECONDS, so this is a game changer. The only hitch is that i also have other columns with 10 digit account numbers that are also getting formatted, so how can we limit the changes to a particular column, perhaps by counting the delimeters or something?
And then, how can we do the same thing for the search and replace so that this is done on the text file as well?
Also it loses a lot of the existing formatting, obviously, since it’s coming from text. What if we leave the original file open, and do the data transfer by copy and paste so that everything just gets pasted (without format) back into the original spreadsheet. Alternatively, and this would solve the other issue above, just perform this only on the needed columns, so that only the identified column with the phone numbers, and those columns with search/replace issues gets processed in text and then pasted back to their original cells?
At least Excel 2016 for Mac supports VBA. Can’t you use that?
Disregard, I was temporarily out of my mind.
Kind of stuck with Numbers presently, but the script above that exports the cells, makes the changes, and brings them back is surprisingly robust, just need some adjustments to it to make it work perfectly.
Numbers doesn’t have a paste special like Excel, but paste and match style does the trick. So the best solution would be to export either all the cells or a limited number of columns, perform the text changes, and then rather than opening a new file, copy/paste and match style back into the original. If you cannot parse out separate columns (or if that requires multiple repeats of the same code) then the only remaining hurdle is how to prevent the formatting from also changing other 10-digit numbers.
EDIT Ughh… this won’t work either because Numbers chokes with all that data going into and out of the clipboard, so that’s a workable solution. The best thing, and super fast, is what you’ve already done, and then I just have to reformat the sheet, which is not the end of the world, but am still stuck with unintended formatting on the other columns.
Here is a demonstration movie.
step 1 Download Xcode project from github.
step 2 Build the project as you like
step 3 Install the built framework binary to your ~/Library/Frameworks.
step 4 Run above script
it actually works, thanks, but it’s super slow since it’s going down cell by cell.
1 Like
This is what @chrillek (post #14) suggests too. He only talks about Excel, but the same is possible with Numbers.
I think, this should give the most speed efficient script, which will contain only 1 GUI scripting command to paste the data processed by the SED command back into the column (suggested by moderator @Nigel Garvey in your other thread).
– 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
I’ll try to write such a script.
Thanks so much! Can you incorporate the search/replace as well so that it’s both done at the same time, or at least we run each column separately so we’re only pasting what we need? This avoids processing the SED command on unwanted columns of other 10 digit numbers and also doesn’t slow down the app into pasting the entire sheet.
The following is the only way I know to avoid the repeat loop. This script has three issues that need to be fixed. A non-breaking space needs to be inserted after the closing parentheses in the telephone number. The cursor has to be in cell A1 before the script is run. An empty cell will show missing value. All of these items are easily fixed, and, just for the sake of completeness, I’ll work on that later.
tell application "Numbers"
set theCells to (every cell in column 1 of table 1 of sheet 1 of document 1)
set theValues to value of (every cell in column 1 of table 1 of sheet 1 of document 1)
getFormattedValues(theValues) of me
end tell
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
on getFormattedValues(theValues)
set newValues to {}
repeat with aValue in theValues
set aValue to contents of aValue
if aValue ≠ missing value and (count aValue) = 10 then set aValue to "(" & text 1 thru 3 of aValue & ")" & text 4 thru 6 of aValue & "-" & text 7 thru 10 of aValue
set end of newValues to aValue
end repeat
set text item delimiters to {linefeed}
set the clipboard to (newValues as text)
set text item delimiters to {""}
end getFormattedValues
As you’re writing it, here are the two snippets of code I’m using that wrap the sample formatting line (I will add all the other formatting lines below it later), the first works on all open Numbers files, and the second processes all files in a folder, one at time. They also both save the changed Numbers file and export an Excel copy to the same folder.
So if you could incorporate your script into these, above the point where the formatting begins, that would be awesome.
Even better, if we could have only one script that essentially branches the code at the start to determine whether I want to operate on open files or closed folder, that would keep me from having to maintain two separate scripts. Perhaps it’s possible to throw up a dialog asking, “Run script on open files or folder?” with two buttons (Files, Folder), and then after clicking, it does whatever was chosen.
RUNS ON OPEN FILES
tell application "Numbers"
repeat with myDoc from 1 to count of documents
tell table 1 of sheet 1 of document myDoc
set width of column "A" to 100
end tell
set docName to the name of document myDoc
set docPath to file of document myDoc as text
tell application "Finder" to set docDir to container of alias docPath as text
set newExportFileName to docName & ".xlsx"
set the targetFileHFSPath to docDir & newExportFileName
export document myDoc to file targetFileHFSPath as Microsoft Excel with exclude summary worksheet
save document myDoc without asking
end repeat
close documents
end tell
RUNS ON FOLDER
set sDir to choose folder
tell application "Finder" to set sList to (files of sDir whose kind is "Numbers Spreadsheet") as alias list
tell application "Numbers"
repeat with myDoc in sList
open myDoc
tell table 1 of sheet 1 of document myDoc
set width of column "A" to 100
end tell
set docName to name of document 1
set newExportFileName to docName & ".xlsx"
set the targetFileHFSPath to (sDir as text) & newExportFileName
export front document to file targetFileHFSPath as Microsoft Excel
close front document with saving
end repeat
end tell
Not so sure anymore. The “Excel” thingy was just a figment. The main problem, as I see it, is the last part: Set the column values again. And that’s also what is slow, it seems. But it could, probably, be done with GUI scripting, as you said.
Here’s an experimental JavaScript script:
(() => {
const app = Application("Numbers");
const ca = Application.currentApplication();
ca.includeStandardAdditions = true;
// Get all values from the 1st column of the 2nd sheet in the first document's 1st table
const table = app.documents[0].sheets[1].tables[0]
const vals = table.columns[0].cells.value();
const newVals = vals.map(v => {
if (!v || ! /^\d{10}/.test(v)) return v; // ignore anything not a phone no */
return (v+"").replace(/(\d{3})(\d{3})(\d{4})/,"($1) $2-$3"); /* convert to US format */
});
ca.setTheClipboardTo(newVals.join('\n')); // write converted values as newline separated strings to clipboard
app.activate();
// Set the selection so that cmd+v copies into it
const rangeVal = "B1:B"+ `${vals.length }`;
table.selectionRange = table.ranges[rangeVal];
const se = Application('System Events');
se.keystroke('v',{using: ["shift down", "option down", "command down"]});
})()
It kind of works. That is, reading everything from the first column of the first table’s first sheet in the first document (what a drag), converting all values consisting of 10 digits to US phone format (and that’s what we’re talking about, right?) and sending those to the clipboard as a string containing each phone number on its own line – all honky dory. But then, pasting the clipboard into the table, converts this
(123) 456-7890
to
-123
Numbers seems to see the space in the phone number as a separator during paste. So, it interprets the input as two columns, and (123) is seen as a negative number (why, oh why, do these programs always try to be “clever”? And how can I tell them to do what I want?)
Now the question is: How could one make Numbers accept the clipboard lines as entries for one column only? I tried adding quotes (single and double ones). That “worked”, but it gave me a quoted text… Escaping the opening parenthesis gave me a \(123) in the first column …
Someone probably knows how to fix this mess. Which it is, in my opinion. Or perhaps everything is simpler in AS. I wouldn’t know.
Oish… Apple makes things so complicated sometimes. Can we try the GUI scripting idea to select the custom format from the menu? Of course, even that Numbers is far behind Excel because it does not save custom formats globally, which means I need the same custom format for every file - the workaround appears to be that I can copy one cell from one custom format and then paste it into each spreadsheet to import the format - what a pain!
Just thinking, is the problem you’re having with pasting it back that Numbers thinks it’s a number and not text? If it’s just “text” then it cannot possibly have a reason to reject it as complete cell data, no?
Exists Number.app problem with a SPACE detecting (indicated by @chrillek). When I replace it with “_”, the paste action works as expected.
tell application "Numbers" to tell table 1 of sheet 1 of document 1
set format of column "B" to text
set rowCount to row count
set selection range to range ("B1:B" & rowCount)
set oldValues to value of cells of range ("B1:B" & rowCount)
end tell
set ATID to AppleScript's text item delimiters
set AppleScript's text item delimiters to return
set oldText to quoted form of (oldValues as text)
set newText to do shell script "echo " & oldText & " | sed -E 's/\\(?([0-9]{3})\\)?([0-9]{3})?([0-9]{4})/(\\1)_\\2-\\3/g' "
set AppleScript's text item delimiters to ATID
set the clipboard to newText
delay 1
tell application "System Events"
set frontmost of process "Numbers" to true
keystroke "v" using {option down, shift down, command down}
end tell
Let me take it for a spin!
error “System Events got an error: Script Editor is not allowed to send keystrokes.” number 1002
Okay fixed… testing now.