Copying String Data from Numbers to Pages

Scripting Forums AppleScript | Mac OS X
#1

I have a simple spreadsheet of textual information in a Numbers document. I want to copy the string data from individual cells so it can be pasted and formatted into a Pages document and saved for later editing.

Each row has 5 columns. Each cell has a piece of text I need to copy, paste over into a Pages document, and then repeat, until one row has been completed. Then the Pages document will need to save and close, and then open up a new blank document for the repeat.

On repeat, the applescript should step down to the next row and repeat for all 5 columns.

Right now, I’m having trouble even getting a script to properly select a single cell at a time. I’m using the following code, but it looks like it’s selecting the entire row.

tell application "Numbers"
	activate
	tell table 1 of sheet 1 of document "KK-6July-1"
		repeat with i from 2 to row count
			set selection range to row i in column 1

Can anyone point me in the proper direction to select a single cell at a time, copy the string data, and paste it into a Pages document? Thank you. :smiley:

#2

Here is a handler which I use very often to get parameters describing the selected area.


#=====
(*
set { leDocument, laFeuille, laTable,  numLigne1, numColonne1, numLigne2, numColonne2} to my get_SelParams()
tell application "Numbers" to tell document leDocument to tell sheet laFeuille to tell table laTable
*)
on get_SelParams()
	local le_document, la_Feuille, la_Table, num_Ligne1, num_Colonne1, num_Ligne2, num_Colonne2
	tell application "Numbers" to tell document 1
		set le_document to its name
		set la_Feuille to ""
		repeat with i from 1 to the count of sheets
			tell sheet i to set maybe to the count of (tables whose selection range is not missing value)
			if maybe is not 0 then
				set la_Feuille to name of sheet i
				exit repeat
			end if -- maybe is not 0
		end repeat
		if la_Feuille is "" then
			if my parleAnglais() then
				error "No sheet has a selected table embedding at least one selected cell !"
			else
				error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"
			end if
		end if
		tell sheet la_Feuille to tell (first table where selection range is not missing value)
			tell selection range
				set {haut_gauche, bas_droite} to {name of first cell, name of last cell}
			end tell
			set la_Table to its name
			tell cell haut_gauche to set {num_Ligne1, num_Colonne1} to {address of its row, address of its column}
			if haut_gauche is bas_droite then
				set {num_Ligne2, num_Colonne2} to {num_Ligne1, num_Colonne1}
			else
				tell cell bas_droite to set {num_Ligne2, num_Colonne2} to {address of its row, address of its column}
			end if
		end tell -- sheet.
		return {le_document, la_Feuille, la_Table, num_Ligne1, num_Colonne1, num_Ligne2, num_Colonne2}
	end tell -- Numbers
end get_SelParams

#=====

on parle_anglais()
	return (do shell script "defaults read 'Apple Global Domain' AppleLocale") does not start with "fr_"
end parle_anglais

#=====

on parleAnglais()
	local z
	try
		tell application "Numbers" to set z to localized string "Cancel"
	on error
		set z to "Cancel"
	end try
	return (z is not "Annuler")
end parleAnglais

#=====

You are free to choose the handler identifying the language because I know that some users dislike the use of “localized string” as a switch.

If the passed infos aren’t sufficient, ask for more.

KOENIG Yvan (VALLAURIS, France) dimanche 7 juillet 2013 09:54:01

#3

I went through your code and even tried to run it, but nothing happened and even though I know the AppleScript is all essentially the same, I’m having a tough time getting around the French involved. :confused: Sorry about that.

Maybe it would help to show what I’m trying to do visually. I have long lists of spreadsheet data that look like this snippet:
http://25.media.tumblr.com/3d95f29364be3b784e6472bbb60e024a/tumblr_mpkkjxU6JP1r69mhko1_1280.png

Row 1 is the header row. Beginning on row 2, is my first complete series of information. Each row represents an article that needs to be written. I want my script to:

  • Copy cell B2 and make it the document name in a new Pages document. Then it will copy that same string to very first line of the document and make it bold.
  • Skip two lines and then copy cell A2 in italics to the Pages document.
  • Skip two lines and then copy cell C2 in bold to the Pages document.
  • Skip two lines and then copy cell D2 in bold to the Pages document.
  • Skip two lines and then copy cell E2 in bold to the Pages document.
  • Add some other automatic text that it the same every time.
  • Save the Pages document in the proper location and close it.

