Macro to "grade" a set of text answers in a spreadsheet

OpenOfficeCheckAnswers.js

// https://wiki.openoffice.org/wiki/Documentation/DevGuide/Scripting/Using_the_Scripting_Framework
// 
// Check OpenOffice speadsheet text cells for similarity to a provided set of answers
// Will Sahatdjian - 12-29-2015
//
// Based on original work by:
// Copyleft 2010 by Kas Thomas
// http://asserttrue.blogspot.com/

// !!! CHANGE THESE PARAMETERS !!!!

var ANSWER_FIRST_ROW_NUMBER = 5;  //skip first 5 rows because they aren't answers
var ANSWER_COLUMN_NUMBER = 0; //zero-based position.  3rd column would equal 2

var ANSWER_KEY = [] {
  "Perfect sentence number one",
  "Perfect sentence number two",
  "Perfect sentence number three",
  "Perfect sentence number four",
  "I plead tha fizzif"
};

// !!! NO CHANGES NECESSARY AFTER THIS POINT !!!!

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);

// go thru the sheet one row at a time
// and collect cell data for answer rows
// check Levenshtein distance to compute "error rate"
function harvestCells( sheet, rows, columns ) {
   var masterArray = [];
   for (var i = 0; i < rows; i++) {

       var answer_number = i - ANSWER_FIRST_ROW_NUMBER;

       if(answer_number < ANSWER_FIRST_ROW_NUMBER || answer_number >= ANSWER_KEY.length) {
        continue;
       }

       var cell = sheet.getObject().getCellByPosition( ANSWER_COLUMN_NUMBER,i );
       var content = cell.getFormula();

       var answer = ANSWER_KEY[answer_number];
       var errors = getEditDistance(answer, content);
       var accuracy = Math.round(accuracy(answer, content) * 100);

       masterArray.push("EXPECTED: \"" + answer + "\" | ACTUAL: \"" + content + "\" | ERRORS: " + errors + " | ACCURACY: " + accuracy);
   }

   return masterArray;
} // harvestCells()

// Levenshtein
// https://gist.github.com/andrei-m/982927
function getEditDistance(a, b){
  if(a.length == 0) return b.length; 
  if(b.length == 0) return a.length; 

  var matrix = [];

  // increment along the first column of each row
  var i;
  for(i = 0; i <= b.length; i++){
    matrix[i] = [i];
  }

  // increment each column in the first row
  var j;
  for(j = 0; j <= a.length; j++){
    matrix[0][j] = j;
  }

  // Fill in the rest of the matrix
  for(i = 1; i <= b.length; i++){
    for(j = 1; j <= a.length; j++){
      if(b.charAt(i-1) == a.charAt(j-1)){
        matrix[i][j] = matrix[i-1][j-1];
      } else {
        matrix[i][j] = Math.min(matrix[i-1][j-1] + 1, // substitution
                                Math.min(matrix[i][j-1] + 1, // insertion
                                         matrix[i-1][j] + 1)); // deletion
      }
    }
  }

  return matrix[b.length][a.length];
};

// Jaro-Winkler
// https://github.com/zdyn/jaro-winkler-js
function accuracy(string1, string2) {
  var ch, i, j, jaro, k, l, len, len1, len2, m, matchWindow, n, numMatches, prefix, ref, ref1, ref2, string1Matches, string2Matches, transpositions, windowEnd, windowStart;
  if (string1.length > string2.length) {
    ref = [string2, string1], string1 = ref[0], string2 = ref[1];
  }
  matchWindow = ~~Math.max(0, string2.length / 2 - 1);
  string1Matches = [];
  string2Matches = [];
  for (i = k = 0, len = string1.length; k < len; i = ++k) {
    ch = string1[i];
    windowStart = Math.max(0, i - matchWindow);
    windowEnd = Math.min(i + matchWindow + 1, string2.length);
    for (j = l = ref1 = windowStart, ref2 = windowEnd; ref1 <= ref2 ? l < ref2 : l > ref2; j = ref1 <= ref2 ? ++l : --l) {
      if ((string2Matches[j] == null) && ch === string2[j]) {
        string1Matches[i] = ch;
        string2Matches[j] = string2[j];
        break;
      }
    }
  }
  string1Matches = string1Matches.join("");
  string2Matches = string2Matches.join("");
  numMatches = string1Matches.length;
  if (!numMatches) {
    return 0;
  }
  transpositions = 0;
  for (i = m = 0, len1 = string1Matches.length; m < len1; i = ++m) {
    ch = string1Matches[i];
    if (ch !== string2Matches[i]) {
      transpositions++;
    }
  }
  prefix = 0;
  for (i = n = 0, len2 = string1.length; n < len2; i = ++n) {
    ch = string1[i];
    if (ch === string2[i]) {
      prefix++;
    } else {
      break;
    }
  }
  jaro = ((numMatches / string1.length) + (numMatches / string2.length) + (numMatches - ~~(transpositions / 2)) / numMatches) / 3.0;
  return jaro + Math.min(prefix, 4) * 0.1 * (1 - jaro);
};

// a Swing UI for displaying the data
function EditorPane( ) {

   Swing = Packages.javax.swing;
   this.pane = new Swing.JEditorPane("text/html","" );
   this.jframe = new Swing.JFrame( );
   this.jframe.setBounds( 100,100,500,400 );
   var editorScrollPane = new Swing.JScrollPane(this.pane);
   editorScrollPane.setVerticalScrollBarPolicy(
   Swing.JScrollPane.VERTICAL_SCROLLBAR_ALWAYS);
   editorScrollPane.setPreferredSize(new java.awt.Dimension(150, 150));
   editorScrollPane.setMinimumSize(new java.awt.Dimension(10, 10));
   this.jframe.setVisible( true );
   this.jframe.getContentPane().add( editorScrollPane );

   // public methods
   this.getPane = function( ) { return this.pane; }
   this.getJFrame = function( ) { return this.jframe; }
}

( function main( ) {

   //get the document object from the scripting context
   oDoc = XSCRIPTCONTEXT.getDocument();

   //get the XSpreadsheetDocument interface from the document
   xSDoc = UnoRuntime.queryInterface(XSpreadsheetDocument, oDoc);

   // get a reference to the sheets for this doc
   var sheets = xSDoc.getSheets();

   // get Sheet1
   var sheet1 = sheets.getByName("Sheet1");

   // construct a new EditorPane
   var editor = new EditorPane( );
   var pane = editor.getPane( );

   // harvest cell data (from sheet, rows, cols)
   var masterArray =  harvestCells( sheet1, 100,8 );

   // display the data
   var text =  masterArray.join("\n") ;
   pane.setText( text  );
})();