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.