Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Apple Script and Number

Hi all, my apologies if this is a silly questions but it's been a long time since I've done any type of script writing and I am a complete Apple Script amateur.


I am attempting to write a simple program that opens a Numbers document, and populates cells from one sheet, from the cells of another sheet, creates a PDF of the result, and then e-mails that PDF.


So far, I've gotten this, and I keep getting an error....


on run {input, parameters}


tell application "Numbers"

activate

open "/Users/jimmy/newfolder/Experiment.numbers"

tell the second table of the sheet {name:"Invoice"}

set the value of cell "A2" to the value of cell "K2" of the first table of the sheet {name:"Roster"}

end tell

end tell

return input

end run


The error is....


Numbers got an error: Can’t set sheet {name:"Invoice"} to value of cell "K2" of table 1 of sheet {name:"Roster"} of table 2 of sheet {name:"Invoice"}.



Once Again, apologies if this is a silly question, but I've been at it for a bit and can't quite figure out how I should be scripting this.

Posted on Jun 30, 2015 9:43 AM

Reply
16 replies

Jun 30, 2015 10:42 AM in response to anothrgrnworld

The problem is most easily explained when you consider the hierarchy.


The line of script in question is inside a 'tell the second table...' block. Therefore every command will be directed to that object. You can see this in the error message: "...cell "K2" of table 1... of table 2 of sheet "Invoice". The script is referencing the wrong location.


There are a couple of solutions. One is to nix the tell statement and be more direct:


set the value of cell "A2" of the second table of sheet "Invoice" to the value of cell "K2" of the first table of the sheet {name:"Roster"}


The other is to separate the 'get' and 'set' statements so there is no ambiguity:


tell the first table of the sheet {name:"Roster"}

set theVal to the value of cell "K2"

end tell


tell the second table of the sheet {name:"Invoice"}

set the value of cell "A2" to theVal

end tell

Jun 30, 2015 12:43 PM in response to anothrgrnworld

Below are generalized handlers that highlight the doc-sheet-table-cell hierarchy.


These handlers assume you have already opened the documents via script or manually.


If you are copying lots of values, you'd probably want to read the values into a list-of-lists first and convert to a tab-delimited string that the script would then "paste" into the destination. But the approach here works well enough for most cases.


Note that you don't have to write sheet {name:"Roster"}. Just write sheet "Roster"


SG



--copies value from a specified location in a Numbers document and places that value in a a specified location in the same or another document


(*

Example: to copy value in cell B2 of table 'MyTableName' on sheet 'MySheetName' in 'MyDocName1' to cell B2 of table 'MyTableName1' on sheet 'MySheetName2' in 'MyDocName2', use these two lines:


set v to getValueOfCell("MyDocName1.numbers", "MySheetName", "MyTableName", 2, 2)

setValueOfCell(v, "MyDocName2.numbers", "MySheetName2", "MyTableName2", 2, 2)


*)



to getValueOfCell(docName, sheetName, tableName, rowNumber, colNumber)

try

tell application "Numbers"

tell document docName

tell sheet sheetName

tell table tableName

tell rowrowNumber'scellcolNumber

set res to value

end tell

end tell

end tell

end tell

end tell

return res

on error eMsg number eNum

error "Can't getValueOfCell: " & eMsg number eNum

end try

end getValueOfCell


to setValueOfCell(cellValue, docName, sheetName, tableName, rowNumber, colNumber)

try

tell application "Numbers"

tell document docName

tell sheet sheetName

tell table tableName

tell rowrowNumber'scellcolNumber

set value to cellValue

end tell

end tell

end tell

end tell

end tell

on error eMsg number eNum

error "Can't setValueOfCell: " & eMsg number eNum

end try

end setValueOfCell

Jun 30, 2015 6:55 PM in response to SGIII

Thank you both for your answers. I am starting to understand a bit more now but unfortunately neither solution worked for me.


What am I doing wrong here?




tell application "Numbers"

activate

open "/Users/joseph/Dropbox/Experiment.numbers"


set v to getValueOfCell("Experiment.numbers", "Roster", "ActiveStudents", 2, 11)

try

tell application "Numbers"

tell document "Experiment.numbers"

tell sheet "Roster"

tell table "ActiveStudents"

tell row 2 cell 11

set res to value

end tell

end tell

end tell

end tell

end tell

return res

on error eMsg number eNum

error "Can't getValueOfCell: " & eMsg number eNum

end try

end tell

end


to setValueOfCell(v, "Experiment.numbers", "Invoice", "invoiceTable", "2", "1")

try

tell application "Numbers"

tell document "Experiment.numbers"

tell sheet "Invoice"

tell table "InvoiceTable"

tell row 2 cell 1

set value to cellValue

end tell

end tell

end tell

end tell

end tell

on error eMsg number eNum

error "Can't setValueOfCell: return input end run" & eMsg number eNum

end try

end setValueOfCell

Jun 30, 2015 7:05 PM in response to anothrgrnworld

You'll want to leave the two handlers (subroutines beginning with to) just as they are. Don't substitute in hard-coded values for the green variables there.


Then call the handlers by substituting your values "between the quotes" in these two lines:


set v to getValueOfCell("MyDocName1.numbers", "MySheetName", "MyTableName", 2, 2)

