Student Book Review Database W/ formMule & formRanger

One of our teachers, +Shoshana Cooper, had a great idea during our summer Chromebook orientation day. She wanted to create a moderated book review site for students that was powered by a Google Form. This post is the result of that conversation.  With the help of +Andrew Stillman's add-ons and +Romain Vialard's Awesome Table, this project is able to do quite a lot. I’ll start by showing what it looks like, explain what the system actually does, and then explain how to use the demo files step by step.

How it looks

The book review site is based on Romain Vialard 's Awesome Table gadget. This aptly-named gadget creates a sortable list from a Google Sheet and looks really slick. I add the gadget and the review form to a stripped down Google Site and embed the site using an iframe on any teacher’s webpage.  This almost effortlessly enhances the web presence for the class site and increases teacher buy-in. The gadget and form can be added in the regular way if a teacher has a Google Site. This modularity is important and will allow us put the same form in multiple spots to power a book review database for each of the five elementary schools!

What it does

The form is set to record the user ID to keep submissions accountable (this has to be turned on if you copy the form, the demo files are in the wild and don’t have this option.) The system uses Andrew Stillman's formMule Add-On to email the classroom teacher and a designated point person for each building in a district (the point person in my building is our librarian.) The email includes a copy of the student’s review and a link to the moderation Google Sheet. The first column of the moderation sheet is the moderation column - adding a “1” moderates the review and adds it to the site. Each review can also be edited using the “Form Response” sheet before moderation.

The review keeps the child’s name concealed when a when a review is written, saying instead, “The reviewer is XX years old from XYZ building.” When a teacher reviews a book, the review declares the teacher’s name. Each review has those cool graphic stars indicating the rating and can also display the book cover. The book cover is optional and requires the moderator to add a link on the moderation site to the cover graphic. To make this easier, the moderation sheet automagically links to the book’s page on Librarything. The moderator can visit the link and copy/paste the book cover image URL.

Students can also recommend a book for a type or reader (such as Kids who like scary stories, etc.) The “Type of reader” list on the form is updated via another Andrew Stillman gift, the formRanger Add-On. When a student comes up with a new type of reader it is automatically added to the form.  More on that later.

How to set it up

Don’t let all the stuff going on in this project dissuade you from trying it yourself, the complicated parts are taken care of by the add-ons and functions prepared in the sheet.  What is left to do is list the variables unique to your school, install the add-ons, and add the interface to your website. Lets find out how.

Add the variables

Copy the Moderation Google Sheet and Form. The sheet is already prepared with protected ranges on the moderation page to keep teachers from accidentally deleting formulas.  All of the variables for the form (teacher names, buildings, emails, etc.) are controlled from the “fromRanger” sheet. Navigate to this sheet and add the teachers, teacher emails, and buildings for the participating classes (delete the demo names first!)

In addition to the classroom teacher, each building has a point person to help moderate reviews. The point person in my building is our librarian. Determine who the the point person is for each building and update the emails in the “Point person for moderation” column. Finally, add any genres that you wish to include on the form. Hold off on the “Who would like this book” column, we’ll get to that is a bit.

How to install the add-ons

Installing and configuring add-ons is a little easier than working with scripts on the old Google sheets. We are going to install two add-ons to make this project work: formMule and formRanger. We will install and configure formRanger first.

formRanger

FormRanger is a form add-on and requires you install from the form. Navigate to the “Edit Form” window (from the spreadsheet Form > Edit Form) and click on Add-ons > Get Add-ons. Search for “formRanger” by New Visions Cloud Lab and install. Now formRanger will appear when you click the “Add-ons” menu item. Do this and click “Start.”

The formRanger sidebar will display when you click “Start” and every question on the form will be represented there. Each column from the “formRanger List” sheet needs to be set to update the appropriate question on the form. For example, under “School:” in the sidebar, check the box next to “Populate from values list.” Then click the “Select” menu and choose “New Values List.” You need to point the add-on to the book review Google sheet and specify the “Building List” column of the “formRanger Lists” sheet.  Repeat this action for the “Genre” and “Who would you recommend this book to?” questions.

The "Who would you recommend this book to?" column is updated by student submissions. Submit the form yourself a few times to add a some choices in order to get the list started. Consider items such as "Kids who like funny books." or "Kids who like suspense."

formMule

Unlike formRanger, formMule is a spreadsheet add-on. Install formMule by clicking “Add-ons > Get Add-ons” from the Book Review Google Sheet and search for formMule.  Once installed, click “Add-ons > formMule > Set-up > Choose Source Data and Set Merge Type.” Set the “Moderation” sheet to contain your email addresses and merge source data. Then turn on the time-based trigger to fire every hour. Click next to build your email templates.

