Numbers AppleScript: paste clipboard as rows below

Actually, by far the most "portable" is to just glance at the Dock and make sure Numbers 2 is not running. That keeps the code simple and clean. My script posted above, which addresses the OP's question by taking advantage of the native Numbers ability to parse tabbed data pasted in from the clipboard, simply does not need this extraneous stuff. With Numbers 3 it works really well whether Numbers 3 is running or isn't running. So I'm not sure how we all got off on this tangent.🙂

SG

(deleted)

I'll repost it later.

(...I figured out how to post tab character in sample TSV text in the source code. It cannot be posted directly...)

Hello

If you want to implement unattended background process, here's an AppleScript script you may explore. I think the code is fairly self-explanatory with embedded comments. You may specify the POSIX path of the Numbers file in |FILE|, sheet index or name in |SHEET| and table index or name in |TABLE| properties in script.

When it is run, it will open the specified file if not yet open, populate the table, save and close the document if it is opened by the script. (If the file is already open when it is run, script will not close it. Also if script opens the file, it will minimize the window before processing the file.)

--APPLESCRIPT on run set tsv to "value_11" & tab & "value_12" & tab & "value_13" & linefeed & ¬ "value_21" & tab & "value_22" & tab & "value_23" & linefeed & ¬ "value_31" & tab & "value_32" & tab & "value_33" & tab & "value_34" & linefeed _main(tsv) end run on _main(argv) (* string argv : TSV text *) script o property |FILE| : (path to home folder)'s POSIX path & "Desktop/a.numbers" property |SHEET| : 1 -- index or name property |TABLE| : 1 -- index or name property aa : _text2array(argv, tab, linefeed) -- 2d-array from TSV text set |FILE| to (|FILE| as POSIX file as alias)'s POSIX path -- normalise the path (optional) tell application "Numbers" tell (document 1 whose path = |FILE|) set _was_open to exists if not _was_open then -- open the file if not yet open open |FILE| as POSIX file repeat until exists tell current application to delay 0.5 end repeat end if set doc to it end tell if not _was_open then tell (window 1 where its document = doc) set miniaturized to true -- minimise the window (optional) end tell end if tell doc tell sheet |SHEET|'s table |TABLE| set jx to count columns repeat with i from 1 to count my aa set a to my aa's item i if (count a) = 0 then -- ignore empty record else add row below row -1 -- add new row at end tell row -1 repeat with j from 1 to count a if j > jx then add column after cell -1 -- add column if necessary set jx to jx + 1 end if set cell j's value to a's item j end repeat end tell end if end repeat end tell if modified then save -- save changes if not _was_open then close -- close the file if not originally open end tell end tell end script tell o to run end _main on _text2array(t, fs, rs) (* string t : source text string fs : column separator (field separator) string rs : row separator (record separator) return list : 2d-array represented by t *) script o property pp : _split(t, rs) property qq : {} repeat with p in my pp set end of my qq to _split(p's contents, fs) end repeat return my qq's contents end script tell o to run end _text2array on _split(t, d) (* string t : source string string or list d : separator(s) return list : t splitted by d *) local astid0, tt try set {astid0, AppleScript's text item delimiters} to {AppleScript's text item delimiters, {} & d} set tt to t's text items set AppleScript's text item delimiters to astid0 on error errs number errn set AppleScript's text item delimiters to astid0 error errs number errn end try return tt end _split --END OF APPLESCRIPT

If you want to use it in Automator's Run AppleScript action, you need to change the run handler to the following:

on run {input, parameters} _main(input's item 1) end run

and receive tsv text from previous action.

---

And now that you mentioned perl, here's perl script to invoke the applescript via osascript(1).

#!/usr/bin/perl -w use strict; my $tsv = <<"TSV"; value_11\tvalue_12\tvalue_13 value_21\tvalue_22\tvalue_23 value_31\tvalue_32\tvalue_33\tvalue_34 TSV system('/usr/bin/osascript', '-e', &applescript(), $tsv) == 0 or die "applescript failed: $?"; sub applescript() { return <<'APPLESCRIPT' on run argv _main(argv's item 1) end run on _main(argv) (* string argv : TSV text *) script o property |FILE| : (path to home folder)'s POSIX path & "Desktop/a.numbers" property |SHEET| : 1 -- index or name property |TABLE| : 1 -- index or name property aa : _text2array(argv, tab, linefeed) -- 2d-array from TSV text set |FILE| to (|FILE| as POSIX file as alias)'s POSIX path -- normalise the path (optional) tell application "Numbers" tell (document 1 whose path = |FILE|) set _was_open to exists if not _was_open then -- open the file if not yet open open |FILE| as POSIX file repeat until exists tell current application to delay 0.5 end repeat end if set doc to it end tell if not _was_open then tell (window 1 where its document = doc) set miniaturized to true -- minimise the window (optional) end tell end if tell doc tell sheet |SHEET|'s table |TABLE| set jx to count columns repeat with i from 1 to count my aa set a to my aa's item i if (count a) = 0 then -- ignore empty record else add row below row -1 -- add new row at end tell row -1 repeat with j from 1 to count a if j > jx then add column after cell -1 -- add column if necessary set jx to jx + 1 end if set cell j's value to a's item j end repeat end tell end if end repeat end tell if modified then save -- save changes if not _was_open then close -- close the file if not originally open end tell end tell end script tell o to run end _main on _text2array(t, fs, rs) (* string t : source text string fs : column separator (field separator) string rs : row separator (record separator) return list : 2d-array represented by t *) script o property pp : _split(t, rs) property qq : {} repeat with p in my pp set end of my qq to _split(p's contents, fs) end repeat return my qq's contents end script tell o to run end _text2array on _split(t, d) (* string t : source string string or list d : separator(s) return list : t splitted by d *) local astid0, tt try set {astid0, AppleScript's text item delimiters} to {AppleScript's text item delimiters, {} & d} set tt to t's text items set AppleScript's text item delimiters to astid0 on error errs number errn set AppleScript's text item delimiters to astid0 error errs number errn end try return tt end _split APPLESCRIPT }

Tested with Numbers v2.0.5 under OS X 10.6.8.

Good luck,

H

Mark Sealey wrote:

...In fact I do only have the latest Numbers.

My reason for wanting to do this (and I'm so grateful that you've all been able to suggest such a rush spectrum of options and variations) is nothing more - nor less - than to automate a routine task (adding first and last names to a webpage when they come in from an email (I've written the rest in Perl)) so that, once correct, I just run the script without having to think about it. And that 'thinking includes having to have apps and docs otherwise open.

Hi Mark,

As you probably saw my original script above doesn't care if Numbers and the doc are open or not open. However, when it's finished it always closes the doc. If you want it to leave the doc open if it was already open and frontmost, then you can do something like the script at the end of this post.

BTW, I can't get Hiroto's script to run on my machine (El Cap with Numbers 3) without throwing off an error:

So I can't test whether all that extra code brings sufficient advantage to accomplishing a routine task.

It sounds as if you only need to insert a row or two at a time. But if you are doing more than that then the "paste" approach to inserting a block of data in Numbers is many times faster setting values cell by cell, and (especially if you already have the data tab-delimited) requires much less coding to boot.

SG

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

property sht : "Sheet 1"

property tbl : "Table 1"

set doNotClose to false

if getPathFrontDoc() is doc then set doNotClose to true

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

if doNotClose then return

close front document

end tell

to getPathFrontDoc()

try

tell application "System Events" to tell process "Numbers" to return ¬

text 8 thru -1 of (window 1's attribute "AXDocument"'s value as text)

on error

return "Nothing open" -- if no windows open

end try

end getPathFrontDoc

Hello

Here's revised script which introduced launch command to launch Numbers in background without opening template chooser or untitled document when not already running and also simplifed logic to open file. Additionally it replaced the statement:

document 1 whose path = |FILE|

with:

document 1 where its path = |FILE|

in order to avoid the reported error -1728 (errAENoSuchObject, errOSACantAccess) under some recent OS(es) based upon my inference that the error is casued by terminolgy conflict between class name and property name.

Incidentally I have noticed the previous perl script cannot prevent the template chooser or untitled document from opening even when the launch command is introduced and it is because the behaviour of osascript(1) differs depending upon whether the script is to be compiled and executed from applescript source text or just to be executed from compiled script. When osascript executes pre-compiled script, template chooser can be suppressed (at least with Numbers v2 under OS X 10.6.8).

Thus perl script is revised as follows, provided that the applescript is pre-compiled and saved as ~/Desktop/a.scpt.

#!/usr/bin/perl -w use strict; my $tsv = <<"TSV"; value_11\tvalue_12\tvalue_13 value_21\tvalue_22\tvalue_23 value_31\tvalue_32\tvalue_33\tvalue_34 TSV my $scpt = "$ENV{HOME}/Desktop/a.scpt"; system('/usr/bin/osascript', $scpt, $tsv) == 0 or die "applescript failed: $?";

And the revised applescript to be called from the perl script above is as follows.

--APPLESCRIPT on run argv _main(argv's item 1) end run on _main(argv) (* string argv : TSV text *) script o property |FILE| : (path to home folder)'s POSIX path & "Desktop/a.numbers" property |SHEET| : 1 -- index or name property |TABLE| : 1 -- index or name property aa : _text2array(argv, tab, linefeed) -- 2d-array from TSV text set |FILE| to (|FILE| as POSIX file as alias)'s POSIX path -- normalise the path (optional) tell application "Numbers" launch -- silently tell (document 1 where its path = |FILE|) set _was_open to exists if not _was_open then -- open the file if not yet open set doc to open |FILE| as POSIX file else set doc to it end if end tell if not _was_open then tell (window 1 where its document = doc) set miniaturized to true -- minimise the window (optional) end tell end if tell doc tell sheet |SHEET|'s table |TABLE| set jx to count columns repeat with i from 1 to count my aa set a to my aa's item i if (count a) = 0 then -- ignore empty record else add row below row -1 -- add new row at end tell row -1 repeat with j from 1 to count a if j > jx then add column after cell -1 -- add column if necessary set jx to jx + 1 end if set cell j's value to a's item j end repeat end tell end if end repeat end tell if modified then save -- save changes if not _was_open then close -- close the file if not originally open end tell end tell end script tell o to run end _main on _text2array(t, fs, rs) (* string t : source text string fs : column separator (field separator) string rs : row separator (record separator) return list : 2d-array represented by t *) script o property pp : _split(t, rs) property qq : {} repeat with p in my pp set end of my qq to _split(p's contents, fs) end repeat return my qq's contents end script tell o to run end _text2array on _split(t, d) (* string t : source string string or list d : separator(s) return list : t splitted by d *) local astid0, tt try set {astid0, AppleScript's text item delimiters} to {AppleScript's text item delimiters, {} & d} set tt to t's text items set AppleScript's text item delimiters to astid0 on error errs number errn set AppleScript's text item delimiters to astid0 error errs number errn end try return tt end _split --END OF APPLESCRIPT

Regards,

H

PS. In case you're lost in trees, I'd like to clarify that the whole point of my script is to be run in background without destroying general clipboard's contents.

H

You can do something like this:

set theDoc to POSIX path of (path to documents folder as string) & "names.numbers"

tell application "Numbers"

opentheDoc

-- etc.

end tell

SG

Hi Mark,

Did you try what I actually posted?

set theDoc to POSIX path of (path to documents folder as string) & "names.numbers"

tell application "Numbers"

opentheDoc

-- etc.

end tell

That should do the same thing as what you posted above:

open "/Users/msealey/documents/experiment/names.numbers"

SG