setValueOfCell(v, "MyDocName2.numbers", "MySheetName2", "MyTableName2", 2, 2)


SG

Jun 30, 2015 7:17 PM in response to SGIII

Thank you for the prompt reply, so this should be the code?




tell application "Numbers"


activate

open "/Users/joseph/Dropbox/Experiment.numbers"


set v to getValueOfCell("Experiment.numbers", "Roster", "ActiveStudents", 2, 11)

setValueOfCell(v, "Experiment.numbers", "Invoice", "invoiceTable", 2, 1)



to getValueOfCell(docName, sheetName, tableName, rowNumber, colNumber)

try

tell application "Numbers"

tell document docName

tell sheet sheetName

tell table tableName

tell rowrowNumber'scellcolNumber

set res to value

end tell

end tell

end tell

end tell

end tell

return res

on error eMsg number eNum

error "Can't getValueOfCell: " & eMsg number eNum

end try

end getValueOfCell


to setValueOfCell(cellValue, docName, sheetName, tableName, rowNumber, colNumber)

try

tell application "Numbers"

tell document docName

tell sheet sheetName

tell table tableName

tell rowrowNumber'scellcolNumber

set value to cellValue

end tell

end tell

end tell

end tell

end tell

on error eMsg number eNum

error "Can't setValueOfCell: " & eMsg number eNum

end try

end setValueOfCell


Because when I run that code I receive the message: Expected “end” or “end tell” but found “to”.


And when I put in an end tell after the set v command, I receive the message: Can’t continue getValueOfCell.

Jun 30, 2015 7:39 PM in response to anothrgrnworld

Assuming you have your Experiment.numbers document already open (and you've got your Sheet and Table names right and you're reading from cell K2 and writing to cell A2):



set v to getValueOfCell("Experiment.numbers", "Roster", "ActiveStudents", 2, 11)

setValueOfCell(v, "Experiment.numbers", "Invoice", "invoiceTable", 2, 1)



to getValueOfCell(docName, sheetName, tableName, rowNumber, colNumber)

try

tell application "Numbers"

tell document docName

tell sheet sheetName

tell table tableName

tell rowrowNumber'scellcolNumber

set res to value

end tell

end tell

end tell

end tell

end tell

return res

on error eMsg number eNum

error "Can't getValueOfCell: " & eMsg number eNum

end try

end getValueOfCell


to setValueOfCell(cellValue, docName, sheetName, tableName, rowNumber, colNumber)

try

tell application "Numbers"

tell document docName

tell sheet sheetName

tell table tableName

tell rowrowNumber'scellcolNumber

set value to cellValue

end tell

end tell

end tell

end tell

end tell

on error eMsg number eNum

error "Can't setValueOfCell: " & eMsg number eNum

end try

end setValueOfCell




SG

Jun 30, 2015 11:18 PM in response to anothrgrnworld

I've gotten a little further and was able to successfully export the completed document as a PDF, but I have two questions:


1) How can I specify a folder other than my desktop? I tried /Users/joseph/newfolder but it didn't work unless I input the to desktop command.


2) How can I export just the first sheet "Invoice" from the file? I don't want all three pages to show up in the PDF.


Thanks!!

Jul 1, 2015 8:52 AM in response to anothrgrnworld

anothrgrnworld wrote:


1) How can I specify a folder other than my desktop? I tried /Users/joseph/newfolder but it didn't work unless I input the to desktop command.



You can choose a different folder with something like this.


SG


tell application "Numbers"

tell document 1

set theFolder to choose folderdefault locationpath todocuments folder

set theFilePath to (theFolder as text) & "MyFileName.numbers"


saveinfiletheFilePath

end tell

end tell

Jul 5, 2015 8:57 AM in response to SGIII

At what point in the script would I insert that bit? Also, I'm not quite sure how to format the "path"...


For instance, if I wanted to save to /Users/joseph/Dropbox what would the format look like of that path?


Right now, my export code looks like this:


tell application "Numbers"

tell document 1

set theFolder to choose folder default location path to "Mac-SD:users:joseph:Dropbox"

end tell

end tell


property exportFileExtension : "pdf"

property useEncryptionDefaultValue : false


set the defaultDestinationFolder to theFolder


set usePDFEncryption to useEncryptionDefaultValue

tell application "Numbers"


activate

try

if not (exists document 1) then error number -128




set documentName to the name of the front document

if documentName ends with ".numbers" then ¬

set documentName to text 1 thru -9 of documentName


tell application "Finder"

set exportItemFileName to documentName & "." & exportFileExtension

set incrementIndex to 1

repeat until not (existsdocument fileexportItemFileName of defaultDestinationFolder)

set exportItemFileName to ¬


documentName & "-" & (incrementIndex as string) & "." & exportFileExtension

set incrementIndex to incrementIndex + 1

end repeat

end tell

set the targetFileHFSPath to (defaultDestinationFolder as string) & exportItemFileName


with timeout of 1200 seconds

if usePDFEncryption is true then


export front documenttofiletargetFileHFSPath ¬


asPDFwith properties {password:providedPassword}

else


export front documenttofiletargetFileHFSPathasPDF

end if

end timeout


on error errorMessagenumbererrorNumber

if errorNumber is not -128 then

display alert "EXPORT PROBLEM" message errorMessage

end if

error number -128

end try

end tell


end

Apple Script and Number

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.