The current system has two emails. One is sent to the classroom teacher and the building point person when a student submits a review. Another is sent to the same stakeholders after a review has been moderated. Both of these templates are pre-set if you name the templates correctly. Template 1 should be named “Teacher Notification” and set to trigger when the Timestamp is “NOT NULL.” Template 2 should be named “Moderation Notification” and set to trigger when “Type 1 to Moderate…” equals “1.” You can see these templates and edit them when you click “Next: Edit Templates.” When the templates are edited to your satisfaction, click “Build / preview templates” and formMule is ready!

Using Awesome Table as an interface

The system is ready, all you need is an interface. The spreadsheet is pre-configured to work with the awesome table gadget from Romain Vialard. This gadget is easily embedded in any Google site, making the system highly extensible. The “Awesome Table” sheet must be published for the gadget to work. From the Google Sheet, click “File > Publish to the web” and select “Awesome Table” from the drop down menu. Then copy the link that appears. After you copy the published link, use this tutorial to help you install the Awesome Gadget into a Google Site. Romain Vialard has also composed a comprehensive guide.

Yup. That’s it.

Thanks for making it this far! I’m excited to see if this project gets traction with our students and teachers. Towards that effort, I am helping our fourth grade teachers install the system during their next grade-level meeting. Future plans include gamifying the review process and including a badge system. Please let me know if you use this project - especially if you think of a way to make it better!

Creating a Google Form Powered File Sharing Site in Two Ways: Nose to Tail

 

This blog post has been sticking in my head over the last day and I decided to rewrite sections. The whole system that was used to create the Scarsdale Teacher Share is complex, and while it works for us, it isn’t for everyone. There is a lot to be said for simplicity and I’d like to include a less complex alternative that accomplishes a lot of the same stuff.  In the revised post below, I’ll explain the easier method first and then continue to the more complex version. The whole thing is pretty long - but I hope it's useful.

A couple of weeks ago I had the honor of presenting at the NY/NJ Google Summit.  Lisa Thuman does an amazing job putting the event together and I’m always impressed at the caliber of presenters and participants.  This year I presented on the Google script powered teacher sharing site that I’ve blogged about before. I promised the good folks in my session (thank you all for coming!) that I would write something to outline the system; this is that post. I meant to get it out sooner, but in the immortal words of John Lennon, “Life is what happens to you while you're busy making other plans.”

        This process allows a district to create a Google Form powered Google Site. A teacher can submit a file to share, organize it with self selected or generated tags. The simpler method uses a special gadget to interface with the form submissions and the shared resources.  The more complex example creates Google Site pages created automatically using the information given by the form.  For the complex example, I created a set of simple demo files that you can use here. The files that run the Scarsdale site are more complicated, but all of the basic functionality can be found in these demo files.

      With either method you must create a spreadsheet using the old sheets and build a form. Decide whether the system will work with a domain or be public.  For our district sharing site, the form is limited to the domain. Your spreadsheet needs to collect at least six things:

1. Name of the contributor (Text question or automatic in a domain-only form)
2. Email of the contributor (Text question or automatic in a domain-only form)
3. Name of the resource (Text question)
4. A description of the resource being shared (Paragraph text question)
5. A field for tags (Text question with an “other” option)
6. The URL of the drive resource that is being shared (Paragraph text question)

Once your form is in place, return to your spreadsheet and install the New Visions Form Folio Script. You will have to determine whether you are collecting usernames at this stage. You will also need to determine whether shared resources are copied and how they will be organized by formFolio.  The demo example uses the “Keyword” column to organize resources. A folder is then created for each keyword.  Scarsdale collects usernames in our domain-only share.  These usernames become our method for organization. We also copy the shared resources so that resources are stable despite faculty changes.

After you install formFolio, add the following sheets to the document:

• formRanger List
• csv horz array
• HTML
• buildPages (not needed in the simple method)
• createPages (not needed in the simple method)
• Awesome Tables (optional in the complex method, but you’d be crazy not to)

The “formRanger List” and “csv horz array” use the dynamic tagging system I described in a previous post.

The Simple Method: formFolio & Awesome Tables

The basic components for a teacher share are in place once you have your form and have installed formFolio.  Next, let’s create the interface for the shared resources using the Awesome Tables gadget.  Create a query on A3 of the Awesome Tables sheet. Use the query to grab the form submission data that you want to use in your interface. In the example below, I query the “createPages” sheet because the example showcases the complex method.

