Previous 1 2 Next 15 Replies Latest reply: Mar 19, 2013 12:42 PM by Hiroto Go to original post
  • Hiroto Level 5 Level 5 (5,945 points)

    Hello

     

    I understand your point very well and feel the same that spreadsheet should be capable of performing partial sort.

    The script below is my attempt to fix it. Bad news is it is a bit noisy in making temporary table, moving range to it, sorting the range, moving the range back and deleting the temporary table. Good news is it works as expected.

     

    Here're two recipes; one for script menu and another for services menu (preferable under 10.6 or later)

     

    # 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., "sort selection.scpt" or "sort selection.scptd", in  ~/Library/Scripts/Applications/Numbers directory so that it appear in script menu in Numbers.

     

    Usage (via script menu):

    Select target range in Numbers, invoke the script via script menu, enter the sort specifications when asked and it will sort the selection range according to the sort specs.

     

     

    # Recipe 2 (via services menu)

    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., "sort selection"; and

    7) quit Automator.app.

     

    To set keyboard shortcut for "sort selection" 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 "sort selection" service as, e.g., command + shift + 6.

     

    Usage (via services menu):

    Select target range in Numbers, invoke the script via Services menu or keyboard shortcut if defined, enter the sort specifications when asked and it will sort the selection range according to the sort specs.

     

     

    # Notes

    * Sort specifications (specs) are in the form as

        specs = spec[,spec,...]

        spec = <column relative index in selection>[a|d]

        a = ascending (default)

        d = descending

       

        e.g. = 1a,3d

        which means to sort by 1st column in selection in ascending order and by 3rd column in selection in descending order.

        The specs are in primary-key-first order, i.e., 1a is the primary key and 3d is the secondary key.

        You can use 1,3d instead of 1a,3d because "a" is the default direction.

     

    * Tested as script under 10.5.8 and also as Automator service with keyboard shortcut under 10.6.5.

     

     

    # Script

     

    sort selection.applescript

    -------------------------

    (*
        sort selection range only
        v0.2
    *)
    _main()
    on _main()
        -- (0) get original selection specifiers
        tell application "Numbers"
            activate
            set {range:_range, table:_table, sheet:_sheet} to my _selection(document 1)
            tell _range
                if it is missing value then return
                set {ck, rk} to {count columns, count rows}
            end tell
        end tell
        
        -- (1) accept sort specifications
        tell application "Numbers"
            repeat
                display dialog ¬
                    "Enter sort specifications (primary key first)" & return & ¬
                    "  spec[,spec, ...]" & return & ¬
                    "  spec = <column relative index in selection>[a|d]" & return & ¬
                    "  a = ascending (default)," & return & ¬
                    "  d = descending" & return & ¬
                    "  e.g. = 1a,3d" default answer "1a"
                try
                    set specs to my _split(",", text returned of result)
                    repeat with s in specs
                        try
                            set col to s as integer
                            set dir to -1
                        on error
                            try
                                set col to s's text 1 thru -2 as integer
                                set dir to s's character -1
                                if dir = "a" then
                                    set dir to -1
                                else if dir = "d" then
                                    set dir to 1
                                else
                                    error number 8000
                                end if
                            on error number errn
                                display dialog "invalid spec: " & s
                                error number errn
                            end try
                        end try
                        if col > ck or col < 1 then
                            display dialog "column index is out of range [1, " & ck & "]: " & col
                            error number 8001
                        end if
                        set s's contents to {col, dir}
                    end repeat
                    exit repeat
                on error number errn
                    if errn = -128 then error number -128 -- user cancel
                end try
            end repeat
        end tell
        
        -- (2) mark to move the original selection range
        tell application "System Events"
            tell process "Numbers"
                keystroke "x" using {command down, shift down} -- edit > mark to move
            end tell
        end tell
        
        -- (3) make new work table and select range B2
        tell application "Numbers"
            tell _sheet
                set _wktable to make new table at end with properties {column count:ck + 1, row count:rk + 1}
                tell _wktable
                    set selection range to range "B2"
                end tell
            end tell
        end tell
        
        -- (4) move the original selection range to work table
        tell application "System Events"
            tell process "Numbers"
                keystroke "v" using {command down, shift down} -- edit > move
            end tell
        end tell
        
        -- (5) sort the moved selection range in work table
        tell application "Numbers"
            set {range:_wkrange, table:_wktable} to my _selection(document 1)
            repeat with s in specs's reverse
                set {col, dir} to s's contents
                tell _wktable
                    if dir = -1 then
                        sort by _wkrange's column col direction ascending in rows _wkrange
                    else
                        sort by _wkrange's column col direction descending in rows _wkrange
                    end if
                end tell
            end repeat
        end tell
        
        -- (6) mark to move the sorted selection range in work table
        tell application "System Events"
            tell process "Numbers"
                keystroke "x" using {command down, shift down} -- edit > mark to move
            end tell
        end tell
        
        -- (7) select the original selection range
        tell application "Numbers"
            tell _table
                set selection range to _range
            end tell
        end tell
        
        -- (8) move the sorted selection range in work table to orignal table
        tell application "System Events"
            tell process "Numbers"
                keystroke "v" using {command down, shift down} -- edit > move
            end tell
        end tell
        
        -- (9) delete the work table
        tell application "Numbers"
            delete _wktable
        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
    
    on _split(d, t)
        local astid, astid0, tt
        set astid to a reference to AppleScript's text item delimiters
        try
            set {astid0, astid's contents} to {astid's contents, {d}}
            set tt to t's text items
            set astid's contents to astid0
        on error errs number errn
            set astid's contents to astid0
            error errs number errn
        end try
        return tt
    end _split
    

    -------------------------

     

    Hope this may help,

    H

Previous 1 2 Next