Sunday, November 23, 2014

Redefining the Bird House, Part 2

The second grade students learned more than I expected in our "Redefining the Birdhouse" challenge. Beyond making cool birdhouses, they might be able to help Congress...

Mrs. Cheung’s second grade students had spent several days brainstorming their redesigned bird habitats and now it was time to start making.  These students are familiar with using materials like Legos to build - but this project was different.  They needed to realize a three dimensional birdhouse from 2 sheets of 18 inch square cardboard. We wanted to ease the class into three-dimensional thinking and how better to do that than with Minecraft?

Three-dimensional thinking
Building in three dimensions from two dimensional sheet material isn’t intuitive - we imagined confused stares and the chorus of, “What do we do now?”  We started by printing out Minecraft blocks from using card stock. Cutting, folding, and gluing a 3-D cube is challenging but the children were able to persevere because, well, Minecraft is cool. We modeled cutting and folding the paper and had a sample cube available (without adhesive) so that students could see how it turns out. Although students used glue sticks to hold the cube together, I’d recommend having some scotch tape on hand, you’ll thank me later. It took a cognitive leap for the class to turn the paper into a cube, and I’m glad we started that way.

Cube building was an important step to prime our students for three dimensional thinking. Once primed, students reconvened into teams to plan how to change their cardboard sheets into 3-D bird houses. The children used small whiteboards and dry erase markers to sketch out where they should cut and fold the cardboard. Whiteboards have a playful, impermanent feel and the teams planned with enthusiasm. Teams needed to take turns planning on the small whiteboard and negotiate the direction for the birdhouse.

It became clear that inter-group communication was valuable once students started building. Students used scissors, safety cutters (I highly recommend these for any elementary maker space!), and duct tape to construct their houses. Glue guns and the drill press (for bird house doors!) were available with teacher support. Each group was doing something interesting, but in isolation from the other cool things that were happening in other groups. We needed to reinforce that the teams were not in competition with one another, and could, in fact, help other teams. We needed to regularly stop the work and discuss what was going well and what wasn’t. Students were more self-assured after each discussion. Where teams needed to practice working together during the planning phase, now they needed to work together as a class. It was strangely foreign!

What I’d do differently next time
Next time I do this project I will try to build a better scaffold for empathy from the bird’s perspective. It is difficult for a seven year old to think from the perspective of another person, much less a bird. The kids did a great job but I think it could have been even better. I think it’s great that they figured out how to make furniture, I’m just not sure it fits into the design challenge. I’d also push harder on inter-team collaboration. I cannot stress enough how valuable it was to have the teams share with one another. Our students are used to competing with one another, both individually and in teams. It took a shift to think of the design challenge as a common goal that we could work on together to solve.

Why I think Congress should try design thinking
We live during a polarized time where people are more likely to talk over one another than to one another. The design thinking process encourages students to put themselves into someone else's shoes when they problem solve. The process enables teams to work together towards a common goal, rather than against each other. Lately it seems that the skills of negotiation, collaboration, and perspective sharing are rarely practiced. I'm encouraged that this generation could buck the trend.

Thursday, November 13, 2014

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 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."

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!

Thursday, November 6, 2014

Redefining the Bird House

Recently I met with one of our second grade teachers, +Jennifer Cheung, to redesign a performance task for a unit on animal habitats. We were inspired by the birdhouse project mentioned in the SparkTruck movie and decided to expand it for her class (You can see our learning plan below.) It's been an interesting project so far. Next week we begin the "Prototype" phase and our student teams will build their proposed birdhouses out of cardboard. I can’t wait to see what what they come up with.

The addition of the second T-Wall in our Maker Space made it possible to have a full class ideating at the same time. Jen's class used the space several times to brainstorm their bird habitats and I couldn't be happier about how it worked. The T-Walls really contribute to collaborative brainstorming and make ideation sing. But the best part of the maker space is not the T-Wall; it’s the design thinking process developed by the Stanford The stuff in the space is useful, but it’s the process that’s changing pedagogy. 

Here's how we broke the process down:

Empathize (Done in the Classroom and at home)
“How do the birds in our area interact with their habitat?”
Observe the birds outside of the classroom and at home.  What types of birds do you see?  What are the birds doing?

Define (Done in the Classroom)
“What does a bird need to be comfortable in a habitat?”
What do the birds need to do what they are doing? Do these birds need special things?

Ideate (Done in the Maker Space)
“How might we build a comfortable habitat for these birds?”
Use “T-Walls” to brainstorm in teams.  Don’t yet explain the constraints of the project so as not to discourage “moonshot” thinking.  Have teams talk through their ideas. At the end, engage in “Considered Selection.” Each team member gets a graphic organizer with three columns: "Most Practical," "Most Exciting," and "Most Unusual." Team members write one choice for each category on the organizer. This becomes a way to vote as a team.

Prototype (Done in the Maker Space)
This is where the teams discover that there are constraints to the project.  Each team gets a square of cardboard (1.5 feet by 1.5 feet?), string, and duct tape to make the bird house. Teams will have safety cardboard cutters, hole punchers, and scissors. Teachers will help with a hot glue gun, and string. In fact - I’m thinking about getting Flex Seal to coat them. (As seen on TV!)

