google scripts sidebar that makes it easier to enter grades into a spreadsheet

readme.md

#Speed Grader ##Remove all the needless clicking around and searching for students in a spreadsheet

I felt I needed to remove the needless clicking and tabbing around my spreadsheet that is involved with entering grades. So, this is my solution. It allows you to select an assignment from a dropdown list. This dropdown is populated by the header row in your spreadsheet. Then you can tab back and forth between the 'Student Search' and the 'Grade' input boxes. When you are done, simply close the sidebar!

The only bit of customization that is needed is:

1. On line 3 you need to set the name of the file that the sidebar html is in.
2. Line 15 is the number of the column where your assignments start. The app will identify this column and all the columns to the right as assignments that can be graded. Change this number to whatever column your student information starts on.

easy-grader.gs

function gradeBar() {
  var gradeBar = HtmlService
    .createHtmlOutputFromFile('sidebar')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setTitle('Grader');
    
  SpreadsheetApp.getUi().showSidebar(gradeBar);
}

function endColumn() {
  Logger.log(SpreadsheetApp.getActiveSheet().getDataRange().getLastColumn());
}

function getAssignments() {
  var startColumn = 11; //this is the column to start pulling assignments from
  var endColumn = SpreadsheetApp.getActiveSheet().getDataRange().getLastColumn();
  
  var rowTitles = SpreadsheetApp.getActiveSheet().getRange(1, startColumn, 1, endColumn)
    .getValues()
    .reduce(function(first, second) {return first.concat(second);})
    .filter(function(each) {return each;});
  
  var assignments = {};
  for(i = 0; i < (rowTitles.length); i++) {
    assignments[startColumn + i] = rowTitles[i];
  }
  
  return assignments;
}

function setGrade(student, assignment, grade) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var snColumn = 1;
  var maxRows = sheet.getLastRow();
  
  var snValues = sheet.getRange(1, snColumn, maxRows).getValues()
    .reduce(function(first, second) {return first.concat(second);});
  
  var studentRow = snValues.indexOf(parseInt(student)) + 1;
  
  //set the value of the correct cell
  sheet.getDataRange().getCell(studentRow, assignment).setValue(grade);
}

sidebar.html

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.css">

<!-- main html -->
<div style="padding: 5px;">
  <form id="gradeForm">
  
    <div class="block form-group">
      <label for="assignments"><b>Choose assignment to grade</b></label>
      <select id="assignments"></select>
    </div>
    
    <div class="block form-group">
      <label for="tags"><b>Search for student name...</b></label>
      <input type="text" class="formInput firstTab" id="tags">
    </div>
    
    <div class="block form-group">
      <label for="grade"><b>Grade to post...</b></label>
      <input type="text" class="formInput secondTab" id="grade">
    </div>
    
    <div class="block form-group">
      <button type="submit" class="action" id="postGrade">Post grade</button>
    </div>
   
  </form>
</div>

<!-- script tags -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
<script>
$(document).ready(function() {
  
  //get the student names from the spreadsheet for fuzzy search
  google.script.run.withSuccessHandler(function(availableTags) {
    $('#tags').autocomplete({
      source: availableTags,
      autoFocus: true
    });
  }).getAvailableTags();
  
  
  //get the names of the assignments in the gradebook
  google.script.run
    .withSuccessHandler(function(assignments) {
      $.each(assignments, function(key, val) {
        $('#assignments').append('<option value="' + key + '">' + val + '</option>');
      });
    })
    .getAssignments();
    
  $('form').submit(function() {
    var grade = $('#grade').val();
    var assignment = $('#assignments').val();
    var student = $('#tags').val().split(' ')[0];
    
    google.script.run
      .withSuccessHandler(function() {
        //refocus the box on the student box
        $('#tags').val('');
        $('#grade').val('');
        $('.firstTab').focus();
      })
      .setGrade(student, assignment, grade);
      
      
    return false; //prevent the default form submit
  });
  
  $('.formInput').keydown(function(e) {
    if(e.which === 9) {
      e.preventDefault();
      if($(this).hasClass('firstTab')) {
        $('.secondTab').focus();
      } else {
        $('.firstTab').focus();
      }
    }
  });
});
</script>