I know how to use System Events to execute the copy and paste, and other automatic keystrokes, but I’m having trouble getting Numbers to select single cells so I can just get to step 1.

I feel like the best strategy might be executing a copy / paste for all the data, then moving over to Pages and spitting it out, one instruction at a time. Not sure, though.

#4

Right now the biggest issue I am having is setting text from my Numbers spreadsheet as strings to my various variables. It doesn’t seem like I’m doing it right, according to AppleScript.

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	--repeat with i from 2 to 26
	set title to (text of cell 2 of row 2)

It keeps saying “error “Numbers got an error: Can’t get every text of cell 2 of row 2 of table 1 of sheet 1 of document 1.” number -1728 from every text of cell 2 of row 2 of table 1 of sheet 1 of document 1”

Not sure how to tell it to parse out the text from a cell, in order to make that my string for my variable.

#5

The error message tell you exactly what you are doing wrongly.
As we may see in the dictionary, a cell as no “text” property.

cell‚n [inh. range > item] : A cell in a table
elements
contained by columns, ranges, rows.
properties
column (column, r/o) : The cell’s column.
name (text, r/o) : The cell’s coordinates (e.g., C3).
row (row, r/o) : The cell’s row.
value (any) : The cell’s value.

use :
set title to value of cell 2 of row 2

KOENIG Yvan (VALLAURIS, France) dimanche 7 juillet 2013 17:35:18

#6

Yes, of course. Apologies for not realizing that sooner.

This worked. And I can see that my AppleScript is correctly grabbing the information I need. Now I’m having trouble assigning that data into Pages as necessary.

With my variable title, I attempt this:

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	repeat with i from 2 to 2
		set title to value of cell 2 of row i as text
		set keyword to value of cell 1 of row i as text
		set H1 to value of cell 3 of row i as text
		set H2 to value of cell 4 of row i as text
		set H2 to value of cell 5 of row i as text
		
		
		tell application "Pages"
			make new document
			set document name to title
			
		end tell

And I receive this error: "error “Pages got an error: Can’t set document "Pages" to "FX Trading Rules For Survival".” number -10006 from document “Pages”

I tried setting it as a property also, and that just leaves the Pages document sitting as “untitled”.

#7

Be patient, I’m writing the needed script.

KOENIG Yvan (VALLAURIS, France) dimanche 7 juillet 2013 18:25:19

#8

Here it is.


on run
	tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	
		tell row 2
			set A2 to value of cell 1
			set C2 to value of cell 3
			set d2 to value of cell 4 # I apologizes, this son of a b_tch change D2 to d2
			set E2 to value of cell 5
		end tell # row
	end tell # Numbers
	
	set docName to "blablah" & (do shell script "date +_%F_%H%M%S.pages") # edit to fit your needs
	set targetFolder to (path to documents folder as text) # edit to fit your needs
	
	set myNewDoc to my makeNewIworkDoc("Pages", docName, targetFolder)
	set skip2lines to linefeed & linefeed & linefeed & linefeed
	tell application "Pages" to tell document docName
		set body text to A2 & skip2lines
		set italic of first paragraph to true
		set bold of last character to true
		set last character to C2 & skip2lines
		set bold of characters -3 thru -2 to false
		set last character to d2 & skip2lines
		set bold of characters -3 thru -2 to false
		set last character to E2 & skip2lines
		set bold of characters -3 thru -2 to false
		set bold of last character to false
	end tell # Pages
end run

#=====

on parleAnglais()
	local z
	try
		tell application "Numbers" to set z to localized string "Cancel"
	on error
		set z to "Cancel"
	end try
	return (z is not "Annuler")
end parleAnglais

#=====
(*
Creates a new iWork document from the Blank template,
saves it and returns its pathname.
example:
set myNewDoc to my makeNewIworkDoc(theApp, docName, folderPath)
 *)
