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: https://sites.google.com/site/usfounderbook/

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 http://sites.google.com. 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!

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.

Google Site Sharing Repository, Part 1: a Tagging System with formRanger

This is the first post about creating a Google Site file sharing repository.  The site will be populated automatically via a Google form, and powered by a few +Andrew Stillman  scripts (for background, look at this post here.)  This post will explain the tagging system, and how a combination of Google Spreadsheets, some functions, and formRanger will make the tags teacher generated and dynamic.  

Tags will create the organizational structure of the file repository. Teacher ownership over the tags is a crucial part of this.  It is impossible for a small group to derive the best categories for all the teachers across multiple buildings and grade levels. Furthermore, categories change over time and the site needs to be dynamic to stay relevant. The Google form that we are using to submit resources will employ "checkbox" question types for teachers to select multiple keywords. These will be organized by grade level and subject. Each checkbox will also include an "Other" option for teachers to contribute their own comma separated keywords.  The contributions added in the "Other" box will be taken by Andrew's formRanger script to update the form, creating a feedback loop. Users of the site will be able to see how many times each keyword has been used and will be able to click on Keywords to see similar resources.

In order for this to happen, the spreadsheet needs to do several things: 

1. separate the comma separated keywords
2. identify the unique keywords
3. add the unique keywords to a list that informs formRanger
4. and count the number of times each keyword is used. 

To do this, the spreadsheet needs three pages:

1. one for the form responses (created via the form.) (Form Responses 1)
2. one to separate the comma delimited values (csv horz. array)
3. one to collect the unique keywords into a column and count how many are used. (formRanger List)

First, create a form and use a check box question type for the tags.  Add all the categories that you'd like to start with to the check box. Use the form you just made and check off each category, then submit.  This step is important because formRanger will replace the categories with those from the spreadsheet. Filling out the form gives formRanger a baseline.

Next, create a sheet for formRanger. This sheet will use the form submissions to check for unique items (from the "other" field,) split the responses by comma, and sort alphabetically. Add this function to A2 of the "formRanger List" sheet:

=sort(unique(transpose(split(ArrayFormula(concatenate('Form Responses 1'!B2:B&",")),","))))

Then, install formRanger and use the column created on the formRanger List sheet to populate your checkbox. Set formRanger to run with every form submission.

At this point, you have a loop where the tag question from the form is updated automatically. Now, lets set up a counting system so users know how many times each tag has been used.  To do this we need to break up the CSV responses so that each tag has it's own cell. Create a third sheet to delineate the comma separated values from the checkbox responses. Use this function in A2:

=ArrayFormula(iferror(if({1,0},'Form Responses 1'!B3:B;split('Form Responses 1'!B3:B;","))))

Notice that the array starts on B3, this is because you don't want to count your first submission where you checked everything. Finally, count the instances of delineated values in the new sheet using this function in B2 of the formRanger list sheet:

=ArrayFormula(if(len(A2:A),countif('csv horz. array'!B2:BZ,A2:A),iferror(1/0)))

So, there you have it! The "formRanger" list can be used in a number of different ways.  I'll be converting these into links that search the website.

There is one thing that doesn't work the way I intended.  The "sort" function on the formRanger List page doesn't alphabetize the first and last entries.  I have no idea why - let me know if you do!

Building a teacher sharing reposititory in Google Sites using scripts

My district uses Google Apps for Education, which is an amazing platform to support teacher communities of practice.  That said, creating a culture of collaboration is hard - there are all sorts of things that get in the way. Teachers often question whether what they have is worth sharing, they are concerned that their work will be judged by peers, but most of all, they don't have time.  To help build the culture in my district, we are promoting a mixture of Google Plus and physical "meetups" to encourage teachers to share practice. But while Google Plus is great for conversations, it's less useful to share "stuff." 

We thought Google Drive might help so we organized folders by subject and grade level.  It was important to us to make the process democratic and we gave the whole faculty edit access to the folders. Unfortunately, this had unintended consequences. Teachers accidentally edited the work of others without making copies. Folks who installed the local Google drive accidentally deleted work that they didn't need - not realizing it deleted the work for everyone.  This didn't help win over the cause of sharing.

When Andrew Stillman released the formFolio script I realized that there might be an alternative.  Using a combination of formFolio, formMule, formRanger, and some custom script, I am creating a Google Site that will do the following:

1. Create Google Sites pages automatically on teacher form submission.
2. Add the teacher's resource to a neutral folder where people can access but not edit.
3. The form will create a page that contains a link to the resource, a description, attribution, and custom tags created by the user that link to similar resources.
4. User created tags that will, in turn, re-populate the form to keep it relevant.
5. The Google site will include analytics to point teachers to the most popular submissions.
6. User will be able to promote useful resources via Google Plus.
7. When teachers submit a resource, they will also receive a thank you email with a link to their resource page and a link to suggestions of similar resources.

The pages created by the Google form will create pages similar to this mock-up. Ultimately, we need the system to be easy to use.  My teachers are already using Apps and I hope to reduce cognitive load by using tools that already familiar. Furthermore, building the site in Google keeps the system free and consistent.  That's the plan, anyway! 

