For the last couple of months, one of my high school classes at Jewish Day School has been working on building an interactive tool about the Six Day War for a middle school curriculum unit. They have put a lot of work into researching their data and laying it out in Google Earth, and now we’re putting together a tour of the data for the middle school students, that will also teach them a little about how to use Google Earth. It’s been enormously fun.

But. But, we now have to do some programming or some very careful recording in Google Earth to create this tour (a là Al Gore’s Climate Change tours). And my students are far more interested in the design end of things than in the coding end of things. They’re great at what they do, but niggling coding details give them head aches.

So I built a Google Docs spreadsheet, into which they can plug various pertinent information and out of which comes valid KML instructions that will define the tour. I’m a little proud of this — I don’t think that there’s anything else quite like it out there. There are three kinds of information that need to be entered:

  1. SoundCue — any audio that should be played in the background. You can either enter the whole URL to the MP3 file that should be played, or just the file name (and make sure that you enter the path in which all the files can be found on the KML Generator tab). You also need to tell us both when the cue should be played and how long it should last.
  2. AnimatedUpdate — anything that should be happening in Google Earth in terms of showing photo overlays or placemarks or polygons or what-have-you. Enter what should happen, and when (and, if relevant, for how long).
  3. FlyTo — any time we change where (or when) we’re looking at Google Earth. Enter when and how long the transition should take, and the date (and time) that we’re meant to be looking at.

The AnimatedUpdate and the FlyTo also expect the user to enter KML code — which can be copy-and-pasted from Google Earth KML exports for each transition. Happily, if something needs to happen more than once, entering the KML for the first instance will automatically populate future instances. In addition, the pauses necessary to synch up all of the action are calculated by the spreadsheet.

Update March 25, 2011: The spreadsheet above is our live work (since I kept updating the spreadsheet after this post).

The end result is both a visual timeline of the tour (helpful for debugging any weird errors) and also KML code that can be copy-and-pasted out of Google Docs and into a KML file. (Caveat emptor: depending on what you’re pasting the KML into — I like Xmplify — you may see that there is a leading and trailing quote that need to be manually deleted.)

Right now the spreadsheet can handle tours up to four minutes and 10 seconds in length (250 seconds, for those of you keeping score at home). This is because I was originally copying the KML out of the KML Orderer worksheet, and Google Docs supports pasting of up to 1000 rows in total. You’ll see that the current KML worksheet is a single cell, getting around this limitation, but I didn’t bother to extend any of the other worksheets. Just make sure you fill down all the formulas if you extend any of the sheets!

Here’s a link to a scratch copy of the spreadsheet. Feel free to copy and use it for your own purposes — let me know how you used it!