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!