Tuesday, August 8, 2017

Securing Live Google Sheets

Once in a while, I have a student who knows my technology tricks well enough to teach me a lesson. That didn't happen this time because the little voice on my shoulder spoke up before a student crashed my live Sheets experiments.

If you haven't read the post on Live Exit Tickets, please do so before continuing with this post.

google sheets security

I hadn't thought much about securing the document key. In fact, all of my students had the doc key to the master sheet, which meant they could potentially hack the sheet.

This issue only came to mind when I decided to ditch the online gradebook provided by the school system for something more reasonable – a book designed with Google Sheets that could do whatever I needed (more on this project still to come).

I'm sure there's a more sophisticated way to solve this problem, but the solution I present below works. It's a bit labor intensive on the front end, so I'm working on solutions to automate more of the tasks and will share my findings as I learn what works best.  

Exit Ticket Security

1. Make a secure sheet for each student.

These sheets will not be accessed by the student. I keep them in a folder labeled "Secure Sheets A 1718 (class block and school year code)." The data is sent from these individual sheets to the sheet the student makes with the doc key I email to them (step 3 below).

Simply put, this individual sheet protects the master sheet document key. Otherwise, students would be able to use the document key to change data and see confidential information, effectively creating a FERPA violation. No one wants that, right?

2. Import data from the student tab on the master sheet.

The student data is sent from the individual student tab on the master sheet to the secure sheet. Remember, the purpose of the secure sheet is to protect the master sheet document key.

3. Share the key from the secure sheet with students.

This step can be labor intensive, so I email the keys to my students via Google Sheets with a script (image below). The doc link below includes the script I use. It also includes an "email sent" code so the sheet can be used for new students without sending emails to every student, again.

The benefit is in the efficiency of pasting the information in one place without opening 27 emails. Plus, it's a record for future reference.

Copy this email sheet.

google scripts java

4. Provide viewing permission on the secure sheet.

The secure sheet needs viewing permission assigned for the student to which it correlates – another labor intensive step that could probably be automated with a script.

What's next?

I need to find or write the following scripts that ...
  • Make a new sheet doc for each name on a roster (I think Alice Keeler has this one or one like it.)
  • Import the document keys to a sheet (make the email step easier).
  • Automatically provide view only permission to student secure sheet. 
These steps are currently done manually, which doesn't take long. Start to finish, the security part takes 20 minutes per class, which includes double checking the work. Based on the amount of time it saves throughout the year, 20 minutes is nothing. But I want to automate as much of the process as possible. 

I am not an expert on sheet functions or Java Script, so any recommendations you can offer will be much appreciated. 

Thursday, August 3, 2017

4 Google Sheets for Classroom Management

I love Google Classroom. It's easy to set up, and the features are very accommodating. But it doesn't do a great job managing the most critical information – rosters, grades, feedback, student reflection, etc. For that, I use Sheets. 


Sheets is a tool that every teacher should use for the simple routine of modeling its use for students. When our kids leave the safety of K-12, they need to have strategies and tools for organizing information that relates to the tools that businesses and institutions have been using for decades. 

Spreadsheets are the standard for most organizations, and I don't see it changing any time soon. We've gone from VisiCalc to Google Sheets, computing in the cloud, and the columns, rows, and cells concept has not changed. 

Here are the four sheets I use and think every classroom should consider. 

1. Roster (basic info, technology survey, books)

The roster sheet is where it all starts. It's a great place to collect and maintain information about your students. This past spring was a piece a cake because I had RosterSync, which imported the roster from Google, Classroom.

My favorite sheet is the technology survey that I use every year. Actually, it changes every year, but you get the idea. I could't imagine trying to design a learning activity for a specific student without information about their technology habits, routines, and overall confidence.


2. Groups

This past year was the first time I assigned every group for cooperative learning activities, and I won't ever do it differently. The students were more productive, I could balance the achievement levels, and classroom became a more consistent cooperative learning environment.

Each student receives an index number (known only by me). The index number is determined by skill level, knowledge, sociability, and leadership qualities. It makes grouping students a quick process.

The columns are activities, and the teacher can use the comment tool to document observations. 


3. Exit Tickets (and other responses)

Sure, you could use any number of apps to manage exit tickets. It's even easy to use paper. But since my students have Google Sheets for so many things, why not remain consistent, right?

