It seems Google has deprecated several functions in this script, meaning it may not work. Since I am neither a programmer nor the original author of this script, and since I no longer use it, I cannot offer any support. However, you are welcome to attempt to get it working (I recommend StackExchange for seeking help), and, if you do find a solution that works, I’d be more than happy to update my post again with the proper script. Having said all this, I may take a closer look this winter break and see if I can modify it to work.
I have made an amazing little gradebook on Google Spreadsheets which allows students to view their individual grades in realtime. This means students click on my gradebook link and are taken directly to a page that shows only their grades. Any change I make in my gradebook will be reflected in their grade report automatically when they refresh or visit again.
Numerous people have already written and demonstrated how Google Sheets can be used as a gradebook for teachers, but most (if not all) of these gradebooks do not or cannot allow students to view only their grades. Instead they must view a whole roster’s worth of grades, which can be confusing, disorganized, and frightening if their names are there or other students know their student numbers. Using Google Spreadsheets, Google Apps Script and help from a super nice gentleman on StackOverflow, my students now have the ability to view their individual grades. They get a detailed report too, which includes their overall grade, attendance and participation breakdown, and notes. All this is easily customizable and I intend on explaining how you can make your own below.
- This script only works when students are logged into Google. Therefore, you must use their Gmail address or an email address connected to Google.
- Your Google Spreadsheet must be made public (shared as “Anyone who has the link”) but is still secure because the link is hidden in the script and is in no way accessible to users.
Step 1: The Gradebook
There are hundreds of different gradebook options. Any of them would work with my gradebook app. I used a modified version of the weighted points gradebook here. This is a very powerful gradebook and easily converts to Google Sheets (simply upload it to your drive, right-click, and choose Open With -> Google Sheets). For some other examples, you can see this webinar or browse the countless templates from the Google Template Gallery. Any gradebook is fine, so long as it suits your needs.
Step 2: The “StudentGrades” Sheet
In my gradebook I have the following sheets “Gradebook” for overall grades, exams, and projects, “Attendance”, “Participation”, and “Names” for student names, numbers, and email address. I also have a sheet called “StudentGrades” which combines all these sheets into one meta-sheet.
Thescript pulls individual student data from a single meta-sheet. This would be a sheet that combines overall grades, assignments, attendance, participation, etc. The stuff you normally have separate sheets for. You would still have separate sheets for them, only they become combined in a super-sheet called “StudentGrades” (or whatever you choose to name it).
Normally, grade data is laid out in rows. This script looks at data in columns. This isn’t a problem. You can use the TRANSPOSE function to copy rows from other sheets and change them into columns in the “StudentGrades” sheet.
The first column is for labels such as “Email”, “Name”, “Attendance”, etc. You can style this manually, or pull in data using TRANSPOSE. The remaining columns will be for students. One student per column. Student emails must be included in the first row of each column. The following video will explain how to create the meta-sheet. (Note: once you make your meta-sheet, you still need to follow the instructions in Step 3 below.)
Step 3: The Script
In Google Sheets, select Tools -> Script Editor and then copy and paste the code from here (updated 3/26/14). Be sure to change the spreadsheet key. In addition, read through the code comments (they begin as two slashes //) so you understand some things you can change.
Before the code can be used, it must be saved as a version. Go to File -> Manage Versions and enter in a number or name in the textbox. Then go to Publish -> Deploy as web app… Select the version, choose “User accessing the web app” under Execute, and “Anyone” under Access. Then hit Update. You will need to save a new version and re-deploy for any changes you make after a deployment. Test out your gradebook. Note: your email should be included in one of the columns for testing purposes.
Step 4: Notes, Bugs and Annoyances
- Make sure your spreadsheet is shared as “Anyone with link”, but don’t worry – it is still secure.
- If anyone has a fix for this, please let me know!
- In the “StudentGrades” meta-sheet, you may need to format numbers in different ways until you get it to look the way you want. Sometimes the student report shows numbers with an extra “/ 20” on them unless they are in Plain Text. Go to Format -> Number.
- When first using this web app, students will see a warning page that asks them to authorize the app. As far as I know, there is no way to avoid the warning. But, it should only appear once.
- If you find any other bugs or annoyances, please let me know in the comments!