Using AppleScript To Automate In Numbers

The latest version of Numbers restores our ability to use AppleScript to automate some actions in your spreadsheets. You can use AppleScript to create new commands in Numbers and do things that could be difficult or impossible to do otherwise. Take a look at some simple examples that populate cells with random numbers and modify the values of checkboxes.

Video Transcript: Hi, this is Gary with MacMost.com. Today let's look at using AppleScript with Numbers.
So, Numbers has had AppleScript support for a long time but when the new version came out recently it was missing from it. Then an update added it back. So there has been a lot of interest in using AppleScript to manipulate cells and things in Numbers.
Here I am using the latest version of Numbers, version 3.2, and I'm going to use AppleScript to modify some things in some cells.
To write AppleScript I'm going to use Automator. I can use the AppleScript editor and that is probably a good way to start and experiment with your scripts. 
But it is easy to create a Service which then can be accessed inside of Numbers by selecting a New Document in Automator using Service, choosing it and then searching for the Run AppleScript action, putting it in there, and just having that. We're going to have No Input from the application and we can actually go and set this to be only a Numbers service. There. Now all I need to do is write some AppleScript and put it right in here.
So I have pasted in a simple script here. Let me show you how it works. Basically you have to say well I want to you tell the application Numbers to come to the front and activate. Then I'm going to specify that I am going to go to the first table in the active sheet of the first document, Document 1. 
Now there is no error checking in these simple scripts. A complex AppleScript would have all sorts of things that would make sure that there is a table, maybe use a table, select a table, and all that. I'm just going to do things very simply here and assume that is all set up and everything is good. The only command I'm actually going to give is to set the value of a certain cell, C4, to 42.
I'm going to run this and test it out and you can see right away it works right there. C4 is set to 42.
You can do a lot of other basic things. For instance instead of setting it to a specific number, like 42, I can choose a random number from 1 to a 100 or any values there. This is useful because in Numbers you can use the rand function or the random between function but those will put random numbers that change all the time. 
Say you wanted to have a bunch of sample numbers populated and you didn't want them to change after you initially put them in there this would do that. They would just be in there as if you had typed them. 
Also here I am going to set another cell's background color to a red, green, blue value here. This will make it all red. When I run this you can see it put a random number in there and it put red in there. The cool thing about random number is I can continue to run it and you can see it will always do a different number.
If I wanted to do a range of cells rather than a specific one I can use this. The background color of the range and give it a range like this, B4 to C5. Now when I run that you can see it sets those right there instead of just the one. So I can do all sorts of different ranges like that. 
You can also, instead of using cell or range, you can use row and column. Say column C or row 5.
Now another way to use AppleScript is with check boxes and other elements like it. The checkbox in Numbers you can't change the value of this with a formula. It is something that you actually have to take an action to do. You can check it or you can uncheck it. But suppose I wanted this to be checked if the value of the cell next to it was greater than 50. So I can do that using AppleScript.
As a matter of fact I'm going to, in my new script here, set that first cell, C4, to a random value between 1 and 100.  Then the second cell I'm going to set to either true or false based on whether or not the value of the cell is greater than 50. So let's give that a try. I'm going to run it and it's going to put a random number in there, 47, and it is not checked. If I run it again it's going to put another random value, 93, and you see it is checked.
Let's go a little further with that. I put checked boxes in column D here and column C is empty. I've got a new script here that is actually going to repeat over a range of cells starting with 2 and going to all of the cells in column C. It is going to set the first one, the cell in column C to a random value between 1 and 100 and then in column D it is going to use the same check there to see if it is greater than 50 and check the box if it is. So when I run this you can see it populates it with random numbers on the left and checks off the appropriate box on the right.
Now it doesn't have to be random numbers. I could simply have these be something you fill in and then have the script be simply something that simply checks or unchecks these boxes accordingly.
Now let's take a look at how we turn this into a service. I've created another script here that is basically the same thing but it's going to repeat over all the cells in the selection range and it is going to set each of them to a random number. So if I run this, but first let me go into Numbers here and I'll select these six cells. Then I'll go back into Automator and it will then run it. You can see it is going to do that.
Say I want to turn this into something where I can easily access without having Automator run. I've already set this up as a Service. It is untitled. I'm going to hit Save and I'm just going to call it Test 1. It will save it as a work flow. Now I've said it before that it is going to use Numbers app and save it as a Service. 
So now if I go to Numbers and I go to Services I should see it appear right there. Test 1. If I select it at anytime it will populate the selected range like this. There you go and I don't have to have Automator running to do that. The cool thing is that under the Services menu here I can actually assign a keyboard shortcut to this in System Preferences to that specific menu item in Numbers Test 1 so I can make actually a command to do things very easily for me like put a random number in there, or put today's date in there. Anything that AppleScript can do.
So if you are interested in finding out what you can do in Numbers with AppleScript well it is changing all the time as Apple adds more functionality with updates but here is a handy website that has a lot of information. Of course you can use the AppleScript editor. Kind of look through all of the AppleScript documentation and just search online for all sorts of other scripts and things that you can do. AppleScript is a very frustrating language even if you are an experienced programmer so if you are not an experienced programmer don't expect to be able to do too much. But perhaps maybe by looking at some information online you can put together some things that help you get your work done.

Comments: 4 Responses to “Using AppleScript To Automate In Numbers”

Nilesh Parmar

9 years ago

Love this, would like to how you would a script from numbers to convert the active sheet to cvs with having to go to export. Or better yet, the AppleScript to do it directly from desktop.

Gary Rosenzweig

You can use Automator to simulate a sequence of menu items commands and mouse clicks. So use that to create a service for Numbers that does the steps you need.

Relever

Anyone seen/ developed an apple script to replicate the "Goal Seek" function in Excel?

As far as I can tell there has never been a Goal Seek function in Numbers, in any version. I suppose you could write a script to do it, but that would take some know-how and a lot of work. Probably more cost effective to just get Excel since it has it. Right tool for the right job situation.

Comments Closed.

Using AppleScript To Automate In Numbers

Comments: 4 Responses to “Using AppleScript To Automate In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information