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. 

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.