Numbers AppleScript: paste clipboard as rows below

Something like this might work:

--Assumes clipboard contains tab delimited data and Numbers document is open

set theText to (the clipboard) as text

set newRows to (count the paragraphs of theText)

set oldDelimiters to AppleScript'stext item delimiters

set AppleScript'stext item delimiters to tab

repeat with j from 1 to newRows

set theRecord to text items of theText'sparagraphj

tell application "Numbers"

--set theTable to a reference to the table to which you want to add information

set theTable to table 1 of sheet 1 of document 1

tell theTable

add row below last row

repeat with i from 1 to column count

set value of cell i of last row to item i of theRecord

end repeat

end tell

end tell

end repeat

set AppleScript'stext item delimiters to oldDelimiters

I don't think anything other than Numbers can write to a Numbers document, and it has to open the document to do that.

AppleScript can check whether Numbers is running, note that state, launch it if it isn't running, check whether the document is open, open it or bring it to the front if necessary, add the rows you want, save the document and then return the document and application to the state in which it found them, without additional user input. If you know that the application and document will always be closed when the script runs it could be a lot simpler.

If you are doing this unattended, I would wonder if it is necessary to involve the clipboard in the process, since that's basically part of the user interface.

Mark Sealey wrote:

Is it possible to have something like this without Numbers actually open, please: I want to put it into Automator and have the whole thing run unattended?

Hi Mark,

Yes, it is quite easy to have a script have Numbers open a document, insert tabbed values from the clipboard, and close the document. Just insert your document's path and name, the target sheet name, and the target table name in the property statements in the script below. (The script won't complain if the document happens to be open already when you run it.)

If you are uncertain of the whole path of your document you can locate it in Finder and drag it onto an open window in Terminal, then insert that result into the script.

This is one of those rare situations where so-called "gui scripting" is noticably more efficient than having a script assign values to cells one-by-one, especially if you have quite a few new rows to insert.

This script mimics the manually pasting of tabbed clipboard contents into a Numbers cell. You'll notice when you click a Numbers cell once and command-v to paste values the table expands automatically as much as needed. It does its thing much faster than a script that inserts values cell by cell.

As written the script assumes you do not have any blank rows at the bottom of the table. If you typically do have blank rows a small change in the script can handle that.

If you've specially formatted the table and want to preserve the format you can use

tell application "System Events" to keystroke "v" using {option down, shift down, command down}

Substitute your user name for sg3, or get the whole path via the terminal as described above.

Tested in Script Editor and in a Run AppleScript action in Automator.

SG

property doc : "/Users/sg3/Desktop/Test.numbers"

property sht : "Sheet 1"

property tbl : "Table 1"

tell application "Numbers"

opendoc

tell front document to tell sheet sht to tell table tbl

add row below last row

set selection range to last row's first cell

end tell

activate

repeat until frontmost

end repeat

tell application "System Events" to keystroke "v" using command down

close front document

end tell

SG,

A couple points about your script.

If this is run unattended it would probably not be an issue, but GUI scripts tend to be more fragile because something like a badly timed mouse click can change the focus and break them. Efficiency is probably also unimportant if the script is run unattended.

Mark indicated that this is a Numbers 3.6.1 document. On a system like mine, where I use both Numbers 3.6.1 and Numbers 2.3, if I had only Numbers 2.3 running at the time this script was run, I think it would fail because it would try to open the document in the wrong application. This could be addressed with version checking code, and the script could also check whether Numbers 3.6.1 is running and preserve that state when it finishes.

If the user's preference is set to "Ask to keep changes when closing documents" it might be necessary to add "saving yes" to the close statement. The script could also check whether the document is already open and leave it open if that is the case.

Error checking to notify the user if the script fails might also be nice for an unattended process, but this is all getting beyond the "quite easy" realm, and may not be required by Mark.

Jeff

Hi Jeff,

Thanks for the thoughtful comments.

The script is tested in Numbers 3. Not sure if it works in Numbers 2. I should have mentioned that it's for Numbers 3 only. On my machine to avoid AppleScript confusion I long ago renamed Numbers 2 from "Numbers" to "Numbers09". Numbers 2 should not be open when running the script. Numbers 3 can be closed or open.

I tend to have less trouble when I pare down scripts to the bare basics, then add features only if needed. In this case, on my machine it's working fine as written so I didn't add "saving yes, etc."

I agree that GUI scripting is generally fragile and usually is not the best choice. But this is I think a notable exception: pasting tabbed values into Numbers from the clipboard is much faster and more efficient than setting values row by row, cell by cell. I use it quite a bit.

