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.

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

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