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.