The Birdhouses are hung on the tree and the class will observes how the birds interact with them during the year. If time allows, Perhaps students will redesign the houses after several months of observation for fidelity to the design thinking process.

Sunday, October 5, 2014

The D.School's T-Wall: Flexible Collaborative Spaces

Last summer I spent a lot of time trying to rethink our school’s Maker Space. One of my biggest influences was  “Make Space” by the Stanford “Make Space” showcases flexible design and creative environments that adapt to the needs of the people working in them. One project that resonated with me was the “T-Wall,” a freestanding structure with two intersecting 6’ X 4’ walls clad in dry-erase board and mounted on casters.

I had thought about getting whiteboard paint to surface the workbenches, or maybe the cabinet doors, but it’s expensive and seems difficult to apply. A T-Wall, on the other hand, can be moved around the room and acts as a great divider for group work. What’s better, the whiteboard material is just Gloss White Hardboard which is a great deal at 14 bucks a sheet. I used the’s plans and after buying lumber, quality casters, and fasteners, the T Wall cost me just under $120. Not too bad for a piece of moveable classroom furniture with 5 large spaces for dry erase collaboration! Even so - I think I can make these cheaper with experience.

This has been a crazy September, but I’ve still used the T-Wall a few times. It was fantastic in a staff meeting where teachers could form groups based on learning style, discuss a problem, and write or draw freely within their nook.  It was just as good with students brainstorming ideas for the Maker Space. The kids naturally engaged in conversations about their ideas when confronted with the common surface of the “wall.” Then, they took a museum walk to read each other’s ideas.

It was successful enough to make a second T-Wall. This time I am going to deviate from the’s specifications. The original plan calls for two 6’ by 4’ walls. Unfortunately, this makes it difficult to move through a standard doorway. I had to assemble it inside the room. Also, the T-Wall stands at an imposing 6ft 4in after adding casters. This is tall enough to collide with the SMART Board arm at the end of the classroom - an accident waiting to happen. The next build will be a called the “Little T”, one that’s a foot shorter and six inches narrower.

Saturday, October 4, 2014

Student Timelines from a Google Form using sheetSpider

An update for this project has been posted here.

The goal of this project is to give students a personal timeline of the wonderings and observations that they make using a Google Form. Then, this slick timeline can be embedded in a student portfolio. This project can be integrated into the moderated Twitter exit ticket that I blogged about a few weeks ago, or it can stand alone.

Here is what you need:
  • A simple Google Form linked to an “old” spreadsheet that required students to sign into your domain.
  • The SheetSpider Script from New Visions for Public Schools (thus the “old” spreadsheet)
  • An affinity for nice looking timelines

I can’t wait to implement this in my school, and hopefully build it into some student portfolios. One word of caution: this system needs the student satellite sheet to be published to the web in order for it to be read by the Timeline application. The next section explains how to do this yourself, step by step. There currently isn’t a moderation component although one could be built in, similar to the one used for the Twitter system.

So How Does This Work?
SheetSpider is a nifty script that lets you distribute values to multiple sheets based on a criteria - in this case the criteria is the student Google ID.  Based on this ID, sheetSpider will send each student’s exit ticket responses to a unique Sheet for that student. SheetSpider can be set to use a template when making the satellite sheets and we will use the template file from Timeline JS. If you haven’t seen this Timeline creator, you should.  It does a beautiful job.

Make a copy of the Timeline template and then use the “copy to” feature to add the sheet to your “Exit Ticket” sheet file. The timeline sheet has more columns than we need for our simple exit ticket form, but we need the whole template when we copy the student submissions to the satellite sheets. Specifically, we want to send the form response timestamp to the “Start Date” column, the exit tweet column to the “Headline” column, and the username column to "Media Credit." A simple function like the one below can move the data from tab to tab, you'll see it at the top of the previously mentioned columns in the demo sheet.

= ARRAYFORMULA ( If ( 'Form Responses 1'!C2:C = "", "", 'Form Responses 1'!C2:C ) )

Now it’s time to install the sheetSpider script. Keep in mind that as of this writing, sheetSpider is only available with an  "old" Google Sheet. You must click "tools" > "script gallery" and search for "sheetSpider." A tab named “SpiderSheetEntities” will be created when you install and initialize the script.  Then, click the sheetSpider menu item and proceed to “Step 1: Set Up Entity Sheet.” 

We have to set the script to run on “Manual Push” because we are using the copied timeline sheet to trigger sheetSpider instead of the form responses sheet. All of the items in Step 1 can match to the columns in the “SpiderSheetEntities” tab that was already created. You might want to create a folder for your satellite timeline sheets and paste the folder ID into the “Key of Primary Folder…” field.

Now proceed to “Step 2: Provision/Update…” In the first field, paste the document Key of the Timeline JS template. The feeder sheet needs to be set to “od1” which is the tab that we copied over from the template. The unique entity name can be set to “Media Credit” which is where we fed in the student usernames. At this point, click the “Save and Provision…” button.