I use one Google Form for the entire course. The responses go to a master sheet where each student has a sheet with their responses (and my feedback) separated. My students have a tab at the bottom of their achievement tracking sheet where they can access their exit ticket responses and my feedback. It's all live and more efficient than some of the tools that often promise more than necessary.

Check it out.



4. Achievement Tracking

The way I do achievement tracking serves several purposes. Each student has one sheet doc that contains all of the sheets (tabs at the bottom) for the course. This makes it really easy for them to find out assignment details, check grades, write reflections, and so much more. 

As mentioned above, this is a live document concept. This means that as I add information to a sheet or as form responses are submitted, the students' sheets are updated. It's quicker than any other method of sending and receiving classroom information. 



Bonus: Open CSV exports from edtech tools with Google Sheets. Add the data to the docs you use for classroom management. There's no right or wrong way to gather and organize this information. 

If you have an idea to share about how you use sheets, please share in the comments below for everyone's benefit.   

Wednesday, June 7, 2017

Virtual Office Hours With Remind

My phone isn't the one dinging and buzzing with notifications. That would drive me crazy. I don't like Twitter badges and email is just a scary thought.


But I set Remind notifications to banners on my locked screen because my students deserve it. They deserve to be able to ask me a question while working on an assignment and get a response that hopefully enables them to persist.

Kids these days communicate differently ‒ nobody questions that. They use their phones to chat back and forth in small bits, and, consequently, they don't like waiting or listening ‒ there goes traditional schooling.

I'm not going to lie. It's tough. I often can't finish a sentence before they ask a question that would've been answered if they had just listened for another second. It sounds frustrating, but it's where they are.

Meeting kids where they are has led me to practice virtual office hours with the Remind app. It's one of three apps that I require for my classes, and it allows my students to ask questions almost anytime.

You may be thinking that email would work just fine, but it doesn't. I don't check my email more than a couple times outside of school hours, and kids are much less consistent. During school it's about once an hour or so, but after school I can be reached best via text (or Remind app).

I've been doing virtual office hours for about four years, and it's the days like today that really make it worthwhile. You see, my students have a paper due tomorrow, and several of them need clarification of expectations or help staying focused on the task of finding the evidence that support their thesis.


The text above is from a student who could be perceived as a procrastinator, unorganized, or just not a good student. Although these descriptions may be true, this student is really great at making connections between one idea or thing to another development in history. She just gets overwhelmed by the amount of work it takes to complete long term assignments and needs more help than school hours provide. 

I particularly enjoy how well she takes ownership of her choices and reflects on why she likely made that choice. It's hard to ask for help. That's why I gladly offer it through virtual office hours.

Sometimes help is a little more basic, like the example below. 


This student is making up work from medical absence. It's hard enough to keep up with school work while making up work from missed time from being hospitalized. Being there for students when they are in the mode to do the work is what makes virtual office hours so powerful. 

I use Remind, but I'm sure there are other apps or ways to facilitate communication outside of school hours. Which reminds me ... I was once told by a teacher that they don't want to be available to students outside of school. Personally, I didn't become a teacher to punch a clock. I love learning and want to fan that passion among the youth. 


Wednesday, May 24, 2017

Lessons Learned | Interdisciplinary Graduate Seminar Exposes All

In graduate school, I took a geography seminar on industrial ecology. It not only sounded cool, I had been tired of writing papers that were weighed down with philosophy. I wanted to work with numbers and do some quantitative analysis.  

The professor was an economics geographer and had a Scottish accent – a well educated one. The seminar was about seven graduate students, some working on masters degrees in environmental philosophy, while others were working on interdisciplinary studies. 

We read articles for homework, prepared positions, and discussed the concepts from the articles while exploring different cases. Most of the analysis focused on the extent to which the case established a symbiotic relationship among its firms.

I write about foundry sand waste streams and the logistical issue of reusing the sands in construction aggregates. It was fun because it was what I consider very concrete scholarship. Additionally, I learned that I could apply the research skills I was developing as an anthropology student to almost any discipline. 

More importantly, I learned how to engage in an interdisciplinary setting, which can be highly critical. What's particularly challenging is the lack of confirmation bias. When scholars of the same discipline are making observations with similarly trained lenses, it's easy to feel more confident in assumptions when new evidence is presented. Scholars without investment in an area of study sometimes offer a fresh look at the data and offer alternatives to the analysis commonly found in the discourse. This is a bit oversimplified for the purpose of this post, especially considering literature and peer reviews often look for alternatives to the published theory to avoid this issue.  