Like you I would usually go for pasting the clipboard contents manually into an open document exactly where I want the data, but I've found it really is "pretty easy" to have a script open a particular document and insert data into a specified location if that's what's called for by one's workflow.

SG

SG,

I agree about paring the script down to basics, which can make things much easier when you are writing the script for yourself, and you (theoretically) know exactly what you want and can control the environment where the script runs. I'm not so confidant that someone else using one of my scripts would make the same choices, though.

I have renamed Numbers 2 to Numbers09 on my machine, too. When I was playing with this script, I tested an example with either a 'tell application "Numbers"' or a 'tell process "Numbers"' when Numbers 2 ("Numbers09") was running, but Numbers 3 ("Numbers") was not and the compiler changed the name to "Numbers09". Since both applications have the same Bundle ID, the system has trouble distinguishing them. I was only able to address one version, if the other version was running, by using its path.

Jeff

Hi Jeff,

I think you are describing my experience too. When running a script intended for Numbers 3 I always make sure Numbers 2 is not open. Otherwise, AppleScript gets confused and I I've noticed it does the unexpected things as you describe, such as changing the name of the application to "Numbers09" (or whatever Numbers 2 has been renamed to).

I haven't run a script for Numbers 2 in a while but I think if it is the only version open then it will work ok with scripts with

tell application "Numbers09."

Anyway, both the scripts in this thread seem to work well with Numbers 3, but best make sure Numbers 2 is not open when running them.

SG

Not basic AS primers, but some useful resources are macosxautomation.com and macscripter.net.

And of course:

https://developer.apple.com/library/mac/documentation/AppleScript/Conceptual/App leScriptX/AppleScriptX.html

SG

Mark,

There is a link on this webpage to a set of four scripts, one of which, the "iWork to PDF.scpt", I use as a model when I want to do this sort of checking/preserving state. Changes to the operating system have made the method of saving to pdf obsolete, but the model for handling the applications and documents are still valid.

SG does make a good point about keeping things simple, though. If you expect that when the script you are writing will run, Numbers will not be running and the document will be closed, you can just tell Numbers to open the document and it will work, even if one or both of them are open. Unless you can imagine a situation where it would be necessary to keep the document open if it was open before the script started, you can just close it when the script is done. You may know that the script will run at a time when Numbers will not be doing anything that would require it to remain running; in that case you can just quit it at the end of your script. Or maybe you can just leave Numbers running; an inactive application doesn't use many resources.

Whether you actually have to do this or not, I think it is interesting to know that it can be done, and how to go about it.

A note in case you do use both Numbers 3 and Numbers '09: the Systems Preferences test won't distinguish between them because they both produce processes named "Numbers". However, all applications have a property "running" which is the value "true" if the application is running and "false" if it isn't. Therefore, if Numbers 3 is in the normal location, the expression:

running of application "Macintosh HD:Applications:Numbers.app"

will evaluate as "true" if Numbers 3 is running, but "false" if it isn't, even if Numbers '09 is running (and therefore has a process "Numbers" running).

The same method of addressing the application by its full path can be used in "tell" statement to unambiguously distinguish between the two applications. (At the expense of portability, since the path is liable to be machine-specific; for example, my hard drive is not named "Macintosh HD".

Jeff Shenk wrote:

The same method of addressing the application by its full path can be used in "tell" statement to unambiguously distinguish between the two applications. (At the expense of portability, since the path is liable to be machine-specific; for example, my hard drive is not named "Macintosh HD".

If both versions are frequently running on a particular machine it is possible to check which ones are running, no matter what the hard drive is named, via something like this:

To check if Numbers 3 is running...

running of application ((path to applications folder as string) & "Numbers.app")

To check if Numbers 2 is running...

running of application ((path to applications folder as string) & "Numbers '09:Numbers09.app") -- or Numbers.app if not renamed to Numbers09.app

And the version number can be retrieved by something like this:

version of application ((path to applications folder as string) & "Numbers.app")

--> "3.6.1"

version of application ((path to applications folder as string) & "Numbers '09:Numbers09.app")

--> "2.3"

However, I don't use any of this much. I usually find it easier to keep a script simple and just check manually that Numbers 2 is closed (which it usually is here).

SG

Hello

By the way, you can use POSIX path to address application, which should make the souce code more portable.

E.g.,

application "/System/Library/CoreServices/Finder.app" is running application "/Applications/iWork '09/Numbers.app" is running

Regards,

H