Timeline Maker 3, Beautiful Timelines Through a Google Form

I promised the good folks who attended my session at ISTE (thanks for coming!) that I would update the Timeline Maker system for the newest version of Timeline JS. I wanted this version to be as accessible as possible so I built in a control panel to guide teachers through the setup process. This system uses a Google Form to create a beautiful interactive and embeddable timeline.

Timeline JS is a beautiful tool that transforms a Google Sheet into an interactive multimedia timeline. This system streamlines the process by using a Google Form to create the timeline. It also uses the incredible sheetSpider script, programmed by Andrew Stillman from New Visions for Public Schools. SheetSpider will create multiple timelines from the same form, making the system function for group work or individual students.

Set Up

First make a copy of the Timeline Maker. This was built for schools and assumes that you are copying it into an Edu Domain. As such, it has been developed to work with recorded usernames. This can be used outside of a domain, but will require some minor tinkering.

I really want to make this system as easy to use as possible. The sheetSpider script is powerful, but I think it can be confusing, especially if you want to control how it works. I created a “Set Up” sheet that takes teachers through the process step by step. Via this sheet, the system can be set up to create one timeline for the class, for small groups, or for each student. The “Set Up” sheet also allows teachers to turn on moderation, allowing submissions to be vetted before they get to the timeline. I’ve linked to two example sheets below, one set-up for small groups and another set up for a whole class. The data came from the good folks who attended my ISTE presentation.

Whole Class Timeline From Example

What’s next?

I didn’t involve any scripts or add-on beyond sheetSpider in the effort to keep this as simple as possible. In future iterations I hope to do the following:

• Install formMule and record the edit form link
• Have the system email the student if something needs editing (via formMule)
• Have formRanger update a Student Name question with each submission
• Tie it into the Twitter Google Form system so that exit tweets are recorded in a timeline.

The time was right for this revision. The goal was to build a system for teachers who might not be Google Sheet ninjas yet, but still want Timeline JS goodness powered by a Google Form. This is the first time I’ve tried to approach a system this way, please let me know if it works for you!

Edit: Giant thanks to Dan Crowley and Kelly Kermode for their help chasing down some bugs. If you experience any trouble, please let me know in the comments. (7/28/2015)

Edit 2: Fixing old bugs made new bugs! Thanks to the help of Stephanie Schroeder I was able to debug them. Once again - if something doesn't seem right, leave a comment and let me know. Thanks! (7/30/2015)

Student Timelines from a Google Form using sheetSpider

An update for this project has been posted here.

The goal of this project is to give students a personal timeline of the wonderings and observations that they make using a Google Form. Then, this slick timeline can be embedded in a student portfolio. This project can be integrated into the moderated Twitter exit ticket that I blogged about a few weeks ago, or it can stand alone.

Here is what you need:

• A simple Google Form linked to an “old” spreadsheet that required students to sign into your domain.
• The SheetSpider Script from New Visions for Public Schools (thus the “old” spreadsheet)
• An affinity for nice looking timelines

I can’t wait to implement this in my school, and hopefully build it into some student portfolios. One word of caution: this system needs the student satellite sheet to be published to the web in order for it to be read by the Timeline application. The next section explains how to do this yourself, step by step. There currently isn’t a moderation component although one could be built in, similar to the one used for the Twitter system.

So How Does This Work?

SheetSpider is a nifty script that lets you distribute values to multiple sheets based on a criteria - in this case the criteria is the student Google ID.  Based on this ID, sheetSpider will send each student’s exit ticket responses to a unique Sheet for that student. SheetSpider can be set to use a template when making the satellite sheets and we will use the template file from Timeline JS. If you haven’t seen this Timeline creator, you should.  It does a beautiful job.

Make a copy of the Timeline template and then use the “copy to” feature to add the sheet to your “Exit Ticket” sheet file. The timeline sheet has more columns than we need for our simple exit ticket form, but we need the whole template when we copy the student submissions to the satellite sheets. Specifically, we want to send the form response timestamp to the “Start Date” column, the exit tweet column to the “Headline” column, and the username column to "Media Credit." A simple function like the one below can move the data from tab to tab, you'll see it at the top of the previously mentioned columns in the demo sheet.

= ARRAYFORMULA ( If ( 'Form Responses 1'!C2:C = "", "", 'Form Responses 1'!C2:C ) )

Now it’s time to install the sheetSpider script. Keep in mind that as of this writing, sheetSpider is only available with an  "old" Google Sheet. You must click "tools" > "script gallery" and search for "sheetSpider." A tab named “SpiderSheetEntities” will be created when you install and initialize the script.  Then, click the sheetSpider menu item and proceed to “Step 1: Set Up Entity Sheet.” 

We have to set the script to run on “Manual Push” because we are using the copied timeline sheet to trigger sheetSpider instead of the form responses sheet. All of the items in Step 1 can match to the columns in the “SpiderSheetEntities” tab that was already created. You might want to create a folder for your satellite timeline sheets and paste the folder ID into the “Key of Primary Folder…” field.

Now proceed to “Step 2: Provision/Update…” In the first field, paste the document Key of the Timeline JS template. The feeder sheet needs to be set to “od1” which is the tab that we copied over from the template. The unique entity name can be set to “Media Credit” which is where we fed in the student usernames. At this point, click the “Save and Provision…” button.

“Step 3: Disagregate and Push Data” is the last sheetSpider step we need. Make sure your settings are “Manual Push” and “Append only new unique records.” The uniqueness criterion can be both “Start Date” and “Media Credit” to ensure uniqueness. Running Step 3 will create the satellite sheets. You will need to publish those sheets for the timelines to work.

Now lets connect the timelines.  Open the “SpiderSheetEntities” tab and insert two columns to the right of Column F. Name these “Timeline Embed” and “Timeline Link.”  Then, add one column to the right of “J” and name it “Spreadsheet Key.”

Paste the following function into K2:

=ARRAYFORMULA(right (I2:I,23))

Paste the following function in G2:

= ARRAYFORMULA ( IF (A2:A = "", "" , "<iframe src='http://cdn.knightlab.com/libs/timeline/latest/embed/index.html?source=" & K2:K & "#gid&font=Bevan-PotanoSans&maptype=toner&lang=en&height=350' width='100%' height='350' frameborder='0'></iframe>"))

Then, paste the following function in H2:

= ARRAYFORMULA ( IF (A2:A = "", "" , "http://cdn.knightlab.com/libs/timeline/latest/embed/index.html?source=" & J2:J & "&font=Bevan-PotanoSans&maptype=toner&lang=en&height=650"))

These last functions complete the project.  Student submitted exit tickets are aggregated to personal spreadsheets, and once the spreadsheets are published, they are used to create embeddable timelines. The embed code works great but not in a Google Site. Use the "Timeline Link" and the iframe gadget if you are using a Google Site. Let me know if you use the project or make it better!