Populating a Keynote Presentation From a Numbers Spreadsheet Using JavaScript

You can use the Script Editor app on your Mac to create JavaScript programs that control other apps. In this example, learn how to take data from a Numbers spreadsheet and use it to add new slides to a Keynote presentation. While it takes some programming know-how to build automation tasks like this, it is good to know what is possible.

Video Transcript
Here's a super advanced tutorial that involves programming. Say you had a presentation you wanted to create and the data for the presentation was in a spreadsheet.

So here I have a Numbers document with one sheet and one table in it. Simple as can be Numbers document. It's got a list here of all the states and the state flower. I also have the scientific name but I'm not going to use that here. I want to create a presentation that has one slide each for the state and its flower. So I have fifty slides in the presentation. It could be used for various different things. It doesn't necessarily have to be this topic but you get the idea. This could be a lot longer text, whatever. I don't to have to create fifty different slides and copy and paste all this data across. So how can I do it using programming.

Well, I can using the ScriptEditor, which is an app that comes with your Mac, and some Java Script programming. Now ScriptEditor, in the past, has been used for AppleScript and you can switch between AppleScript and JavaScript. Java Script is much more modern and advanced programming language. A lot more people know and I use it for lots of things so I try to stick with Java Script when I can. It turns out it's a lot easier to do this task in Java Script.

I wrote this very short little script here that will do exactly this. Let me go through it with you.
So the first two lines are basically just going to declare the term Numbers with a capital N, as the Application numbers and Keynote with a capital K as the Application keynote. Just shortens the typing for the rest here.
Then I'm going to create something called table. Table is going to point to, in Numbers, the first document, the first sheet, and the first table. Notice these zeros in here. Java Script is a zero based array language so zero is first, one is second, two is third. So first document, first sheet, first table. In other words this table right here.

Now, presentation is going to be the first document in Keynote which is this one. I only have one document opened in each one here so it avoids confusion. Then I'm going to create a variable called slideMaster and that's going to point to presentation.masterSlides. Something called Title and Subtitle. Where does this come from? Well if I go to View, Edit, Masterslide in Keynote you can see here are all the masterslides and the first one is called Title ampersand subtitle. So I'm using exactly the name of that slide in the master slide there to basically give it this variable here. So as I create new slides I'm going to create them using this slide template right there. You could have chosen one of the other ones and you can certainly go ahead and edit the title and subtitle one as well to customize it's field which you need before you run this.

Alright, so here we go. So this is where it's handy to do JavaScript because you can do just normal four loops here which if you've ever done JavaScript programming is standard. It's going to loop starting at one through to the number of rows so table.rowCount. Number of rows in the table. I++ means it's going to increase go from one to two, two to three, etc until it gets to the end of the table. Now I said before it was zero based here so shouldn't it be starting at zero? Well, there's a header row here that says State, Name, Scientific Name. So I don't want to include that one so I'm starting at one, I'm starting at the second row there.

Okay, so I'm going to create a newSlide = Keynote.Slide and I'm feeding it the information that baseSlide is going to be slideMaster which is this up here. So in other words I'm going to create a new slide based on this slide master template. Then I'm going to do presentation.slides.push new slide. So I'm going to add the new slide. Then I'm going to set something called the default title item of that slide to the table.rows i so row two will be the first one here. cells zero. So the first column at its value. Default body item, instead of default title item, to the same thing except cell 1 instead of cell zero. In other words the second column there.

So default title item and default body item are these fixed things inside of Keynote that Java Script knows to look for. So nothing I created. It's just for all those slides there. They either have a default title item or a default title item and default body item. So that's just something that's just part of how Keynote works and how it's hardwired with these two special things.

So it's going to loop through there, create a new slide and populate it with each one. So let's try it. I'm going to run it and we'll watch here and we'll see the slides appear. There they all are. It goes to fifty-one because I have that first slide here that was blank that was already sitting there. You can see Alabama, Alaska, etc.

