9 Replies Latest reply: Mar 31, 2013 8:59 PM by E.Scupp
E.Scupp Level 1 Level 1 (0 points)

I'm working in a Numbers spreadsheet with over 50,000 rows.

I'd like to be able to navigate to specific cells directly, without using scrolling, etc.

Thanks,

Eddy


Numbers '09 v2.3, OS X Mountain Lion (10.8.3)
  • jaxjason Level 4 Level 4 (3,460 points)

    I dont think there is a keyboard shortcut, but i dont think it would be hard at all to make a quick applescript to do this. It pops up a inputbox and you type in the number of the row to jump to in the current table. and it selects taht cell.

     

    At least i think it should be easy. I'm at work on lunch and cannot try it till i get home later.

     

    Jason

  • E.Scupp Level 1 Level 1 (0 points)

    Jason,

    Thanks for the reply.

    I've been looking at the Applescript commands etc. There are some that are very close; a property of the class 'cell' is 'name' which is the letter/number coordinates, but since I've not scripted before I haven't figured out how to get from these to a script that does as you describe, enter coordinates and go there.

    It's a good lead, I'm just not quite there yet.

    Eddy

  • Hiroto Level 5 Level 5 (5,790 points)

    Hello

     

    You may try such a script as listed below.

    Select a cell in the target table and run this script. Specify the cell name, e.g., A3000, in resulting dialog and it will select the cell.

     

    -- select cell.applescript

    _main()
    on _main()
        tell application "Numbers"
            if not (exists document 1) then return
            set {table:_table} to my _selection(document 1)
            if _table is missing value then
                display dialog "Select some cell(s) in target table first"
                return
            end if
            tell _table
                set {rk, cn} to {count rows, column -1's name}
            end tell
            
            repeat
                display dialog "Enter cell name" & return & ¬
                    "  row in 1.." & rk & return & ¬
                    "  column A.." & cn default answer ("A" & rk) with title "Select Cell"
                set cellname to text returned of result
                try
                    tell _table
                        set selection range to range cellname
                    end tell
                    exit repeat
                on error
                    display dialog "Unable to select the specified cell: " & cellname
                end try
            end repeat
        end tell
    end _main
    
    on _selection(doc)
        (*
            reference doc : target document
            return record : {range:_range, table:_table, sheet:_sheet}
                _range = reference to named range in selection
                _table = table object to which selection range belongs
                _sheet = sheet object to which selection range belongs
        *)
        (*
            Limitation
                Numbers allows to select uncontinuous regions
                but its scripting interface does not provide decent method to retrieve them.
            
                If uncontinuous regions are selected, 'selection range' returns the minimum continuous region
                which includes all the regions in selection.
        *)
        script o
            property parent : {}
            property pp : {}
            local q, r, s, _range, _table, _sheet
            tell application "Numbers"
                set pp to doc's every sheet's every table's selection range as list
                repeat with p in my pp -- per sheet
                    set q to p's every reference -- retrieve object (filtering out missing value)
                    if q ≠ {} then
                        set q to q's item 1 -- selection range object [1]
                        set r to q as record -- selection range object specifier record [2]
                        set _table to r's every reference's item 1 -- container table reference [3]
                        set s to (a reference to _table's selection range) -- selection range reference [4]
                        set _range to (a reference to _table's range (s's name)) -- named range reference [5]
                        set _sheet to (_table as record)'s every reference's item 1 -- container sheet reference [3]
                        return {range:_range, table:_table, sheet:_sheet}
                    end if
                end repeat
                return {range:missing value, table:missing value, sheet:missing value}
            end tell
            (*
                [1] class specifier for 'range' is broken in Numbers 09
                [2] «class want» value is broken in Numbers 09
                [3] simple method to get «class from» value without asking for «class from» key which causes trouble in recompilation of the token 'from'.
                [4] proper reference of selection range object
                [5] proper reference of named range object
            *)
        end script
        tell o to run
    end _selection
    

     

    In case, there're two ways to invoke it via menu or keyboard shortcut. Recipes follow.

     

    # Recipe 1 (via script menu)

    1) Open /Applications/Utilities/AppleScript Editor.app; and

    2) if script menu is not yet enabled, open Preferences… > General and enable "Show Script menu in menu bar"; and

    3) copy the code listed below to new document; and

    4) save it as compiled script or script bundle with name, e.g., "select cell.scpt" or "select cell.scptd", in  ~/Library/Scripts/Applications/Numbers directory so that it appear in script menu in Numbers.

     

     

    # Recipe 2 (via services menu under 10.6 or later)

    1) Open /Applications/Automator.app; and

    2) choose "Service" template; and

    3) drag "Run AppleScript" action from the left pane to the right pane; and

    4) replace the existing template code with the code listed below; and

    5) set the service attributes so that service receives [no input] in [Numbers.app], where [...] is set via drop down menu in Automator workflow editor window; and

    6) save the workflow with name, e.g., "select cell"; and

    7) quit Automator.app.

     

    To set keyboard shortcut for the service:

    8) open /Applications/System Preferences; and

    9) select Keyboard > Keyboard Shortcuts; and

    10) select Services in left pane; and

    11) set keyboard shortcut for the service as, e.g., command + shift + 5.

     

    Hope this may help,

    H

  • jaxjason Level 4 Level 4 (3,460 points)

    I was just going to do something a whole lot simpler need to figure out to tell the selected sheet and table... but this is close.

     

    display dialog "Enter cell name" default answer ("A1") with title "Select Cell"

    set cellname to text returned of result

    tell application "Numbers"

      activate

              tell the front document

                  tell the first sheet

                       tell the first table

                             try

                                   set selection range to range named cellname

                             on error

                                   display dialog "Cell not in table"

                                   set selection range to range named "A1"

                             end try

                       end tell

                  end tell

              end tell

    end tell

     

    Jason

  • jaxjason Level 4 Level 4 (3,460 points)

    got it

     

    display dialog "Enter cell name" default answer ("A1") with title "Select Cell"

    set cellname to text returned of result

     

    tell application "Numbers"

      activate

              tell the front document

                        repeat with i from 1 to the count of sheets

                                  tell sheet i

                                            set x to the count of (tables whose selection range is not missing value)

                                            set myshtnm to the name


                                  end tell

                                  if x is not 0 then

                                            set the current_sheet_index to i

                                            exit repeat

                                  end if

                        end repeat

     

                        tell sheet current_sheet_index

                                  set the current_table to (the first table whose selection range is not missing value)

                                  tell the current_table

                                            try

                                                      set selection range to range named cellname

                                            on error

                                                      display dialog "Cell not in table"

                                                      set selection range to range named "A1"

                                            end try

                                  end tell

                        end tell

              end tell

    end tell

  • E.Scupp Level 1 Level 1 (0 points)

    Hiroto and Jason,

     

    You are both awesome !

     

    Both scripts worked within the sheet that was active.

    I'll be studying both to learn the details and as reference for more scripts I'll be writing for this project.

     

    If you are aware of tutorials or reference guides, please send links.

     

    Thanks much,

     

    Eddy

  • Hiroto Level 5 Level 5 (5,790 points)

    Hello

     

    I don't know of any tutorials or links specific to scripting Numbers. So listed below are some general links.

    And I'd like to add that, as a matter of fact, you'd need some (indeed much) trials and errors to learn scripting applications via AppleScript because every application has its own spells and peculiarities although AppleScript language itself is rather simple and well-defined. This makes learning AppleScript unnecessarily more involved and difficult than it is.

     

    Anyway, here're some links.

     

    # Primary reference:

     

    AppleScript Language Guide

    http://developer.apple.com/library/mac/documentation/AppleScript/Conceptual/Appl eScriptLangGuide/introduction/ASLR_intro.html

    http://developer.apple.com/library/mac/documentation/AppleScript/Conceptual/Appl eScriptLangGuide/AppleScriptLanguageGuide.pdf

     

     

    # Additional materials:

     

    Technical Note TN2065 : do shell script in AppleScript

    http://developer.apple.com/library/mac/technotes/tn2065/_index.html

     

    AppleScript release notes:

    http://developer.apple.com/library/mac/releasenotes/AppleScript/RN-AppleScript/I ntroduction/Introduction.html

    http://developer.apple.com/library/mac/releasenotes/AppleScript/RN-AppleScript/R N-AppleScript.pdf

     

    Some tutorials (have not reviewed through, though)

    http://www.macosxautomation.com/applescript/learn.html

     

     

    # Helper applications for GUI scripting:

     

    System Events

    /System/Library/CoreServices/System Events.app

     

    Accessibility Inspector

    /Developer/Applications/Utilities/Accessibility Utilities/Accessibility Inspector.app

     

     

    # Low-level internals references (legacy documents; yes AppleScript is a legacy language! ):

     

    Apple Events Programming Guide

    http://developer.apple.com/legacy/mac/library/documentation/AppleScript/Conceptu al/AppleEvents/intro_aepg/intro_aepg.html

    http://developer.apple.com/legacy/mac/library/documentation/AppleScript/Conceptu al/AppleEvents/AppleEvents.pdf

     

    Apple Event Manager Reference

    http://developer.apple.com/legacy/mac/library/documentation/Carbon/Reference/App le_Event_Manager/Reference/reference.html

    http://developer.apple.com/legacy/mac/library/documentation/Carbon/Reference/App le_Event_Manager/apple_event_manager.pdf

     

    Open Scripting Architecture Reference

    http://developer.apple.com/legacy/mac/library/documentation/Carbon/Reference/Ope n_Scripti_Architecture/Reference/reference.html

    http://developer.apple.com/legacy/mac/library/documentation/Carbon/Reference/Ope n_Scripti_Architecture/Open_Scripti_Architecture.pdf

     

     

    Hope this may be of some help.

    Good luck,

    H

  • Hiroto Level 5 Level 5 (5,790 points)

    Hello

     

    Yes, getting current selection is a real mess in Numbers scripting if you have multiple sheets and tables.

    I often use a handler I have written some time ago to get selection references in Numbers 09.

    Write once and use much, as I'm lazy.

     

    Best wishes,

    H

  • E.Scupp Level 1 Level 1 (0 points)

    Hello,

     

    Thanks much for the great list of references !

    I've got my work cut out for me.

     

    Best regards,

     

    E.