The Ultimate Google Gradebook (with Individual Score Reports) – updated

(Updated 11/10/2016)

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.

(Updated 3/26/2014)

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.

Caveats

  1. This script only works when students are logged into Google. Therefore, you must use their Gmail address or an email address connected to Google.
  2. 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.

=TRANSPONSE(A1:H7)

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.

Get the script here.

Step 4: Notes, Bugs and Annoyances

  1. 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!
  2. 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.
  3. 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.
  4. If you find any other bugs or annoyances, please let me know in the comments!

 

29 thoughts on “The Ultimate Google Gradebook (with Individual Score Reports) – updated

  1. Sarah Campbell says:

    Hi Anthony!

    I really love this idea and have gotten pretty far in setting it up! I have hit a wall though and was wondering if you hit it too or if you had a way around it.

    I teach 7 different classes and I would like them each to be able to log in to see their grades – do you know if I have to create a different spreadsheet for each of the classes? I can’t put them all on one metasheet (StudentGrades) because of the limit to rows and columns. I tried creating different meta-sheets but I can only seem to have the script run off one of them.

    Thanks!
    Sarah

    • Anthony Teacher says:

      Hi! I teach three different courses for a total of 5 classes. Each class has its own gradebook/spreadsheet, so I have 5 different instances of the script. If you go to http://www.anthonyteacher.com/gradebook, students simply select their class to go to their gradebook. I believe this is the only way to do it. I find that keeping separate spreadhseets makes each class much easier to organize, especially if different classes receive different homework assignments for whatever reason.

      That being said, how many students do you have? Google’s limits for columns is 256. Do you have more than 255 students? Also, their max cell count is 400,000, which is about 1200 rows.

      I hope the script works well for you. Please let me know how you decide to use it!

  2. Katherine Trueman says:

    Hi Anthony

    I really like the idea of “opening” my gradebook to my students (my school does not have any online grade program). I am having one issue though; I cannot seem to share the correct link with my students. They keep getting the “bad error” or the “Request access…” error. When I test the script using my email, all is fine. Which document, link do I share?

    Thanks.

    Katherine

    • Anthony Teacher says:

      I have had this error before. I can’t remember exactly why it occurs, but I think it means that your gradebook spreadsheet is not shared. Make sure that it is shared publicly. Don’t worry about students or strangers accessing it. It’s impossible for them to get the spreadhseet address. They only see the webapp address. If this doesn’t work, please let me know.

      By the way, I recommend adding yourself to gradebook to test that it works before giving it to the students.

  3. Zark Strasburger says:

    Hi Anthony,
    Finding your tutorial is like finding the Holy Grail (almost). I have been looking for a solution like this for years. I have everything set up and have worked out most of the kinks. The script works for all of my test email accounts, but doesn’t work for any of my students. They are all getting an error that reads: “Your results are not available or your instructor has the wrong email address. Please contact your instructor ASAP.”
    Do you have any ideas of how to fix this? My school uses gmail for education for all faculty and student email. I have gotten the script to work on accounts that are both within and outside the school’s email domain. I am wondering if it is possible that the google group that my students belong to doesn’t permit some of the functionality required to make the script and sheets function properly. Thanks for any advise you can give. I really want this to work.

    • Anthony Teacher says:

      You need to set the sheet’s settings to be viewable to anyone with the link. Don’t worry, as it is impossible to actually get the link from the script. But, this allows students to see the sheet.

      Please don’t share it with a Google Group or individual students, as that allows them to view the entire gradebook (and thus all students geades).

      • Meridith says:

        Hi I agree with Zark, your addon is amazing. I have duplicated this project for 3 classes and it works. I love it I am about to trial it with year 5.

        I have 3 students who are receiving the error “Your results are not available or your instructor has the wrong email address. Please contact your instructor ASAP.” One student has a hyphenated email, the rest do not. I have reentered the email addresses and had students double check the spelling, checked sharing permissions. Any ideas for trouble shooting.

        Regards
        Meridith

        • Anthony Schmidt says:

          The script is case sensitive, so they should make sure they are entering if with the same case as you have. Also, if they are using another account connected to their gmail, make sure you list the main gmail spreadsheet.

        • Carrie says:

          My students are getting this error: Your results are not available or your instructor has the wrong email address. Please contact your instructor ASAP. AND the sheet is shared so anyone with a link can view.

          • Anthony Schmidt says:

            Hi. I looked at your sheet, included my email address, and tested your app. It worked fine for me. I logged in with a gmail address. My suggestion is to inquire about case sensitivity and making sure they are logging in to Google with their school IDs. They absolutely must be logging in with their school IDs via google.

  4. wademexico says:

    Hi. Just wondering if you had another site for the code I need to make your gradebook work. Pastebin seems to be under attack.

  5. Timpano says:

    Thanks for your post I have been looking for a solution to this problem for a while. I have completed all the steps in your tutorial but continue to get an error at the same script line 29;

    var ss = SpreadsheetApp.openById(‘[INSERT SPREADHSEET KEY HERE]’);//Put your spreadsheet key here

    Exactly what information do I include as my Spreadsheet Key and do I keep the brackets at the beginning and end of it?

    Thanks in advance for this concern. My students will love this if I can get it working!

  6. Dana Morris says:

    Not sure if you are still answering help questions, but I really enjoyed reading your blog. I am actually using your technique for the college course I teach. I downloaded the gradebook you mentioned that you had as a link. I put in all the grades and points. I am mathematically challenged, but I was wondering if there was a way to weight the grades differently. For instance, all of the homework assignments, class work and quizzes would be 10%, all responses would be 20% and so on. Any ideas on how I can do this or a similar technique. THANK YOU!

  7. Kate Byars says:

    Hey Anthony – I got my Gradebook put together, just like you suggest. My only question: how do you test that it’s working and what link do you send to students?

    • Anthony Schmidt says:

      Test it by using your email address in place of a students. When you set up the script and publish it, there is a URL given to you. Use that URL. I would suggest using http://goo.gl to shorten the URL for students.

  8. Heather says:

    Fabulous concept and great instructions here. Thank you for this post. I am, however, having a similar issue to one of the commenters above – I pasted in the script and replaced the spreadsheet key between the brackets. When I attempt to run the app, however, I receive the error notice: “Bad value (line 28, file “Code”, project “Your Grades”).” Any suggestions would be most appreciated!

  9. Carrie says:

    I am loving my gradebook. One little issue yet is that the overall percentage is coming as 1 instead of the actual percentage on the student’s report. I have dragged over the row and selected percentage and it looks like a percentage on the gradebook page.

    • Anthony Schmidt says:

      This has to do with the way the cell is formatted in Sheets. Just keep changing the formatting until it suits you.

  10. Cory says:

    Wow. Nobody has commented in a while… but wow. This is AWESOME! And your instructions are great. Took a few re-reads, but thanks so much! I will share this with many colleagues. =)

  11. Ross says:

    Hello Anthony,

    This web app is the solution that I have been looking for with using Google Sheets as a grade book. The only issue that I am having is that when the students access their grades, it is reflecting their averages as decimals instead of percentages. I have formatted them as percentages twice now, but through the web app, they are still showing as decimals. Any assistance would be greatly appreciated.

    • Anthony Schmidt says:

      You will have to change something on Line 61, I think.
      The way I deal with this is to make sure everything is decimals on Google Sheets (usually by *100 in the formula) and this prevents me from messing with the code. Hope that helps!

  12. Scotty says:

    I am sorry Anthony. Perhaps I am tired, but where is the link to open this seemingly amazing spreadsheet you have created? I would love to use it beginning next year!

    • Anthony Schmidt says:

      My post contains numerous links. I don’t think I ever actually included my spreadsheet. Instead, I show you how to make your own.

Comments are closed.