Creating Dynamic Pop-Up Menus In Numbers With AppleScript

While it isn't possible to create dynamic Pop-Up Menus in Numbers alone, you can use an AppleScript service to pull data from one table, present it in a list that will then populate the value of a cell. You can set this service up with a keyboard shortcut to make data entry easier. The list will automatically update as you update the second table.

You can also watch this video at YouTube (but with ads).

Video Transcript: So in my previous video I showed you how to get pop-up menus in Numbers to actually work with a list in another table. It takes some work. Every time you update you have to, kind of, redo the pop-up menus. So isn't there a way to do it truly dynamically where it's going to pull from this list no matter what's in it. The answer is yes, there is a way to do it but it requires you to use Automator and AppleScript and it's kind of buggy and it kind of works. I'm going to show you how and maybe it's something you can build off of especially if you're good with AppleScripts.
I have my Table 2 here. It's got these items in it. I've got Table 1 where I want to put stuff and here's what I've done in AppleScript. I've actually started a new Automator task. I've created a new workflow and I've set it as a Service. The Service receives no input and it's used in Numbers only.  Then I added one thing to it, run AppleScript. So I'm basically using Automator as a way to compose AppleScript and set this up as a Service. So what's going to happen.
Well, so the real AppleScript is right in here, the on run that's  kind of added in there by Automator. It's going to tell Application Numbers and tell Table 2 of sheet 1 of document 1. What is that? Well, that is this Table 2 here. So the items that I want to get input into this list. Very clunky to have it always look at Table 2 of sheet 1 of document 1. You might need to adjust that. Probably do some clever AppleScript to maybe get the table by name or some other way. 
Then it's going to create an options list. It's going to start counting at number 2. why?  Because the first item here is just called item so I want to start in row 2.  It's going to repeat while n is less than the number of cells in that column. So in other words repeat from 2 all the way to 12. It's going to grab the value of that cell. Store it in something called option. Then it's going to add that to the options list so options to options and option. In other words the current list plus the new item. It's going to increase n by 1 and it's going to repeat here until it gets all of them. Then it's done with that part. So it calculated this option's variable with a list of items.
Now it's going to tell the front document's active sheet to get the first table whose selection range's class is range. That's a really weird way in AppleScrip of figuring out of what cell is currently selected. Basically saying what table has a range in it, a range of cells. The only one that is going to have one is the one where you have selected a cell. Then tell that selection range to set the variable c to that first cell. So that first cell is going to be c. 
Set a variable called mychoice to choose from list options with prompt please choose default items None. So it's going to pop-up a little list of all the stuff we collected here. It's going to set the value of that cell to that. That's what it does. So Save that and save it as a Service that works in Numbers that should appear in Numbers, Services and we should see it here. Choose From List. There it is.
So I'm going to select a cell here and I'm going to say Numbers, Services, Choose from List. Now notice that I can set a keyboard shortcut for that. So you do that in System Preferences, Keyboard, Shortcuts, and then in Services I can set the keyboard shortcut so I don't have to go through this whole thing of Numbers, Services, Choose from List. It's going to pop-up a list. There it is. Please choose and notice it has all the items that are here. I'll choose one and hit Okay and it populates that.
Is it dynamic? Yes. So I'll add something else here. Let's just add Other to the list and I'll go here and say Choose from List and notice Other now is at the bottom of the list. So it will add items. It will update as you change items here. The keyboard shortcut would make this really easy to just hit the keyboard shortcut and click on an item. You can even, I think, use the keyboard here. So yep I can use the keyboard, down arrow, okay. So with the keyboard shortcut it makes it real easy to enter items and do that. All using AppleScript. 
I'll include the script that you can Copy and Paste into Automator in the description for this video at the MacMost site.

Here is the AppleScript code used in the tutorial.

on run {input, parameters}
	tell application "Numbers"
		tell table 2 of sheet 1 of document 1
			set options to {}
			set n to 2
			repeat while (n ≤ the number of cells of column 1)
				set option to the value of cell n of column 1
				set options to options & option
				set n to n + 1
			end repeat
		end tell
		tell front document's active sheet
			tell (first table whose selection range's class is range)
				tell selection range
					set c to first cell
					set mychoice to (choose from list options with prompt "Please choose" default items "None")
					set the value of c to mychoice
				end tell
			end tell
		end tell
	end tell
	
	return input
end run

Comments: 4 Responses to “Creating Dynamic Pop-Up Menus In Numbers With AppleScript”

Ian

6 years ago

So I followed the instructions in your last video, which worked well for me.

I have re-named the tables in my Numbers document, so in your script, would I have to use my table name, or work out which table number to refer to?

Thanks for a great series of videos, I’ve been watching for years.

Gary Rosenzweig

Ian: Try them. It may work with both. To code and alter code like this you have to experiment. So try it each way.

Hi Gary,

I worked it out, thanks for all your help 👍

Mark

Hi Gary,
I have a Numbers document with numerous "sheets", and "tables" which need contact names entered in order to populate cells. The Script you provided enables the use of one dynamic name list that can be accessed by multiple sheets and tables throughout the document without having to use and change the "Pop Up" menu on each table.
Thanks!

Comments Closed.

Creating Dynamic Pop-Up Menus In Numbers With AppleScript

Comments: 4 Responses to “Creating Dynamic Pop-Up Menus In Numbers With AppleScript”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information