Monday, December 16, 2013

The Google Site FounderBook

My fifth grade classes are studying the Founders and the Constitutional Convention.  There are lots of fun ways to redefine the traditional essay project with this rich subject matter. I really appreciate the work that many have done using social media parodies, like these examples of Historical Fakebook projects.  I wanted to leverage these ideas, too, but using Google Apps - Sites in particular.  

I wanted to build a Google Site that capitalized on the efforts of the class by building social interaction into the project.  What's the point of building a FounderBook page if the founder can't interact with his contemporaries?  

So, this is my first attempt.  It's a good start, but I'm sure I've missed an opportunity or two. This template allows students to build a profile for a Founder, blog from his perspective, and perhaps leave comments on the pages of the other founders. Announcement pages and page templates make Sites well suited for this task.  I built two convention pages to accommodate a class of twenty-plus students.  There is a founder template page ready if you need to add personalities to the list.  The respective convention home pages feature announcement gadgets to display the running blogs for each founder.  

If you're interested in using the template with your class, simply follow the directions below:

1. Create your site by clicking “Use this Template” on the top of the template linked here:

2. Edit the Homepage of your FounderBook by clicking the pencil icon on the top right.

3. Assign Founders to the students in your class by replacing the placeholder names in “Convention 1” and “Convention 2”

4. Click the blue “Share” button and give the students in your class edit access to the site. 

5. This site will now appear on the Students' Sites pages, located at You can also copy and paste the site address into a Google Doc that is shared with your class.

6. The "Instructions Page" assumes that your class has already done research on their Founders and gives a step by step guide to complete the project.

I have several classes using the template and it's going well so far.  Let me know if you improve on the template and/or try it out!

Sunday, December 1, 2013

Google Site Sharing Repository, Part 2: Creating HTML to link formFolio to Google Sites

It’s taken me a few weeks, but I’ve finally finished the Google Site Sharing repository.  Well, finished might be going too far - I’m in “trusted tester” mode.  This is basically how it works: using a Google form outfitted with +Andrew Stillman's formFolio, a teacher in our district’s domain can submit a resource and organize it with keywords. The Google Spreadsheet uses the information from the form to create a Sites page via the createWebPage method. Next, the pushData script exports the catalog information to a public sheet that informs +Romain Vialard's aptly named “Awesome Table” gadget.  This becomes the front end of the repository.

The Google form is one of the largest I have made with 160 items, most of which are conditional. Teachers are able to select grade levels and subject areas to organize what they are sharing. The formRanger method I described earlier is working really well. Even though I started with a baseline list of keywords, it was immediately evident that keywords could not be static.  I've added new keywords with each test submission. After the system is used for a while, I’ll be able to prune keywords from my original baseline that aren't being used. You can see a copy of the form here without the sign in restrictions so that you can see how it flows.

The spreadsheet builds the HTML for the Google Site using the variables from the form submission. The HTML is then used by the "createWebPages" script method to create the Google Site page. To do this, create two spreadsheet pages in addition to the the Form Submission page. Name one page "HTML" and the other "Page Builder."

Next, construct your new page template in Google Sites, manually using the variables you intend to feed from the form. Use a simple page format - not one with sections or columns. When the page looks right, click the HTML button in the toolbar and copy the code. Return to the Google spreadsheet and paste the code into the "HTML" page.  Scan the HTML for the variables that will be populated by the form submissions and cut the code around each variable, pasting it into sequential cells like this.  Leave out the variables, these will be populated from the form using a spreadsheet function.

Now combine the form submissions with the HTML on the "Page Builder" page using an ARRAYFORMULA. You can get creative with how to do this, such as using IF/THAN and VLOOKUP functions to customize the HTML based on the form submissions. Make sure you use the "Resource URL" column created by the formFolio script to connect the submission to the Google Site. In the demo example, you'll find a simple concatenate function:  

=ARRAYFORMULA ( HTML!$B$4 & 'Form Responses'!A2:A & HTML!$B$5 & 'Form Responses'!D2:D & HTML!$B$6 & 'Form Responses'!J2:J & HTML!$B$7 & 'Form Responses'!C2:C & HTML!$B$8 )

Once you have your HTML code you can invoke the createWebPages method. A decent guide for using this method can be found in +James Ferreira's book, Google Script: Enterprise Application Essentials.  Just a warning: this book was out of date just months after it was released - Google Apps Script evolves too often for print.  That said, the ideas found in the book are still relevant and useful.

What Didn't Work

I wanted to include a Google folder gadget on each page so that viewers could see what was shared. I was able to create code that worked using the method above, as long as it was pasted into a page. Unfortunately, createWebPages seems to have stricter rules for code. No matter what I tried, the script would strip the Google gadget out of the HTML. In the long run, I don't think it's necessary. I actually prefer the plus one gadget.

What Might be Next

I'd like to investigate +Martin Hawksey's post "Using Google Spreadsheets as a Google Analytics Data Bridge".  I think it'd be cool to use analytics to show users what's popular on the site. For the time being, though, the priority is showing teachers the site and providing professional development on how to use it.