on makeNewIworkDoc(the_App, doc_name, doc_folder)
	local maybe, nb_doc, path_to_the_App, path_to_the_doc
	if the_App is "Pages" then
		tell application "Pages"
			set nb_doc to count documents
			make new document with properties {template name:item 1 of templates}
		end tell
	else if the_App is "Numbers" then
		tell application "System Events" to set maybe to the_App is in title of every application process
		if not maybe then activate application the_App
		tell application "System Events"
			set path_to_the_App to get application file of application process the_App
		end tell
		
		tell application the_App
			set nb_doc to count documents
			open ((path_to_the_App as text) & "Contents:Resources:Templates:Blank.nmbtemplate:")
		end tell
	else
		if my parleAnglais(the_App) then
			error "The application "" & the_App & "" is not accepted !"
		else
			error "l'application « " & the_App & " » n'est pas gérée !"
		end if
	end if
	
	if doc_name does not end with "." & the_App then set doc_name to doc_name & "." & the_App
	if doc_folder ends with ":" then
		set path_to_the_doc to doc_folder & doc_name
	else
		set path_to_the_doc to doc_folder & ":" & doc_name
	end if
	tell application the_App
		repeat while (count documents) = nb_doc
			delay 0.1
		end repeat
		save document 1 in file path_to_the_doc
	end tell -- the_App
	return path_to_the_doc as alias
end makeNewIworkDoc

#=====

Of course, as I am lazy, I grabbed a handler from my library.

KOENIG Yvan (VALLAURIS, France) dimanche 7 juillet 2013 18:48:22

#9

Wow :smiley: Thank you. Great handler.

I am attempting to edit some of the necessary items and am running into a roadblock with the file path. I wish to save the documents to /kris/Desktop/Freelancing/eLance/Forex/July/First 25 but when I input that into the script, I get a “file could not be saved” error.

I see the default is saving them to my documents folder and that won’t suit this situation.

#10

You must pas a HFS+ pathname, not an Unix one !
set targetFolder to (path to desktop as text)&“Freelancing:eLance:Forex:July:First 25”

The folder is supposed to be available.

KOENIG Yvan (VALLAURIS, France) dimanche 7 juillet 2013 19:44:32

#11

Si le chemin UNIX est donné par un autre segment de code, vous pouvez demander au script d’effectuer la conversion.


set UNIXpath to POSIX path of "/kris/Desktop/Freelancing/eLance/Forex/July/First 25"
set HFSpath to (POSIX file uNIXpath as text)

KOENIG Yvan (VALLAURIS, France) dimanche 7 juillet 2013 21:04:56

#12

Yes, okay. HFS+, got it. The saving is working properly, now.

I started toying with the code to cater it more precisely, and have run into trouble. There are five different segments of info, and I’ve renamed the variables so they make more sense to me when I lay them out.

Before, the keyword was getting placed first, followed by the three headers (H1, H2, h3). The end formatting should look like this (note that anything with a * never changes):

Title
Supporting paragraph 1.*

keyword

Supporting paragraph 2.*

H1
Supporting paragraph.*

H2
Supporting paragraph.*

h3
Supporting paragraph.*

But when I try to play with the way Pages is being told to “set body text”, I get the following error: "error "Pages got an error: Can’t set document "FX Markets and Currency Pairs" to "fx

"." number -10006 from document “FX Markets and Currency Pairs”"

on run
	tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
		
		tell row 2
			set keyword to value of cell 1
			set title to value of cell 2
			set H1 to value of cell 3
			set H2 to value of cell 4 # I apologizes, this son of a b_tch change D2 to d2
			set h3 to value of cell 5
		end tell # row
	end tell # Numbers
	
	set docName to title
	set targetFolder to (path to desktop as text) & "Freelancing:eLance:Forex:July:First 25" #edit to change folder location	
	
	set myNewDoc to my makeNewIworkDoc("Pages", docName, targetFolder)
	set skip2lines to linefeed & linefeed & linefeed & linefeed
	tell application "Pages" to tell document docName
		set body text to keyword & skip2lines
		set italic of first paragraph to true
		set bold of last character to true
		set last character to H1 & skip2lines
		set bold of characters -3 thru -2 to false
		set last character to H2 & skip2lines
		set bold of characters -3 thru -2 to false
		set last character to h3 & skip2lines
		set bold of characters -3 thru -2 to false
		set bold of last character to false
	end tell # Pages
end run
#13

I guess that the culprit is the instruction
set docName to title

It’s supposed to get the title of the Numbers document but
(a) you are not speaking to Numbers but at the script editor or to the script itself
(b) a Numbers document don’t have a title, it has a name.

Replace the culprit by :


tell application "Numbers" to tell document 1
	set docName to name
end tell
if docName ends with "numbers" then
	set docName to (text 1 thru -8 of docName) & "pages"
else
	set docName to docName & ".pages"
end if

and I guess that you will get a better behaviour.

KOENIG Yvan (VALLAURIS, France) lundi 8 juillet 2013 17:12:04