A Google Slides Student Museum

Back in March I had the good fortune of sitting in on +Caren Macconnell’s Google Summit workshop on Choose Your Own Adventure Using Google. Caren did a great job and really got my wheels turning - you can do a lot with this kind of interactivity and it shines in the classroom.

In the past I've used a wiki to create interactive stories like this. It worked pretty well but graphics were more complicated than they needed to be and students couldn’t work on the same page at the same time. Google Slides is a much more flexible and user-friendly tool.

Creating non-linear links or buttons in a Slides presentation is a snap. Just select the text or shape that you want to link to another slide, click the link button, then select the destination slide. This process is even easier if you title the slides beforehand.

This came in handy recently after a local artist visited one of our second grade classrooms. Caren shared a museum template during her Summit workshop that becomes a great showcase for student work and reflections. This was the inspiration for the Museum at QR Slide deck embedded in this post.

Like Google Drawing, slides is an under appreciated tool. I think Slides is sometimes mistaken as limited because it’s easy to use. Don’t fall for this trap, nothing is further from the truth.

Open Thinking Pays Dividends

Last Wednesday I participated in a Google Educator Group Hangout with +James Peterson, +Jane Lofton and +Dan Imbimbo. It was meant to be an advanced sequel to the Google Edu on Air workshop about sheet powered Book Review Sites and it turned out to be much more. It was the kind of great conversation that keeps you up for a few nights while you work out what was discussed.

After the Hangout, James posted his hack of the advanced book review site and his thinking floored me. That evening I stayed up to 3 am incorporating his thinking into my own; I can truly say that the project is better and I am smarter as a result.

For background on the original book review site you can read this post or watch the Google Edu Hangout on Air. James took this site idea and brought it to another level:

• He used HTML in the Awesome Table list to include an average star rating next to each book title. which was such a smart way to provide feedback to users.
• He figured out a way to build a sheets function with “join” and “filter” to combine multiple reviews of the same book into an HTML snippet.
• He uncovered the templates feature of Awesome Tables which isn’t in the official documentation

James did a truly excellent job documenting these improvements on his blog.

James’ book review system is targeted to teachers, not students, and his design has different requirements as a result. I needed to incorporate his improvements into my system where posts are moderated and submission notifications are sent to teachers and librarians. My system also asks slightly more of the submitter, including a plot summary. In the near future I’ll write an explicit post about how I incorporated James’ system, but you are welcome to copy the demo sheet to pull it apart now. Full disclosure - there are a couple of clunky things I’d like to streamline, but it still works as advertised.

I’m so thankful for my PLN who is a daily source of inspiration and support. Every once in a while I’m shown how powerfully good this network can be. This was one of those times.

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 PixelPaperCraft.com 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.

Planning

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.

Building

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.

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 d.school. “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 d.school’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 d.school’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.

Elementary Student Placement Calculated with Google Sheets

Right around now, elementary schools everywhere are beginning to discuss student placement for next year. It can be a time consuming process, with lots of meetings loaded with multi-colored index cards and post it notes. In an effort to make the process easier last year, I created a Google Sheet to help teachers visualize balanced classrooms. This year I improved the system based on lessons learned.

The sheet is shared with teachers after student demographic information is added to the "data" tab.  Teachers complete the sheet with academic and anecdotal information. Then, teachers organize an initial placement of their class. Because of Google’s Sheet collaborative capabilities, this is all done before the first meeting.

When the grade level teachers do meet, they already have a starting place because of the initial placement. The “Dashboard” sheet provides a quantitative look at how the classes are balanced by automatically calculating all of the information, including gender, academics and behavior. Students are moved from one class to another on the "Data" sheet to rebalance the numbers.

The "Class Roster" sheet allows the placement team to see the classes by name.  It’s possible to customize the data shown in each column by using the drop down menus on the right side of the roster.

I’ve included directions for the administrators who are setting up the sheet and for the teachers who are placing their students. Last year we were able to reclaim one whole day from the process. I hope others find this useful as well. Please let me know if you use it and if you have any ideas to make it better.

*All data used in the example is fictional for demo purposes.

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.