Saturday, July 4, 2020

#1 2020-04-25 04:54:24 pm

jhogue
Member
Registered: 2020-04-25
Posts: 6

Need help scripting Numbers

I need some help scripting Apple’s Numbers.

What I want to do is:
    Open a Numbers file
    Search for a specific value in Column E. There will be many rows with the same value.
    Create a list of all the values in column I using only the rows found in the search.
        The list needs each Column I value separated with a semicolon and a space


So far I have Numbers open and the document open.
When I try to reference the sheet I am constantly getting “can’t find sheet “MyData” “
There is a sheet with that name.
I even created a new sheet “Sheet 1” and tried to reference it just like all the example do.
This gives me the same error “can’t find sheet 1”

What am I doing wrong?

Here’s my test script so far:

set TestFile to "Macintosh HD:Users:UserName:MyFile.numbers"
tell application "Numbers"
    open TestFile
    tell sheet "MyData" to tell table 1
        set DataVar to value of cell "A2"
    end tell
   
    display dialog MyTest
end tell
J

Offline

 

#2 2020-04-26 02:07:53 am

KniazidisR
Member
Registered: 2019-03-03
Posts: 1259

Re: Need help scripting Numbers

Hi, jhogue. Welcome.

When you referring to sheet, you should know that its parent object is not Numbers.app but opened document in the Numbers.app. It is hierarchy of Numbers.app (object model). So, to access some object of this hierarchy you should go thru all its parent objects:

Applescript:


set TestFile to "Macintosh HD:Users:UserName:MyFile.numbers"
set MyTest to "Testing Numbers.app"

tell application "Numbers"
   set openedDocument to open TestFile -- parent object of sheet and child object of app
   tell openedDocument to tell sheet "MyData" to tell table 1
       set DataVar to value of cell "A2"
   end tell
   display dialog MyTest
end tell

Absent of telling to document 1 (openedDocument, in my script) raises your error, because doesn't exist

sheet "MyData" of application "Numbers", but exists only:
sheet "MyData" of document 1 of application "Numbers"

Last edited by KniazidisR (2020-04-26 02:35:31 am)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 13.1
Ram: 4 GB

Offline

 

#3 2020-04-26 10:49:52 am

jhogue
Member
Registered: 2020-04-25
Posts: 6

Re: Need help scripting Numbers

Fantastic!
This works! Thank you!

I'm now trying to FIND a value in a specific column (E).
There does not appear to be any commands to "find" or "search" for values in a table/row/column.

I've been trying to first select the column then find the row address by column E's cell value.
...which is not working :-(

This is what I'm trying:

set TestData to "data 123"

tell application "Numbers"
    set MyDoc to open TestFile
    tell MyDoc to tell sheet "MyData" to tell table 1
       
        set the selection range to column "E"
        set DataVar to address of the row of cell whose value is TestData
       
    end tell
    display dialog DataVar
end tell


Any thoughts on how I can make this work?

J

Offline

 

#4 2020-04-26 01:23:12 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4533

Re: Need help scripting Numbers

Applescript:

set TestData to "data 123"

tell application "Numbers"
   set MyDoc to open TestFile
   tell MyDoc to tell sheet "MyData" to tell table 1
       --set the selection range to column "E"
       set DataVar to address of the row of first cell of column "E" whose value is TestData
   end tell
   display dialog DataVar
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) dimanche 26 avril 2020 21:23:05

Offline

 

#5 2020-04-26 02:33:00 pm

jhogue
Member
Registered: 2020-04-25
Posts: 6

Re: Need help scripting Numbers

Merci !

Offline

 

#6 2020-04-26 02:52:16 pm

Fredrik71
Member
Registered: 2019-10-23
Posts: 279

Re: Need help scripting Numbers

If you study Sal Soghoian's website you could learn a lot of Apple iWorks.
http://iworkautomation.com


I  have skied the highest mountain without logic, and still its the imagination that take me anywhere. Its the desire of something that matters for any knowledge I have or could learn.

Offline

 

#7 2020-04-26 04:23:30 pm

jhogue
Member
Registered: 2020-04-25
Posts: 6

Re: Need help scripting Numbers

I am actively using that site for scripting Numbers.
My problem is that it has been many years since I have touched Applescript :-(

I'm going to use it to make my "working from home" life a lot easier :-)

J

Offline

 

#8 2020-04-27 01:28:12 am

Fredrik71
Member
Registered: 2019-10-23
Posts: 279

Re: Need help scripting Numbers

Great, you could also check this site from the same person (Sal)

https://userautomation.com/article/blog0003.html

From here you will find many useful handlers for Keynote or give ideas how to build them yourself.

Regards.


I  have skied the highest mountain without logic, and still its the imagination that take me anywhere. Its the desire of something that matters for any knowledge I have or could learn.

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)