14 Replies Latest reply: Oct 8, 2014 6:42 AM by SGIII
fred6633 Level 1 Level 1 (0 points)

Hello,

I use Numbers 2.3.

I have som documents with a lot of sheets. Each sheet contains one table. I've been trying to write a script that gives me a list to select a sheet and then jump to last row. But I can't get it to work.  Here is my script.

 

tell application "Numbers"

             activate

  set theList to every sheet of front document

  tell front document

  tell sheet (choose from list theList)

  tell table 1

  set the selection range to last cell of column "B" of last row

                         end tell

                 end tell

             end tell

  end tell


Help would be appreciated.


Fred

set theList

 

 

 

 

 

  tell front


 



iMac, Mac OS X (10.6.8)
  • Hiroto Level 5 Level 5 (5,945 points)

    Hello

     

    As far as I know, we need to switch the current sheet before setting selection range of a table (in Numbers v2). So you may try something like this.

     

     

    _main()
    on _main()
        tell application "Numbers" -- Numbers v2
            tell document 1
                set r to choose from list (get sheets's name)
                if r = false then error number -128
                set r to r's item 1
                tell sheet r
                    my select_sheet(it) -- [1]
                    tell table 1
                        set selection range to row -1's cell 2
                    end tell
                end tell
            end tell
        end tell
        (*
            [1] this is required to swtich current sheet in Numbers v2
        *)
    end _main
    
    on select_sheet(_sheet)
        (*
            reference _sheet : sheet object of Numbers v2
        *)
        tell application "Numbers"
            set _name to _sheet's name
            set _doc to (_sheet as record)'s every reference's item 1
        end tell
        tell application "System Events"
            tell process "Numbers"
                set frontmost to true
                tell (window 1 whose subrole = "AXStandardWindow" and title = _doc's name)
                    tell splitter group 1
                        tell splitter group 1
                            tell scroll area 1
                                tell outline 1
                                    tell (row 1 whose group 1's static text 1's value = _name)
                                        set selected to true
                                    end tell
                                end tell
                            end tell
                        end tell
                    end tell
                end tell
            end tell
        end tell
    end select_sheet
    
    

     

     

    Tested with Numbers v2.0.5 under OSX 10.6.8.

     

    Hope this helps,

    H

     

    PS. It uses GUI scripting, which you need to enable in advance.

     

    EDIT: added PS.

  • fred6633 Level 1 Level 1 (0 points)

    Thank you very much Hiroto!

    It worked also in Mavericks. I had to forgot to edit my profile

     

    Fred

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

    You're quite welcome! Glad to know it serves you well.

     

    H

  • fred6633 Level 1 Level 1 (0 points)

    Would it also be possible to set the selection range to last row that is not empty of column B? Some sheets have empty lines in the end.

     

    Fred

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

    Hello

     

    If I understand it correctly, you may try something like the following script. It will select column B of last row in used range of table 1. (If table 1 is blank table, it will select B1.)

     

    Regards,

    H

     

     

    _main()
    on _main()
        tell application "Numbers" -- Numbers v2
            tell document 1
                set r to choose from list (get sheets's name)
                if r = false then error number -128
                set r to r's item 1
                tell sheet r
                    my select_sheet(it) -- [1]
                    tell table 1
                        set _range to my used_range(it)
                        if _range is missing value then -- blank table
                            set selection range to row 1's cell 2
                        else
                            set selection range to _range's row -1's cell 2
                        end if
                    end tell
                end tell
            end tell
        end tell
        (*
            [1] this is required to swtich current sheet in Numbers v2
        *)
    end _main
    
    on select_sheet(_sheet)
        (*
            reference _sheet : sheet object of Numbers v2
        *)
        tell application "Numbers"
            set _name to _sheet's name
            set _doc to (_sheet as record)'s every reference's item 1
        end tell
        tell application "System Events"
            tell process "Numbers"
                set frontmost to true
                tell (window 1 whose subrole = "AXStandardWindow" and title = _doc's name)
                    tell splitter group 1
                        tell splitter group 1
                            tell scroll area 1
                                tell outline 1
                                    tell (row 1 whose group 1's static text 1's value = _name)
                                        set selected to true
                                    end tell
                                end tell
                            end tell
                        end tell
                    end tell
                end tell
            end tell
        end tell
    end select_sheet
    
    on used_range(_table)
        (*
            object _table : table object of Numbers v2
            return reference : reference to the used range of given table
        *)
        script o
            property strict : true -- true for strict test (with temporary format change) for empty cells, false otherwise
            property empty : 0.0 -- value of empty cell (0.0 in Numbers v2)
            property xx : {}
            property ee : {}
            on _undo()
                tell application "Numbers" to activate
                tell application "System Events"
                    tell process "Numbers"
                        keystroke "z" using {command down}
                    end tell
                end tell
            end _undo
            
            -- get text value of cells
            tell application "Numbers"
                tell _table's cell range
                    if strict then set format to text -- set format to text [1]
                    set xx to rows's cells's value
                    if strict then my _undo() -- undo format change
                    set {ix, jx} to {count rows, count columns}
                end tell
            end tell
            
            -- get row range i0..i1 and column range j0..j1 of used range
            set {i0, i1} to {ix + 1, 0}
            set {j0, j1} to {jx + 1, 0}
            repeat jx times
                set my ee's end to empty
            end repeat
            repeat with i from 1 to ix
                set x to my xx's item i
                if x ≠ ee then
                    if i < i0 then set i0 to i
                    if i > i1 then set i1 to i
                end if
                repeat with j from 1 to jx
                    if (j < j0 or j > j1) and x's item j ≠ empty then
                        if j < j0 then set j0 to j
                        if j > j1 then set j1 to j
                    end if
                end repeat
            end repeat
            
            -- build object specifier of used range
            if i0 * i1 * j0 * j1 = 0 then return missing value
            tell application "Numbers"
                tell _table
                    set c0 to row i0's cell j0's name
                    set c1 to row i1's cell j1's name
                    return a reference to range (c0 & ":" & c1)
                end tell
            end tell
            (*
                [1] An empty cell has value 0.0 even if it is formated as text.
                    Using this behaviour, empty cell can be distinguished from cell containing empty string ("").
            *)
        end script
        tell o to run
    end used_range
    
  • fred6633 Level 1 Level 1 (0 points)

    Thank you!

     

    Fred

  • SGIII Level 5 Level 5 (6,240 points)

    It worked also in Mavericks.

     

    AppleScripting has gotten easier in Numbers 3 running under Mavericks.  To jump to the last non-empty cell in the first table of a sheet chosen from a list something like this will do the job (if necessary add a try block to guard against possibility that first table doesn't have a column B with a value):

     

     

    tell application "Numbers" to tell front document
      set theSheetNames to sheets's name
      set chosenSheet to (choose from list theSheetNames) as string
      tell sheet chosenSheet's first table to set the selection range to column 2's last cell whose value is not missing value
    end tell
    
    
    
    
    

     

     

    SG

  • fred6633 Level 1 Level 1 (0 points)

    Thanks,

    But I use Numbers 2.3. With a test your script works if I first selct a sheet in the sidebar and then choose that sheet. Also, it jumps to the last row, not last non-empty row.

     

    Fred

  • SGIII Level 5 Level 5 (6,240 points)

    Hi Fred,

     

    A lot of people using Mavericks have already moved to Numbers 3. I would guess there are many more Numbers 3 users now than Numbers 2 users. So I thought it would be useful for those who come across this thread to know that the scripting for Numbers 3 is *much* easier here than the GUI scripting Hiroto had to do for Numbers 2.  In general the AppleScript support in Numbers 3 is markedly improved.

     

    SG

  • jmoore104 Level 1 Level 1 (0 points)

    Thank you so much Hiroto!    I'd been trying to figure out how to do this for some time, and your solution worked perfectly.  (I'm using Numbers 2.3 on OS X 10.7.5, for reference.)

     

    Jeff

  • Jfnall Level 1 Level 1 (0 points)

    Is this something that if created correctly on a OSX version of Numbers, would carry over and work the same on the iPad? 

  • SGIII Level 5 Level 5 (6,240 points)

    I don't think so. iOS cannot run AppleScript, at least not  yet.

     

    SG

  • fred6633 Level 1 Level 1 (0 points)

    Hi SG III,

    I've started to test Numbers 3.2.2 and your simple script works. Thank you! I added a try statement to avoid erros if I press escape.

     

    Fred

  • SGIII Level 5 Level 5 (6,240 points)

    Glad to hear it works!  Numbers 3 has advantages and disadvantages.  Its better AppleScript support is a clear advantage, making something like this unexpectedly easy.

     

    SG