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!