Miscellaneous‎ > ‎

Gradebook Web App

As a teacher I find using spreadsheets to keep track of student progress/grades very useful. They are infinitely more flexible than the programs designed for tracking grades, especially the one used by our school district. Using a spreadsheet allows me to:
  • Easily give students choice over which assignments they complete.
  • Differentiate and allow experienced students to skip easier assignments.
  • Quickly and easily adjust my expectations for students with IEPs.
  • Quickly import and manipulate data from a variety of websites.
Unfortunately, using a conventional spreadsheet makes communicating grades to my students impossible. However, by attaching a simple web app to a Google sheet, I can allow each user to view their grades (and only their grades) in minute detail in almost real time.

For those of you who are not programmers, you should probably work through at least a portion of the following Khan Academy courses:

Gradebook v1
To get started, work through my video tutorial: How to Create a Simple Gradebook Web App. I've posted my code below, but you'll need to watch the video to understand how it works and how to use it.

Code.gs file:
function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function getGradesForUser() {
  var email = Session.getActiveUser().getEmail();
  var ss = SpreadsheetApp.openById('1DHIy66wnnKzWcGGKJYS2KUnj89sBA3aWSqxNCbiaYps');
  var users = ss.getRangeByName('Users').getValues();
 
  var row = 0;
  while (users[row][0] !== email)
  {
    row++;
    if(row === users.length)
      return 'Student ID not found. Did you log in with your school account? If not, log out of any Google account and log in with your school account.';
  }
  row++; //Arrays are zero indexed
 
  var html = '<table>';
 
  var assignments = ss.getRangeByName('Assignments').getValues();
  var gradebook = ss.getSheetByName('Gradebook');
  var scores = gradebook.getRange(row,1,1,assignments[0].length).getValues();
 
  for(var i = 0, len = assignments[0].length;i < len;i++) {
    html += '<tr><td>' + assignments[0][i] + '</td><td>' + scores[0][i] + '</td></td>';
  }
 
  html += '</table>';
 
  return html;
}

index.html file:
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      table, th, td {border: 1px solid black;}
      td {padding: 5px;}
      td:first-child + td{text-align: center}
    </style>
    <script>
     function onGetGradesSuccess(gradesTable) {
       document.getElementById('output').innerHTML = gradesTable;
     }
    </script>
  </head>
  <body onload='google.script.run.withSuccessHandler(onGetGradesSuccess).getGradesForUser()'>
    <div id='output'>Loading...</div>
  </body>
</html>

Now you have all the tools of a spreadsheet at your disposal when grading. You can filter data, you can find a specific student quickly by using Ctrl+F. You can use vlookup functions to extract data imported from other websites. The possibilities are endless.

Gradebook v2
In addition to tracking progress/grades, you can also use your gradebook spreadsheet to track behaviors, such as whether students come to class prepared. Start by creating a Google Form. Make sure to check the "Collect email address" option.

Examples
Here's an app I made for my school district's tech expo using a Google spreadsheet: Mat-Su Tech Expo
Comments