MacMost Now 925: Using AppleScript With iWork Apps

You can use AppleScript to create scripts that control Numbers and Pages. This is similar to how Microsoft Office users use macros to control Word and Excel. In either case, you need some level of programming skill to be able to write scripts. If you can do it, this can be a very powerful way to enhance Numbers and Pages, adding functionality that isn't possible otherwise.

Video Transcript
Hi this is Gary with MacMost Now. On today's episode let's look at using AppleScript with iWork.

One difference there seems to be between Microsoft's Office apps and Apple's Office apps is that you can script the Microsoft ones. You can use Visual Basic to create macros in Word and Excel to create scripts. It doesn't seem like you can do that with Pages and Numbers.

However you can. You can use AppleScript, a system that has been there almost forever, and script Pages and Numbers. You just need to know how to set it up.

Now this isn't something that the typical user would do. You need some sort of programming experience to be able to write things that can interact with Numbers and Pages. This isn't as easy as using Automator.

Automator is kind of like an interface on top of Applescript allowing you to make very simple things that you can do with different applications and the operating system.

AppleScript is what is kind of underneath it. You have to actually write code to program it. You can write code that specifically addresses both Pages and Numbers. Let me show you how.

So I'm going to run the AppleScript Editor app. You can find this in your Applications Utilities folder. This allows you to run AppleScript code. Now typically if you are going to write code that does something with an application you would go to the Library and under Library you would look for that application. For instance if you wanted to write something that controlled DVD player, there it is. You can select that and look at the different AppleScript commands that affect that.

You notice here that there is no mention of either Pages or Numbers. But it is easy to add that. The way you do that is to go into your Applications folder and you look for an app. In this case I'm going to look in my Applications folder under a folder I've got called iWork 09 and in there I've got the three iWork apps. If I wanted to add the command for Numbers I simply drag and drop into that Library window. Look what happens! It adds Numbers there.

This would only happen if the app has some sort of built in functionality where it interfaces with AppleScript. If it doesn't this isn't going to do anything. Both Numbers and Pages have that ability.

So now let's take a look at what we've got. Here is Numbers for instance. I'm going to double click on that and it is going to bring up the list of commands that has to do with Numbers.

So here we go. We've got this list of commands here. It has also launched Numbers for me in the background. I can now look through and there is the Standard Suite of commands and stuff for doing text and the stuff for doing Numbers app itself. You can see I've got commands right here like add column after, add row before, clear, make to create a new object, remove, sort, split into rows, things to address cells, columns, ranges, tons of stuff that has to do with dealing with spreadsheets.

Let's look at a simple example. First in Numbers I'm going to open up a new spreadsheet. So I've got that and then I'm going to hide Numbers. Now I'm going to, inside of AppleScript, I'm going to set it up so it can communicate. The typical thing to do is right here. Not I'm not going to make this very elegant script. I'm just going to do some basic stuff here.

So I'm going to tell the application Numbers and I'm going to have it go to Table 1 of Sheet 1 of document 1. Now you can use names here instead of numbers, you can also use more complex AppleScript to address the current table and the current sheet, and things like that. But this is the basic setup if I just open up a blank Numbers document and I want to do something with it.

Then I've got a space here where I can put whatever code I want. So the code I've put in here is set the value of cell D5 to 7. Something very simple. I'm just going to set the value of a cell. Notice that AppleScript if very picky. That is why it is very important that you have some programming experience to deal with this. You know, just knowing when to put "the" in front of values here and how to format things and how to write things out. You can do things that look very much like this and they just won't work because you haven't put quite the right command and functions in there.

So if I look at Numbers now you can see it is blank. If I go over to this AppleScript and I hit run and I switch back I can see it has actually put the 7 there in that cell.

So now let's do something a little more useful. You can see this is blank here. I'm going to use date string of current date rather than the number 7. You notice that this is very specific syntax and if I did something a little bit different here it wouldn't work. So I'm going to hit run and sure enough I get the current date put into that cell. That is real value there. Basically I've done the same thing as if inside of Numbers I did insert and then Date & Time.

The difference being that I am actually controlling this. So I can write more complex AppleScript here to do something like say put tomorrow's date or yesterday's date. Or have something in place to put just say the first of this month or something like that. So I can do lots of different things here rather than just having to default to only inserting the current date like you can in Numbers.

I can save this AppleScript out and turn it into a service. So you can then go to Numbers/Services and access the script from here and assign keyboard shortcuts to it even, so you can basically add functionality to Numbers by creating scripts, saving them out to Services, and then adding them into Numbers here where you can perform a variety of functions.

Of course you saw how inside of the Library here there were ways to also get the value of cells and do certain things with cells and certain things, move things around. There is a lot that you can do if you are a programmer and you know how to work with code.

Now perhaps the easiest way to take an AppleScript and turn it into a service is to use Automator. In Automator you can create a new document, create a service, and then set it to work with any application you want. In addition to that you can add Run AppleScript as an Automator action here and then in this space you can put your AppleScript. Put any AppleScript you want in there. So you can develop it in the AppleScript Editor and then you can go ahead and add it in here replacing this part with whatever the script is and then save it out and then you can have a service for Numbers or Pages or any application that is basically in AppleScript.

You can actually edit AppleScript just fine inside of Automator here as well. Just the AppleScript Editor is very useful for editing scripts because you know you can see all of this Library stuff. You can develop things and certainly there is no problem with developing it here and then moving it to Automator and into a service later on.

I also want to point out here that you've got Pages. We dragged and dropped Pages into the Library here as well so now we will be able to look at what is available for Pages. You can see here we've got Pages Application Suite. You can add tables and charts and do all sorts of things here with the document and document properties. Tons of stuff you can do in Pages to script it. So you can really go pretty wild here on creating interesting applications inside of Applications to do things for you.

Again you've got to be a programmer really to work with this. But even if you are not a programmer, if you know one or you can hire them, you can have them basically modify Numbers and Pages to get it to do things that most people don't think are possible with those apps because they are not looking here at the ability to script it.

As a programmer I have to say that I find AppleScript very frustrating. It is an extremely unforgiving language. You need to get things just right for it to work or it just won't work at all. But what is very useful is to simply search, it is easy to search Goggle for AppleScript one word, and then something you are looking for. So if you are looking for how to you know do some manipulation inside of Numbers or to work with some variables or loops or anything inside of AppleScript you can just search for AppleScript and then describe what it is you are looking for and it usually comes up with examples. It is through those examples that you are usually able to find the correct syntax.

I hope you found this useful. Until next time this is Gary with MacMost Now.

Comments: 4 Responses to “MacMost Now 925: Using AppleScript With iWork Apps”

    Keith Flanagan
    10/2/13 @ 10:46 am

    Gary,
    That was probably one of the most interesting posts I have seen in a long time. Back when I was with the “Dark Side” I did a lot of VBA programming for Excel and it is nice to see that I can accomplish similar things with Apple Script. Great Tutorial!

    Stephen Bamforth
    10/6/13 @ 6:23 am

    Gary,

    very helpful Tutorial as I am a convert to MAC and want to get to the nuts and bolt of scripting on the system.
    This has encouraged me to explore further.

    Best wishes from the UK

    Stephen Bamforth

    George Somers's
    11/2/13 @ 4:06 pm

    Can you use Automator with the new version of Pages and Numbers? I thought Andy Ihnatko said on a recent Macbreak that it didn’t. Did I misunderstand him? Thanks.

      11/2/13 @ 4:25 pm

      This video was made before Mavericks. Check the date. Haven’t looked to see what is available linking Automator and the new versions of the iWork apps. Check and report back.

Comments Closed.