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

Alphabetize pop-up menu

Is it possible to alphabetize the entries in a pop-up menu in Numbers? Entering them in alphabetic order isn't possible, since I'll be adding new entries weekly.

Numbers-OTHER

Posted on Jul 18, 2013 7:28 AM

Reply
11 replies

Jul 19, 2013 12:29 AM in response to Wayne Contello

Thank you for your answer! I am making a database with suppliers. I used the pop-up menu option to put there the main product they make. I want that in something like a pop-up menu, to make sure that everyone uses the same kind of keywords to describe the main product, since this file will be used by multiple users. I would really appreciate an advice, I truely am a mac noob 😉

Jul 19, 2013 2:47 AM in response to AKKK

Hi AKKK,


To take a screen shot, hold down the shift and command keys, then type 4. The cursor will change to crosshairs. Release the shift and command keys. Drag over that part of your screen then release the mouse/trackpad. A screen shot will appear on your desktop. In a reply to a message, click on the camera icon:

User uploaded file

and Choose File.


You may have to try this twice. Camera icon sometimes needs a wake-up call, but works the second time.


Regards,

Ian.

Jul 19, 2013 10:30 PM in response to AKKK

Going back to your original question...


It is not possible to rearrange the item list of a popup menu. It is possible to reconstruct the list, either by editing the individual items or by restarting from scratch. But this is a manual operation—each item must be entered individually.


The edits apply only to one menu.


You can paste the new menu into other cells (or into whole rows or columns), but doing so also pastes the current setting of that menu, replacing whatever menu and setting were previously in the cell.


In general, if you're working from top to bottom, you'll want to edit the next menu to be used, then fill down from there, leaving the ones above (where a value has already been chosen) untouched.


Regards,

Barry

Jul 20, 2013 6:51 PM in response to AKKK

Numbers doesn't have a way to alphabetize or sort a pop-up or make it dynamic. But there is a script that can help. It will take a list of items from a Numbers table and will create popup menus from it.


  1. Start the AppleScript Editor application
  2. Paste the entire script into it from below
  3. Select a column of data that you want to use for your popup menu
  4. Run the script
  5. Select the cell(s) that you want to make into popups
  6. Click OK in the dialog box from the script (you'll understand when you get this far) then wait for it to do its thing.
  7. Click OK in the "Done" dialog box


I wish I knew who to attribute the script to. The person's name doesn't appear in the comments and I have forgotten who it was. Wasn't me.


-- Script to populate a Numbers pop-up list.

-- Instructions:

-- GUI scripting must be on in System Preferences

-- Create a list of items. Must be a contiguous range of cells in a table.

-- Select the range of cells to use as items in the popup.

-- Run the script.

-- A dialog box will appear asking you to select which cells you want to turn into pop-ups

-- Select the cells then click OK on the dialog box


set tValues to my doThis(1) -- get values of the selection

if tValues is not "" then


activate

display dialog "Select the cells where you want to create the PopUp." & return & "After that, click on the 'OK' button."

my doThis(tValues) -- set the cell format of the new selection to "PopUp Menu" and set the values of the each menu item

tell application "Numbers" to display dialog "Done"

else

tell application "Numbers" to display dialog "You must select the cells in a table before running this script."

end if


on doThis(n)

tell application "Numbers"

set tTables to (tables of sheets of front document whose its selection range is not missing value)

repeat with t in tTables-- t is a list of tables of a sheet

if contents of t is not {} then -- this list is not empty, it's the selected sheet

set activeTable to (get item 1 of t)

if n = 1 then return value of cells of selection range of activeTable -- return values of the selection

set format of (cells of selection range of activeTable) to pop up menu-- set the format to pop up menu

return my setValuePopUp(n) -- set value of each menu item

end if

end repeat

end tell

return ""

end doThis


on setValuePopUp(L)

tell application "System Events"

tell process "Numbers"

set frontmost to true

delay 0.3

set inspectorWindow to missing value

set tWindows to windows whose subrole is "AXFloatingWindow"

repeat with i in tWindows

if exists radio group 1 of i then

set inspectorWindow to i

exit repeat

end if

end repeat

if inspectorWindow is missing value then


keystroke "i" using {option down, command down} -- Show Inspector

else


performaction "AXRaise" of inspectorWindow-- raise the Inspector window to the front

end if

delay 0.3

tell window 1


clickradio button 4 of radio group 1 -- the "cell format" tab

delay 0.3

tell group 2 of group 1

set tTable to table 1 of scroll area 1

set tc to count rows of tTable

set lenL to (count L)


if tc < lenL then -- ** add menu items **

repeat until (count rows of tTable) = lenL


clickbutton 1 -- button [+]

end repeat


keystrokereturn-- validate the default name of the last menu item

else if tc > lenL then -- ** remove menu items **

repeat while exists row (lenL + 1) of tTable

select row (lenL + 1) of tTable


clickbutton 2 -- button [-]

end repeat

end if


tell tTable to repeat with i from 1 to lenL-- ** change value of each menu item **

set value of text field 1 of row i to item i of L

end repeat

end tell

end tell

end tell

end tell

end setValuePopUp

Jul 21, 2013 3:58 AM in response to AKKK

Hello


Honestly I'd avoid pop-up menu in Numbers except for very simple one because it is seriouly limited in funtionality and difficult to manage due to the lack of decent interface.


That's being said, here're three AppleScript scripts you may try to manipulate pop-up menu items in Numbers 09:


1) paragraphs to popup.applescript, which converts multi-line text in each cell in selection range to pop-up menu items in the the cell.