=QUERY (createPages!B2:J, "SELECT D, E, F, G, H, J", 0)

Then, use the first row to title the columns. These titles will be used in the interface. The second row corresponds to how the columns can be filtered by the end user.  Use the “Awesome Table” documentation to set this up. The spreadsheet has to be public in order for Awesome Tables to work.  If you don’t want to make your whole spreadsheet public, use two additional Google sheets, along with the New Visions script pushData to move the Awesome Tables page to a public sheet.

The final step in the simple version is to add the awesome table gadget to a Google Site page.  This is also explained in the “Awesome Table” documentation.  You can get creative with how the filters behave in the awesome tables sheet and a lot an depend on the data you’ve collected in the submission form.

The Complex  Method: formFolio & Google Site Page Creation

Let’s return to the complex method if you’re up for it. We need to look first at the “csv horz array” page. In the sharing site example, you’ll notice two differences from the dynamic tagging system I described in a previous post. Column A on the “csv horz array” page is named “Key.” This column combines the submission date and username to create a unique value to use on other sheets for vertical lookup formulas. Column B is named “HTML” and uses an if/then array formula to check whether tags have been assigned to a new resource.  If a tag cell is blank (column F and beyond) the formula uses canned HTML from the “HTML” page. If the tag cell contains a keyword, it is made into an HTML link that searches the site for that keyword, also using the “HTML” page as a reference form the code.

= ARRAYFORMULA ( IF ( F2:F2 = "" , HTML!A10 ,HTML!A7 & F2:F & HTML!A8 & F2:F  & HTML!A10 & (IF ( G2:G = "" , "" , HTML!B7 & G2:G & HTML!A8 & G2:G  & HTML!A10 ) & ( IF ( H2:H = "" , "" , HTML!B7 & H2:H & HTML!A8 & H2:H  & HTML!A10) & ( IF ( I2:I = "" , "" , HTML!B7 & I2:I & HTML!A8 & I2:I  & HTML!A10) & ( IF ( J2:J = "" , "" , HTML!B7 & J2:J & HTML!A8 & J2:J  & HTML!A10 ) ) ) ) ) ) )

This if/then strategy, combined with the HTML page, can be used creatively to build variety into your google site page based on user submissions.  Remember to install formRanger in order to connect the script as described earlier to make your form dynamic.

Next we turn to the “buildPages” sheet. The first column is another "Key" column. Just as on the “csv horz array” page, the “Key” column combines the timestamp and username field.

= ARRAYFORMULA ( B2:B &C2:C )

In cell B2 we use a simple arrayformula to copy over the form responses and formFolio columns without including the headers.  

=ARRAYFORMULA ( 'Form Responses 1'!A2:K )

The column headers are manually named in the first row to keep them friendly for the createPage script that we will discuss in a bit - form response headers have a habit of being a bit long. In column M we will add use a "LEN" function to count the Drive ID column that is created by the formFOlio script.  This returns a number when a resource is added to our share folder.

=ARRAYFORMULA ( LEN ( L2:L ) )

Column N uses an arrayFormula with a VLOOKUP that references the “Key” column. For a simpler method, you can install the copyDown script and forgo the arrayformula altogether.

=ArrayFormula ( IFERROR ( VLOOKUP( A2:A ;'csv horz array'!A$2:B  ; {2} * SIGN ( ROW ( B2:B ) ) ; FALSE ) ) )

Now lets move to the createPages sheet. The first two columns on the "createPages" sheet need to be "ID" and "Product Page." These will be used by the createPages script later on. Cell C1 will use a Query formula to pull the data from the buildPages sheet. Using this query we can reorder the columns and specify a query condition. We will use the LEN column that we added to the buildPages sheet to validate the query.

=QUERY (buildPages!A1:N, "SELECT A, B, C, H, E, J, N where M > 1", 1)

Now you can create the HTML for your Google Site page template. Take a look at the HMTL sheet. You will notice that I divided the HTML with yellow cells.  These cells correspond to the variables that are fed from the Google form.

Finally, we need to add the createPages script.  This essentially uses a script from the book “Google Script: Enterprise Application Essentials” The book is dated now, but you can still pull good ideas from the text.  If you investigate the Script Editor on the demo sheet, you will see that the script builds HTML pages on the Google Site using my code. Whenever a variable is called, the script references the columns of the createPages sheet.

This completes the more complex custom Google Site file repository, powered by a Google form.  To round out your site, you could create an interface with Romain Vialard’s aptly named “Awesome Tables” gadget, or simply set your navigation to auto generate.  A big thanks to New Visions for Public Schools for their amazing script library.

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!

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.