If you've read my blog, then you know that I am a classroom teacher. I am surrounded by colleagues who are not working on publishing their work. They are grading papers and planning a crazy amount of lesson material. They are managing behavior and trying to keep up appearances to avoid too many troubles ending up in the principal's inbox. This is not a scholarly situation. It's a learning factory.

Why write about it? Why even bring it up? 

Experiences like the one described above gave me the opportunity to grow as a scholar because I had to find objectivity wherever possible, just to survive. These days, this blog is my catharsis that enables new looks at common routines, avoiding falling into too many routines and patterns that become toxic in the long run (homework because we've always done it, for example).

Objectivity, unfortunately, is not something I see a lot in a high school. People do things the way they do them for 20 years and get frustrated when they have to change. I don't mind changing because I always change. If you are observing the world right now, it's changing more rapidly than ever. In fact, change is inherent in my efforts to stay relevant. I don't know any other way. 

I guess if I were told to stop changing, it may make me feel like the 20-year veterans do when they have to change. Regardless of which side of this spectrum you are on, the changing world will make you vulnerable and exposed. I embrace it, no matter how much trouble it gets me into.

Thursday, May 18, 2017

Flattening Classrooms: Collaborating With Google Apps

When James Caudill asked me if I wanted collaborate, it was an easy decision. Why not? Quite truthfully, I had never collaborated with a teacher in another state, never mind another time zone.

One of us suggested the geography of things and Google My Maps, and the rest was some direct messages on Twitter and a Google Hangout.

James (@teachcaudill) said the video reflection is spot on, which is significant because I commented mostly on his students' work, and we have yet to debrief on the data or methodology.

This video explores collaborating across time zones with Google Apps. It includes several basic and advanced tips for making products in My Maps. Additionally, skills like customizing map points are explained, as well as responsible use of other people's content.

Share your collaboration story so we can flatten our classrooms.

Thursday, May 11, 2017

10 Chrome Extensions I Won't Teach Without

Chrome extensions are right where you need them. When the time comes, it's easy to call up a tool to process what's in front of you the way you need it.


But I'm not partial to a particular developer when there are several options. My favorites are chosen based on how often I use them and how much work they can accomplish (or time saved). For some of the following tasks, the Chrome store has plenty of options.

My suggestion is to read the reviews and do your homework before choosing one over the other. But don't worry if your choice does not work out. There are plenty of others to try.

Here are some of the extensions that I use regularly.

1. Clipper

Although I prefer to use a native tool (built into the device), sometimes that's not possible. For example, I used Snagit (discontinued as extension) to clip content and add annotations, etc. Since Snagit is now only available for desktop, I use Nimbus, which also serves as a tab recorder.

2. Link Shortener

URLs can clutter emails and other documents. They're also too long and distracting for most social media platforms, like Twitter. Shortening the link only takes a couple of seconds, and your audience will appreciate the ease of use.

Since I use Google Apps, I chose a link shortener that connects to my account shortening app (goo.gl). It's nice to be able to keep a record of links and access the same link for future use.

3. Tab Recorder

Screencastify is my favorite tab recorder. I use the paid version and enjoy the simplicity of use. It places videos in my Google Drive and allows me to upload directly to YouTube.

If I need a screen recorder with more options and tools, I use Screencast-O-Matic (not an extension). I can blur student data and edit the final video with ease. Plus, I think the quality is much better. But for a quick tutorial or message, the Screencastify extension works great.

4. Keep

My juniors are currently working on a term paper and seem to be having difficulty finding sources. Perhaps they need the process (or organizational strategies) modeled with more depth.

As I search for resources and find good ones, the Google Keep extension allows me to save an article or website and apply a label to stay organized. After four or five sources are collected, I can go back to Keep and click the label to filter the sources I want to evaluate and use to formulate my argument.

5. Insert Learning

Want to insert questions into a webpage or a YouTube video into a Google doc? Insert Learning does those things and more.

Formerly called DocentEdu, Insert Learning is a tool that allows users to insert images, annotations, videos, assessment items, annotated highlights, and discussions into a webpage or published Google Doc. You have to try it to realize its value for classroom teachers.



6. PDF Printer

I use this extension as often as four or five times a week pulling materials from the Internet. It converts a webpage into a PDF and allows users to edit out images or chunks of text and formatting that are unnecessary.

If you need to pull text from the Internet and convert it to a Google doc, open the PDF with the Docs app within Drive (see below).





7. VideoNot.es

This is a great tool. It's particularly effective with short videos with excellent content. Only a couple of my students have chosen to try it, so I will use it for explanation videos to model note-taking expectations.

8. Poll Everywhere

I used to add lots of multiple choice questions to Google Slides with the Poll Everywhere extension. My students have enjoyed Kahoot!, Quizlet, and Google forms for multiple choice, so I use Poll Everywhere for the other assessment items.

We use the word cloud generator and the vote up response, and the ranking tool. I particularly like the vote up because students can read through other responses and learn from their peers. We don't do vote down because it's a waste of time. By focusing on the good responses, the expectations become clearer.

9. Drive Slides

This is one of my new favorite tools. It makes a Google Slides presentation out of all of the image files in a Drive folder. It takes about five seconds and saves a lot of time bringing together student contributed content.

Here's a post about some of things my class is doing with Drive Slides.

10. Share to Classroom

The Internet has changed the way we access information, which is why my class does not have a textbook. It does, but we don't consult it exclusively or regularly. The textbook-free classroom, however, is supported by tools like Google Classroom and the extension Share to Classroom.

My favorite feature on this extension is the ability to push content students. With a classroom full of Chromebooks, this becomes a very easy way to manage materials on the fly. Likewise, students can send material to the teacher who can then redistribute it to the students.

Share to Classroom, of course, includes its namesake feature of the ability to share something from the Internet directly to Classroom. This can be done in any of the ways posts can be made to Classroom from within the app.

Monday, April 17, 2017

Using Google Apps With OER

Docs Story Builder is one of the lesser known Google apps, yet my students took to it immediately. It's not hard to use, although it has a quirk – editing dialogue erases whatever comes after the edit. But that didn't stop us. Students planned out the dialogues before entering them into the app.


To model the expectations, my explanation for the changing views of war was done with Docs Story Builder. I framed it as a fictional, yet plausible, dialogue among three of their favorite teachers.

Using Open Educational Resources (OER)

This idea all started when I realized that my students needed to build Web literacy skills, and I wasn't designing activities that supported this need. Since the next lesson was about World War I, I decided to use one of the Library of Congress exhibits that compiled firsthand accounts from soldiers in the trenches.

Students chose two memoirs and made a list of facts and opinions from the first page(s) of each. The notes were analyzed for similarities and differences to provide some inspiration for a fictional, plausible dialogue.

Here's my list of resources. If you know of any that you think should be on the list, add the link to the comments below and I'll check it out.

Story Builder

Like many Google Apps, Story Builder is intuitive and provides enough explanation to get in and out of trouble. As I mentioned above, the process can be frustrating. Planning the dialogue in advance is advised, which is a routine our students should practice, anyway.

The business end of the dialogue writing is shown below. When the "write story" button is selected, it takes you to a screen to add the dialogue. This is also where music can be added by selecting the appropriate button. Please note, however, that the "add music" button must be selected to complete the story. If you choose not to add music, an option will available. 


Google Drive Classwork Flow

Our class uses Google Drive folders to share work. I like this way of organizing workflow because it reduces the amount of times we have to share documents. This file share folder is accessible through Google Classroom to make it even easier.


Students accessed a Google Doc to paste their link. If the link isn't saved somewhere, it's lost. The image below shows the doc with the links. I guess this could be shared via Padlet or something fancier than Docs, but I like to keep things simple and less shiny.  


The Benefits

My students will probably never forget about the conditions soldiers endured in the WWI trenches. They will also remember that trenches were WWI, not WWII. I'm confident of this because of the balance of activity that occurred during this project. 

They laughed and had fun writing the dialogues, which always means better learning – laughing is learning. But the best result was the routine of using the Library of Congress to access information. As the course progresses, more OER is used and more student choice and responsibility is exercised.

Lastly, from a tech integration point of view, this activity is quick and easy to execute in a Google Apps classroom. I love easy tools that don't require new accounts. Otherwise, the tech can distract from the learning process. 

Student Work

Click here (or the image below) to see some of the student work samples.

 Click Image