2) popup to paragraphs.applescript, which converts pop-up menu items in each cell in selection range to multi-line text in the cell.


3) sort popup items.applescript, which sorts pop-up menu items in each cell in selection range.


* Note: The last script will use clipboard as temporary storage to get the text of selected item in pop-up menu and thus destroy the current clipboard contents.



Usages are all the same as follows:


Copy each code listed below in a new window of /Applications/Utilities/AppleScript Editor.app, select target range in Numbers 09 and run the script. Scripts are moderately tested with Numbers 2.0.5 under 10.5.8 and 10.6.5 but provided as is without any warranties. As always, please have backup copy of target document before using this sort of scripts.


* In order to use these scripts you need to enbale the option: System Preferences > Universal Access > Enable access for assistive devices.


Hope this may help.

H



# Script 1

--paragraphs to popup.applescript
(*
    convert paragraphs to popup menu items
    v0.1
*)
_main()
on _main()
    script o
        property aa : {}
        property bb : {}
        
        tell application "Numbers"
            activate
            -- get selection specfiers
            set {range:_range, table:_table} to my _selection(document 1)
            -- set pop up menus of cells in selection
            if _range is missing value then return
            tell _range
                set aa to cells
                repeat with a in my aa
                    set a to a's contents
                    set bb to (a's value as string)'s paragraphs
                    if (count my bb) > 1 then
                        set a's format to pop up menu
                        set _table's selection range to a
                        my set_popup_items(bb)
                    end if
                end repeat
                -- restore selection
                set _table's selection range to it
            end tell
        end tell
    end script
    tell o to run
end _main

on set_popup_items(dd)
    (*
        list dd : list of pop up item's string values
    *)
    tell application "System Events"
        tell process "Numbers"
            -- bring Numbers to front
            set frontmost to true
            -- open new inspector window
            tell menu bar 1's menu bar item 9 -- view
                if menu 1's menu item 11's enabled then
                    tell menu 1's menu item 11 -- new inspector
                        click
                    end tell
                else
                    tell menu 1's menu item 10 --  inspector
                        click
                    end tell
                end if
            end tell
            -- get pop up menu items
            set c to count dd
            tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
                tell radio group 1
                    tell radio button 4 -- cell
                        click
                    end tell
                end tell
                tell group 1
                    tell group 2
                        tell scroll area 1
                            tell table 1 -- pop up menu items table
                                set k to count rows
                                if k > 0 then select row 1
                            end tell
                        end tell
                        repeat c - k times
                            click button 1 -- +
                        end repeat
                        repeat k - c times
                            click button 2 -- -
                        end repeat
                        tell scroll area 1
                            tell table 1 -- pop up menu items table
                                repeat with i from 1 to c
                                    tell row i's text field 1
                                        set focused to true
                                        set value to dd's item i
                                    end tell
                                end repeat
                            end tell
                        end tell
                    end tell
                end tell
                tell (button 1 whose subrole = "AXCloseButton") -- close
                    click
                end tell
            end tell
        end tell
    end tell
end set_popup_items

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



# Script 2

--popup to paragraphs.applescript
(*
    convert popup menu items to paragraphs
    v0.1
*)
_main()
on _main()
    script o
        property aa : {}
        property bb : {}
        tell application "Numbers"
            activate
            -- get selection specifiers
            set {range:_range, table:_table} to my _selection(document 1)
            if _range is not missing value then
                tell _range
                    -- get cells and formats in 1d array
                    set {aa, bb} to {cells, cells's format}
                    repeat with i from 1 to count my aa
                        if my bb's item i = pop up menu then
                            set a to my aa's item i
                            set _table's selection range to a -- select cell with pop up menu
                            set cc to my get_popup_items() -- get pop up menu items
                            set a's format to text
                            set a's value to my _join(linefeed, cc)
                        end if
                    end repeat
                    -- restore selection range
                    set _table's selection range to it
                end tell
            end if
        end tell
    end script
    tell o to run
end _main

on get_popup_items()
    tell application "System Events"
        tell process "Numbers"
            -- bring Numbers to front
            set frontmost to true
            -- open new inspector window
            tell menu bar 1's menu bar item 9 -- view
                if menu 1's menu item 11's enabled then
                    tell menu 1's menu item 11 -- new inspector
                        click
                    end tell
                else
                    tell menu 1's menu item 10 --  inspector
                        click
                    end tell
                end if
            end tell
            -- get pop up menu items
            tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
                tell radio group 1
                    tell radio button 4 -- cell
                        click
                    end tell
                end tell
                tell group 1
                    tell group 2
                        tell scroll area 1
                            tell table 1 -- pop up menu items table
                                set pp to rows's text field 1's value
                            end tell
                        end tell
                    end tell
                end tell
                tell (button 1 whose subrole = "AXCloseButton") -- close
                    click
                end tell
            end tell
        end tell
    end tell
    return pp
end get_popup_items

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 _join(d, tt)
    local astid, astid0, t
    set astid to a reference to AppleScript's text item delimiters
    try
        set {astid0, astid's contents} to {astid's contents, {d}}
        set t to "" & tt
        set astid's contents to astid0
    on error errs number errn
        set astid's contents to astid0
        error errs number errn
    end try
    return t
end _join



# Script 3

--sort popup items.applescript
(*
    sort popup menu items
    v0.2
    
    v0.2 -    
        added codes to preserve the selected value of popup menu items.
        * this script will destroy the current clipboard contents
*)
_main()
on _main()
    script o
        property sort_options : {"Sort in ascending order", "Sort in descending order"}
        property comparator : {} -- given later
        property aa : {}
        property bb : {}
        property cc : {}
        
        on copy_as_text()
            (* this will destroy the current clipboard contents *)
            tell application "Numbers" to activate
            tell application "System Events"
                tell process "Numbers"
                    keystroke "c" using {command down}
                end tell
            end tell
            delay 0.1
            the clipboard as Unicode text
        end copy_as_text
        
        tell application "Numbers"
            activate
            -- get selection specifiers
            set {range:_range, table:_table} to my _selection(document 1)
            if _range = missing value then return
            
            -- accept sort direction
            set r to choose from list sort_options default items sort_options's item 1
            if r = false then error number -128 -- user cancel
            set r to r's item 1
            if r = sort_options's item 1 then
                set my comparator to my cmpa
            else
                set my comparator to my cmpd
            end if
            
            tell _range
                -- get cells and formats in 1d array
                set {aa, bb} to {cells, cells's format}
                repeat with i from 1 to count my aa
                    if my bb's item i = pop up menu then
                        set a to my aa's item i
                        set _table's selection range to a -- select cell with pop up menu
                        set v to my copy_as_text() -- get selected value
                        set cc to my get_popup_items() -- get pop up menu items
                        set i1 to my bsearch(cc, v) -- get index of selected value in original list
                        my msort(comparator, cc) -- sort items
                        set i2 to my bsearch(cc, v) -- get index of selected value in sorted list
                        my reorder_popup_items(cc, i2 > i1) -- reorder pop up menu items
                    end if
                end repeat
                -- restore selection range
                set _table's selection range to it
            end tell
        end tell
    end script
    tell o to run
end _main

on get_popup_items()
    tell application "System Events"
        tell process "Numbers"
            -- bring Numbers to front
            set frontmost to true
            -- open new inspector window
            tell menu bar 1's menu bar item 9 -- view
                if menu 1's menu item 11's enabled then
                    tell menu 1's menu item 11 -- new inspector
                        click
                    end tell
                else
                    tell menu 1's menu item 10 --  inspector
                        click
                    end tell
                end if
            end tell
            -- get pop up menu items
            tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
                tell radio group 1
                    tell radio button 4 -- cell
                        click
                    end tell
                end tell
                tell group 1
                    tell group 2
                        tell scroll area 1
                            tell table 1 -- pop up menu items table
                                set pp to rows's text field 1's value
                            end tell
                        end tell
                    end tell
                end tell
                tell (button 1 whose subrole = "AXCloseButton") -- close
                    click
                end tell
            end tell
        end tell
    end tell
    return pp
end get_popup_items

on reorder_popup_items(dd, moved_down)
    (*
        list dd : list of pop up item's string values
        boolean moved_down : whether selected value is moved down in list -- [1]
    *)
    tell application "System Events"
        tell process "Numbers"
            -- bring Numbers to front
            set frontmost to true
            -- open new inspector window
            tell menu bar 1's menu bar item 9 -- view
                if menu 1's menu item 11's enabled then
                    tell menu 1's menu item 11 -- new inspector
                        click
                    end tell
                else
                    tell menu 1's menu item 10 --  inspector
                        click
                    end tell
                end if
            end tell
            -- get pop up menu items
            set c to count dd
            tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
                tell radio group 1
                    tell radio button 4 -- cell
                        click
                    end tell
                end tell
                tell group 1
                    tell group 2
                        tell scroll area 1
                            tell table 1 -- pop up menu items table
                                set k to count rows
                                if k > 0 then select row 1
                            end tell
                        end tell
                        repeat c - k times
                            click button 1 -- +
                        end repeat
                        repeat k - c times
                            click button 2 -- -
                        end repeat
                        tell scroll area 1
                            tell table 1 -- pop up menu items table
                                if moved_down then -- [1]
                                    repeat with i from c to 1 by -1
                                        tell row i's text field 1
                                            set focused to true
                                            set value to dd's item i
                                        end tell
                                    end repeat
                                else
                                    repeat with i from 1 to c
                                        tell row i's text field 1
                                            set focused to true
                                            set value to dd's item i
                                        end tell
                                    end repeat
                                end if
                            end tell
                        end tell
                    end tell
                end tell
                tell (button 1 whose subrole = "AXCloseButton") -- close
                    click
                end tell
            end tell
        end tell
    end tell
    (*
        [1] In order to preserve the selected value X, (another) X must exist in popup items when X is changed to other value.
            Therefore, when X is moved down by sorting, we need to populate the popup items from bottom to top.
            Meanwhile, when X is moved up by sorting, we need to populate the popup items from top to bottom.
    *)
end reorder_popup_items

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 bsearch(xx, x)
    (*
        list xx : source list
        anything x : item to be searched in xx
        return integer : the first index of x in xx if {x} is in xx, or 0 if not.
    *)
    script o
        property aa : xx
        local i, j, k
        if {x} is not in my aa then return 0
        set i to 1
        set j to count my aa
        repeat while j > i
            set k to (i + j) div 2
            if {x} is in my aa's items i thru k then
                set j to k
            else
                set i to k + 1
            end if
        end repeat
        return i
    end script
    tell o to run
end bsearch

on cmpa(x, y) -- comparator for msort handler (sort in ascending order)
    return x > y
end cmpa

on cmpd(y, x) -- comparator for msort handler (sort in descending order)
    return x > y
end cmpd

on msort(cmp_, aa) -- v1.2f2
    (*
        Basic recursive merge sort handler having list sorted in place
    *)
    (*
        handler cmp_ : comparator
            * cmp_(x, y) must return true iff list element x and y are out of order.
        list aa : list to be sorted in place
    *)
    script o
        property parent : {} -- limit closure to minimum
        property xx : aa -- to be sorted in place
        property xxl : count my xx
        property yy : {}
        property cmp : cmp_
        on merge(p, q, r)
            (*
                property xx: source list
                integer p, q, r : absolute indices to specify range to be merged such that
                        xx's items p thru r is the target range,
                        xx's items p thru (q-1) is the first sublist,
                        xx's items q thru r is the second sublist.
                        (p < q <= r)
            *)
            local i, j, k, xp, xr, yi, yj, ix, jx
            
            if r - p = 1 then
                set xp to my xx's item p
                set xr to my xx's item r
                if my cmp(xp, xr) then
                    set my xx's item p to xr
                    set my xx's item r to xp
                end if
                return -- exit
            else
                if p < q - 1 then merge(p, (p + q) div 2, q - 1)
                merge(q, (q + r + 1) div 2, r)
            end if
            (*
                At this point, sublits xx[p, q-1] and xx[q, r] have been already sorted (p < q <= r)
            *)
            
            if my cmp(my xx's item (q - 1), my xx's item q) then
            else -- xx[p, q-1] & xx[q, r] are already sorted
                return
            end if
            
            set yy to my xx's items p thru r -- working copy for comparison
            set ix to q - p
            set jx to r - p + 1
            set i to 1
            set j to q - p + 1
            set k to p
            set yi to my yy's item i
            set yj to my yy's item j
            repeat
                if my cmp(yi, yj) then
                    set my xx's item k to yj
                    set j to j + 1
                    set k to k + 1
                    if j > jx then
                        set my xx's item k to yi
                        set i to i + 1
                        set k to k + 1
                        repeat until k > r
                            set my xx's item k to my yy's item i
                            set i to i + 1
                            set k to k + 1
                        end repeat
                        return
                    end if
                    set yj to my yy's item j
                else
                    set my xx's item k to yi
                    set i to i + 1
                    set k to k + 1
                    if i > ix then
                        set my xx's item k to yj
                        set j to j + 1
                        set k to k + 1
                        repeat until k > r
                            set my xx's item k to my yy's item j
                            set j to j + 1
                            set k to k + 1
                        end repeat
                        return
                    end if
                    set yi to my yy's item i
                end if
            end repeat
        end merge
        on _cmp_(x, y)
            (* primary comparator *)
            return x > y
        end _cmp_
        
        if xxl ≤ 1 then return
        if cmp_ = {} then set my cmp to _cmp_ -- comparator fallback
        my merge(1, (xxl + 2) div 2, xxl)
    end script
    tell o to run
end msort

Jul 21, 2013 4:35 AM in response to Hiroto

Hello


In Script 3, you may want to adjust the delay seconds in _main() :: copy_as_text() handler as follows.


Original:

        on copy_as_text()
            (* this will destroy the current clipboard contents *)
            tell application "Numbers" to activate
            tell application "System Events"
                tell process "Numbers"
                    keystroke "c" using {command down}
                end tell
            end tell
            delay 0.1
            the clipboard as Unicode text
        end copy_as_text


Adjusted:

        on copy_as_text()
            (* this will destroy the current clipboard contents *)
            tell application "Numbers" to activate
            tell application "System Events"
                tell process "Numbers"
                    keystroke "c" using {command down}
                end tell
            end tell
            delay 0.2 -- # adjust this as needed
            the clipboard as Unicode text
        end copy_as_text


I write this because in some circumstances 0.1 sec delay would not be sufficient and script would fail to get the selected item in popup menu, in which case it may fail to preserve the selected item. I'd think 0.2 sec delay is long enough but of course 0.5 sec is safer. This sort of ambiguity is the major drawback of GUI scripting. (There's some delay between the moment of command + c keystroke and the moment of data actually being put in clipboard.)


Regards,

H

Jul 22, 2013 12:00 PM in response to Hiroto

Here're revised version of the first 2 scripts which are now to denote the selected menu item as leading tilde in text.

I know these are not directly related to the original question about sorting the menu items, but they may help to edit popup menu items in general.


* Revised script 2a will destroy the current clipborad contents in the same way as the previous script 3.


Regards,

H



# Script 1a

--paragraphs to popup.applescript
(*
    convert paragraphs to popup menu items
    v0.2
    
    v0.2 -
        added code to specify selected item by leading ~ in text.
*)
_main()
on _main()
    script o
        property mark_char : "~" -- U+007E TILDE
        property aa : {}
        property bb : {}
        
        on select_popup_item(k)
            (*
                integer k : index of popup menu item to be selected (k < 0 denotes backward index)

                virtual key codes:
                    kVK_Space        = 0x31    (49)
                    kVK_DownArrow    = 0x7D    (125)
                    kVK_UpArrow        = 0x7E    (126)
            *)
            tell application "Numbers" to activate
            tell application "System Events"
                tell process "Numbers"
                    key code 49 -- space
                    if k > 0 then
                        repeat k times
                            key code 125 -- down arrow
                        end repeat
                    else
                        repeat -k times
                            key code 126 -- up arrow
                        end repeat
                    end if
                    key code 49 -- space
                end tell
            end tell
        end select_popup_item
        
        tell application "Numbers"
            activate
            -- get selection specfiers
            set {range:_range, table:_table} to my _selection(document 1)
            -- set pop up menus of cells in selection
            if _range is missing value then return
            tell _range
                set aa to cells
                repeat with a in my aa
                    set a to a's contents
                    set bb to (a's value as string)'s paragraphs
                    set kx to count my bb
                    if kx > 1 then
                        -- get index of the first paragraph starting with mark_char
                        set k to 0
                        repeat with i from 1 to kx
                            set b to my bb's item i
                            if b starts with mark_char then
                                set k to i
                                -- remove mark_char
                                if (count b) > 1 then
                                    set my bb's item i to b's text 2 thru -1
                                else -- b = mark_char
                                    set my bb's item i to " " -- set b to space
                                end if
                                exit repeat
                            end if
                        end repeat
                        -- set popup menu items
                        set a's format to pop up menu
                        set _table's selection range to a
                        my set_popup_items(bb)
                        -- select item k in popup menu
                        if k > 0 then
                            if k > (kx + 1) div 2 then set k to k - kx - 1
                            my select_popup_item(k)
                        end if
                    end if
                end repeat
                -- restore selection
                set _table's selection range to it
            end tell
        end tell
    end script
    tell o to run
end _main

on set_popup_items(dd)
    (*
        list dd : list of pop up item's string values
    *)
    tell application "System Events"
        tell process "Numbers"
            -- bring Numbers to front
            set frontmost to true
            -- open new inspector window
            tell menu bar 1's menu bar item 9 -- view
                if menu 1's menu item 11's enabled then
                    tell menu 1's menu item 11 -- new inspector
                        click
                    end tell
                else
                    tell menu 1's menu item 10 --  inspector
                        click
                    end tell
                end if
            end tell
            -- set pop up menu items
            set c to count dd
            tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
                tell radio group 1
                    tell radio button 4 -- cell
                        click
                    end tell
                end tell
                tell group 1
                    tell group 2
                        tell scroll area 1
                            tell table 1 -- pop up menu items table
                                set k to count rows
                                if k > 0 then select row 1
                            end tell
                        end tell
                        repeat c - k times
                            click button 1 -- +
                        end repeat
                        repeat k - c times
                            click button 2 -- -
                        end repeat
                        tell scroll area 1
                            tell table 1 -- pop up menu items table
                                repeat with i from 1 to c
                                    tell row i's text field 1
                                        set focused to true
                                        set value to dd's item i
                                    end tell
                                end repeat
                            end tell
                        end tell
                    end tell
                end tell
                tell (button 1 whose subrole = "AXCloseButton") -- close
                    click
                end tell
            end tell
        end tell
    end tell
end set_popup_items

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

on bsearch(xx, x)
    (*
        list xx : source list
        anything x : item to be searched in xx
        return integer : the first index of x in xx if {x} is in xx, or 0 if not.
    *)
    script o
        property aa : xx
        local i, j, k
        if {x} is not in my aa then return 0
        set i to 1
        set j to count my aa
        repeat while j > i
            set k to (i + j) div 2
            if {x} is in my aa's items i thru k then
                set j to k
            else
                set i to k + 1
            end if
        end repeat
        return i
    end script
    tell o to run
end bsearch



# Script 2a

--popup to paragraphs.applescript
(*
    convert popup menu items to paragraphs
    v0.2
    
    v0.2 -
        added code to denote selected item by leading ~ in text.
        * this script will destroy the current clipboard contents
*)
_main()
on _main()
    script o
        property mark_char : "~" -- U+007E TILDE
        property aa : {}
        property bb : {}
        property cc : {}
        
        on copy_as_text()
            (* this will destroy the current clipboard contents *)
            tell application "Numbers" to activate
            tell application "System Events"
                tell process "Numbers"
                    keystroke "c" using {command down}
                end tell
            end tell
            delay 0.2
            the clipboard as Unicode text
        end copy_as_text
        
        tell application "Numbers"
            activate
            -- get selection specifiers
            set {range:_range, table:_table} to my _selection(document 1)
            if _range = missing value then return
            tell _range
                -- get cells and formats in 1d array
                set {aa, bb} to {cells, cells's format}
                repeat with i from 1 to count my aa
                    if my bb's item i = pop up menu then
                        set a to my aa's item i
                        set _table's selection range to a -- select cell with pop up menu
                        set v to my copy_as_text() -- get selected value
                        set cc to my get_popup_items() -- get pop up menu items
                        set k to my bsearch(cc, v) -- get index of selected value in original list
                        if k > 0 then
                            set my cc's item k to mark_char & my cc's item k -- put leading mark char in selected item
                        end if
                        set a's format to text
                        set a's value to my _join(linefeed, cc)
                    end if
                end repeat
                -- restore selection range
                set _table's selection range to it
            end tell
        end tell
    end script
    tell o to run
end _main

on get_popup_items()
    tell application "System Events"
        tell process "Numbers"
            -- bring Numbers to front
            set frontmost to true
            -- open new inspector window
            tell menu bar 1's menu bar item 9 -- view
                if menu 1's menu item 11's enabled then
                    tell menu 1's menu item 11 -- new inspector
                        click
                    end tell
                else
                    tell menu 1's menu item 10 --  inspector
                        click
                    end tell
                end if
            end tell
            -- get pop up menu items
            tell (window 1 whose subrole = "AXFloatingWindow") -- front inspector window
                tell radio group 1
                    tell radio button 4 -- cell
                        click
                    end tell
                end tell
                tell group 1
                    tell group 2
                        tell scroll area 1
                            tell table 1 -- pop up menu items table
                                set pp to rows's text field 1's value
                            end tell
                        end tell
                    end tell
                end tell
                tell (button 1 whose subrole = "AXCloseButton") -- close
                    click
                end tell
            end tell
        end tell
    end tell
    return pp
end get_popup_items

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 _join(d, tt)
    local astid, astid0, t
    set astid to a reference to AppleScript's text item delimiters
    try
        set {astid0, astid's contents} to {astid's contents, {d}}
        set t to "" & tt
        set astid's contents to astid0
    on error errs number errn
        set astid's contents to astid0
        error errs number errn
    end try
    return t
end _join

on bsearch(xx, x)
    (*
        list xx : source list
        anything x : item to be searched in xx
        return integer : the first index of x in xx if {x} is in xx, or 0 if not.
    *)
    script o
        property aa : xx
        local i, j, k
        if {x} is not in my aa then return 0
        set i to 1
        set j to count my aa
        repeat while j > i
            set k to (i + j) div 2
            if {x} is in my aa's items i thru k then
                set j to k
            else
                set i to k + 1
            end if
        end repeat
        return i
    end script
    tell o to run
end bsearch

Alphabetize pop-up menu

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