I will document the journey here in parts.  My first post will describe the custom tagging system.  I know it can be done, whether the system will be successful remains to be seen. I'm going on the "If you build it..." theory.  

Keyboarding: It isn’t sexy, but neither is eating.

We kicked off keyboarding this week with our third graders.  Keyboarding will be king until the way we input information into computers shifts.  And while speech recognition is getting pretty awesome - it still doesn’t work in a crowded room.  The skill is really important - especially as I watch my 5th grade students peck inefficiently on their new Chromebooks. We can do better. 

So this is our plan: Every third grade student cycles through the lab every day for twenty minutes during a two week immersion period.  We’ve purchased a school subscription to Typing Club, a web based typing app.  It is free for individuals, but we needed the centralized management of the school edition.  I’m happy to report that the pay version is a more robust than the free beta was last year. Typing Club can use Google Apps as a passkey which is also really convenient for us. We’ve pushed the Typing Club Chrome App to our students’ Chrome browsers.  They have to configure the Chrome App (command+click on the App, go to “options,” and then point to our Typing Club domain) but that is easy enough.

During the first week, students practice typing position and the home row. During the second week, I tape a thick piece of paper to the keyboard and require the kids to cover their hands with the paper.  The paper has a keyboard printed on it for reference if they have to look down. After week two, the immersion period is over and we move into homework mode.  Each student must use typing club at home twice a week for fifteen minutes.  It’s easy for teachers to hold students accountable via the Typing Club report feature.  We also send this letter home, which helps communicate the "what and why" of the plan.   We are just entering week two.  The kids have gotten into using Typing Club, they like competing against their own scores.  The program is straightforward and doesn't have the glitz of some of the competition.  I actually like the clean interface and I think it keeps kids focused on what they need to do.  A game or two would be a nice reward, though, and this is missing. Hopefully they will add some moving forward.  Bottom line: the programs works well, it’s simple to set up, and it’s easy for kids for use. I'll let you know how it goes.

Online Rights and Responsibilities with formRanger

The highlight of this week was a 5th grade lesson on digital citizenship.  Students had previously created a chart of classroom rights and responsibilities and discussed the differences between them.  In my lab, we set out to create another list of digital rights and responsibilities. This is a particularly relevant task considering our upcoming 1 to 1 chromebook initiative. The lesson had three elements that worked: using the right right videos, having students create a list collaboratively in a Google spreadsheet, and then vote on their favorites using a formRanger updated form.

It’s hard finding videos on digital citizenship for 10 year-olds because most of the content is targeted for older or younger students. After much searching I found two that fit perfectly.  The first video is a short piece on digital footprints and was a perfect anticipatory set to start our discussion. After talking about digital footprints and why they are important, students started collaboratively building a list of what they felt should be their online rights and responsibilities. I get a thrill each time I see a class working on the same spreadsheet at the same time.  It is amazing to watch them self organize.

After a few moments, we brought the class back together to watch another great spot by Common Sense Media on Cyber Bullying. We checked for duplicates as a team and the student written Rights and Responsibilities were used to update a Google Form via the formRanger script.   Before the period ended, students had used the form to vote on their new digital rights and responsibilities list.  

The Google spreadsheet and form allowed the kids to be at the center of the process.  In the past we would use chart paper and solicit discussion.  Some students would contribute, others would watch.  This lesson was different - everyone was involved - everyone contributed to the list in some way.  That universal contribution lead to more engaged discussion and a pretty great list.  See for yourself.

Observation Form with Autocrat and FormRanger

It's almost as if the Google Scripts Autocrat and FormMule were made with the teacher observation process in mind.  Although the good folks at YouPD shared an excellent example of a teacher observation system using FormMule, there were a few things that didn’t quite fit my school. Thankfully, I was able to study their Teacher Mini-Observation Tracking System and lift some really good ideas.  In the end there were several things that I needed to behave differently:

• The formMule script triggers it’s merge on form submit and I need my administrators to have less pressure. They might start the observation in the classroom, but come back to it later to flesh out comments. To do this I shaped the system around the Autocrat script because the trigger for the merge can be based on a value in the spreadsheet. As a result, the administrator can start the form and submit it unfinished, then come back to the form later via a bookmark.
• The YouPD Mini-Ob tracker is provides a beautiful structure for great feedback.  I needed the form to be a little easier to submit via a tablet. My form relies on multiple choice questions organized and informed by by Danielson's four domains. Each domain includes a comments/evidence section, for more personalized feedback.  Administrators can bang out notes here, and pull it all together later after bookmarking the incomplete form.
• My teachers will be observed three times this year - one formal and two informal. This system is meant for the two informal observations.  If two observations have occurred, the teacher’s name is removed from the form using formRanger.  

That's it. Most of the magic happens in the autocrat script. The "log" page concatenates each domain's feedback and adds the personalized comments. I think the doc looks pretty good when it's merged. I’ve left some comments on the spreadsheet to help navigate how to make it yours.  I hope it’s useful!

Click here to open the Ob Form Folder