Just to give you can example of another way this can be useful I'm going to select them all here and delete them and do it again except I'm going to copy and paste, let me increase the font size again for you so you can see it. So I'm going to copy and paste, basically going to do this twice. I'm going to create a slide and put the state name of the flower; create a slide and put the state name of the flower but for the first one I'm going to remove the flower. So it's only going to create a slide putting the state name there and then another slide with the state name of the flower. What happens when I do that? Well, I'm going to get a hundred slides instead of fifty and the first one is just going to be the name of the state and this one is going to be empty. Then then name of the state and then the flower. So when I play the presentation it's kind of like a quiz. You can ask your class, if it's a school, what's the state flower of Alabama or what's the state capitol or anything and the next slide has the answer. Alaska and the next slide has the answer.

So, that's the basics of how to do it. You can imagine where you can go from here. You can randomize these here and have a random order to them. You can include more text here for more detailed stuff. You can basically create a nice template here that has a beautiful slide in it and then populate it with different things for different presentations or with new numbers. So say if you run an organization with thirty different things with thirty different statistical numbers to them and you want to redo this every month you can populate using this and those thirty different numbers go into thirty different slides very easily. So lots of cool stuff you can do and the basics here are just, you know this, just learning how to program some Java Script to take information from Numbers and put it into Keynote.

Here’s the text of the script:

var Numbers = Application("Numbers");
var Keynote = Application("Keynote");

var table = Numbers.documents[0].sheets[0].tables[0];
var presentation = Keynote.documents[0];
var slideMaster = presentation.masterSlides["Title & Subtitle"];

for(var i=1;i<table.rowCount();i++) {
    var newSlide = Keynote.Slide({baseSlide:slideMaster});
    presentation.slides.push(newSlide);
    newSlide.defaultTitleItem().objectText = table.rows[i].cells[0].value();
	
    var newSlide = Keynote.Slide({baseSlide:slideMaster});
    presentation.slides.push(newSlide);
    newSlide.defaultTitleItem().objectText = table.rows[i].cells[0].value();
    newSlide.defaultBodyItem().objectText = table.rows[i].cells[1].value();
}

Comments: 8 Responses to “Populating a Keynote Presentation From a Numbers Spreadsheet Using JavaScript”

    nick
    3/2/17 @ 11:33 am

    very slick Gary, looking forward to more programming examples, great way to learn Java !

    Leah
    3/2/17 @ 1:18 pm

    This is super. I’m also very interested in writing little scripts for the Mac. Thanks!
    (Club MacMost patron ;)

    Eric
    3/5/17 @ 8:58 am

    Nice one. Could you please make the script available as a starting point for experimentation?

    3/5/17 @ 11:51 am

    Eric: I’m put the script under the video above for those that wish to copy and paste.

    John Luke
    3/22/17 @ 5:16 am

    This script was a great help! Do you know how to add another text box and fill it the same way as defaultTitleText and defaultBodyText??
    Additionally, do you know how I could use a lookup script to update these text boxes? (example would be updating prices in a product portfolio).
    Thanks again!

    3/22/17 @ 6:29 am

    John: Looks like we only have those two existing text boxes to work with in scripts. The other idea may be possible, but I can’t think of how to do it offhand. You’d need to start experimenting and work at it to figure to out.

    Dorit
    4/7/17 @ 7:14 am

    Trying to get from the “fish”to the “fishing”: Is there some documentation (presumably by Apple) to find out about those pre-defined capabilities of Numbers and Keynote? I. e. how do I find out that it’s “presentation.masterslide” or “newSlide.defaultTitleItem().objectText()”?

    4/7/17 @ 7:39 am

    Dorit: There is documentation in the Script Editor app. Use Window, Library to open the topic list. Then dig into a topic. But remember to switch from AppleScript to JavaScript at the top of the documentation window. The information here is just a reference and is sorely locking in examples.

Comments Closed.