“Step 3: Disagregate and Push Data” is the last sheetSpider step we need. Make sure your settings are “Manual Push” and “Append only new unique records.” The uniqueness criterion can be both “Start Date” and “Media Credit” to ensure uniqueness. Running Step 3 will create the satellite sheets. You will need to publish those sheets for the timelines to work.

Now lets connect the timelines.  Open the “SpiderSheetEntities” tab and insert two columns to the right of Column F. Name these “Timeline Embed” and “Timeline Link.”  Then, add one column to the right of “J” and name it “Spreadsheet Key.”

Paste the following function into K2:

=ARRAYFORMULA(right (I2:I,23))

Paste the following function in G2:

= ARRAYFORMULA ( IF (A2:A = "", "" , "<iframe src='" & K2:K & "#gid&font=Bevan-PotanoSans&maptype=toner&lang=en&height=350' width='100%' height='350' frameborder='0'></iframe>"))

Then, paste the following function in H2:

= ARRAYFORMULA ( IF (A2:A = "", "" , "" & J2:J & "&font=Bevan-PotanoSans&maptype=toner&lang=en&height=650"))

These last functions complete the project.  Student submitted exit tickets are aggregated to personal spreadsheets, and once the spreadsheets are published, they are used to create embeddable timelines. The embed code works great but not in a Google Site. Use the "Timeline Link" and the iframe gadget if you are using a Google Site. Let me know if you use the project or make it better!

Wednesday, September 3, 2014

6 Day Cycles & Google Calendar Reboot

It’s schedule time again! The problem is simple: many schools have schedules that follow an a-typical cycle, often using six days instead of five, and Google Calendar doesn’t have a mechanism for recurring events like this. This post cleans up the six day scheduler that I wrote about a year ago. This customized Google Sheet uses the New Visions for Public Schools script “formMule” to automatically create a custom schedule in Google Calendar for the year. My secretaries, nurses, and other support staff loved being able to use Google calendar to easily find any class in the school. This post will provide step by step instructions to make it your own.
Start by making a copy of the 6 day scheduler. Click on the 6 Day Cycle tab and adjust the schedule to fit your system. This sheet is currently formated for the 2014-2015 school year. The Weekday, Six Day Cycle, and Month columns are easily built with functions, but I use a different sheet to calculate these values and then import them using the pushData script. It’s good to use direct values here; there are a lot of dates and all those functions slow down the sheet. Adjust the "Six Day Cycle" column If your schools uses something different. There's no reason you can't have a four day cycle!
Next, click the faculty tab and update accordingly. The email column is optional and only needs to be used if you want to invite teachers to the events in the schedule. I skip it because this leads to lots of email all at once. I don’t want to spam my teachers!
Click on the sheet named “Teacher Schedule.” The scheduler currently accommodates up to 9 periods in each day. Please note that you don't need to use all the periods - the sheet will only create events where the "Description" column is used. Customize the “Start Time” and “End Time” columns to reflect the time slots for your school. The Description column is used to name the events in your calendar. As mentioned before, events that have values in the "Description" column will be created in the Google calendar. These cells are validated from column A in the Faculty List Sheet. Using the validated values (the down pointing arrow on the right side of the cell.) saves lots of time in the long run.
I’ve packaged the formMule script with this sheet, so it should come installed when you make your copy. I’ve also included installation instructions below If something goes wrong with the package. The instructions are at the end of the post
Things I’ve learned:

This project evolved from a lab sign up Google sheet. The sheet was used successfully by teachers all last year to sign up for labs and carts. Appointments were automatically added to a shared calendar. It mostly worked as planned, but teachers found using the sheet to sign up for lab time awkward. More troublesome, teachers had difficulty deleting and updating events. Scheduling frustration never helps anyone, so I’ve decided to forgo the Google sheet for teacher sign ups and give Appointment Slots a chance. It will be a pain to set up but I think it will be a lot easier for teachers. The six day scheduler, on the other hand, has worked great!
formMule Installation
Install formMule from the script gallery (Tools -> Script Gallery.) Click the formMule menu that is now available, run the initial installation, authorize the script and accept the terms. Click the formMule menu again and select “Step One: Define Merge Source Settings.” Set the Merge source to be “Master Schedule” and click “Save Settings.”

Click the formMule menu again and select “Step 2b: Set Up Calendar Merge.” Click the checkbox next to “Turn on calendar-event merge feature.” Under “Create Event Condition” set the drop down box to “Description.” Type “NOT NULL” in the field to the right of “Description.” Set “Event Title” to ${"Description"}, if this is for a particular calendar, Art, for example, I will add “(art)” after ${"Description"}. Set “Start Time” to ${"Start Date Time"}, and “End Time” to ${"End Date Time"}. Click “Save Calendar Settings” at the bottom of the window.

Go back to the formMule menu and click “Preview and perform manual merge.” Look at the “Event Create” tab and make sure that the values are correct. Click “Run Merge Now” on the bottom of the window if everything looks right.
A year-long schedule can have thousands of events and a single merge might not complete the year. I add a time based trigger and set it to fire every hour. The complete schedule will be added to the calendar in less than a day!