Scoring Mock AP Exams with Google Drive and JavaScript

One of the biggest pains about giving mock AP exams to students is converting their scores on the mock exam to something on the AP scale. There are many scales posted for individual practice exams, so I usually just pick one. For this year, a score of 62 or better (out of 80) qualified as a five on the AP scale, while greater than 47 was a four.

Usually I keep my grading sheets in a Google Drive spreadsheet which makes computing class averages, medians and standard deviations super simple. Unfortunately, converting from a raw score to this AP scale proved to be trickier than I would have liked. Case in point, if I wanted to write a spreadsheet 'formula' for this conversion, it would look something like this:

=IF(H2 > 62, 5,IF(H2, > 47, 4, IF(H2 > 37, 3,...)))

This is an ugly solution to a really simple problem. I've truncated the rest of the formula to save some space and avoid going through matching parenthesis hell for a second time. Even worse, it is not immediately clear what this function is doing. The long string of if...else statements makes it more difficult to follow what is happening than should be necessary.

Enter JavaScript

Luckily Google Drive provides its own scripting environment - a flavor of JavaScript once known as Google AppScript. At its most basic, this scripting environment allows users to create their own functions. However, it is also robust enough to support full-fledged applications that leverage the infrastructure of Drive. Cool stuff. Anyway, I found it was much easier to write a user defined function called APScoring() that made it much easier to read my spreadsheet and debug my code.

function APScoring(raw_score) {
/**
* Computes the AP Score for a given range of values
*/
if (raw_score >= 62) 
  val = 5;
else if (raw_score >=47)
  val = 4;
else if (raw_score >= 37)
  val = 3;
else if (raw_score >= 27)
  val = 2;
else
  val = 1;
return val;
};

The function accepts a number as input, then compares it to boundary values for AP Scoring of a certain exam. That's it! Now, instead of an ugly string of if...else statements in my spreadsheet (repeated for every student), I have encapsulated this in my very own function which I can call like any other spreadsheet function. That is, I can call it by using its name and passing it a value through a cell reference. So easy and so much cleaner.

This is a quick-and-dirty solution to be sure, and there are numerous ways to improve it. For example, it would be fairly simple to alter the function to accept an array of values corresponding to the boundaries between scoring levels. That would make it a bit more flexible by allowing users to specify the criteria for each AP score. As it is, it worked nicely for me.

In summary, Google's scripting environment for Drive makes it easy to automate and/or simplify tasks that are cumbersome through the traditional spreadsheet interface.