Friday, January 21, 2011

Using JavaScript Objects In Google Spreadsheets

The power of JavaScript objects is belied by the very simple syntax required for their declaration:
var my_object = {};
That is all that is required to create a JavaScript object using the object literal syntax.  There is also a Java-like syntax for creating objects that uses the new operator but, following Douglas Crockford’s advice in his book “JavaScript: The Good Parts” this syntax is not used here.  Apart from making JavaScript look a little more like a conventional object oriented language, it appears to offer no advantages over the object literal syntax.
Objects are simply collections of colon-separated key/value pairs with key-value entries separated from each other by commas.  Objects can be arbitrarily complex storing values ranging from simple scalars to other objects.  Objects can also store functions where functions attached to objects in this way are known as methods.  This storage of data together with the functions that operate on that data is reminiscent of classes in more conventional object oriented languages.
To demonstrate some of the power of objects, consider the following frequently encountered scenario:
  • How can one count the occurrences of distinct cell value in an input range?  
  • A secondary consideration might be how to filter out just those cell values that occur more than a certain number of times in an an input range.

In Excel, this can be done easily with pivot tables while in a database one can employ the COUNT() function in conjunction with a GROUP BY clause.  Scripting languages such as Perl and Python have built-in objects known as hashes and dictionaries, respectively, to perform this sort of task.   Excel VBA can be extended to include a dictionary object by referencing the Microsoft Scripting Runtime library through the Tools|References menu in the VBA editor.
Regardless of language/environment, this is a typical data reduction task and JavaScript objects can be put to work to do it.

Counting Occurrences Of Values
Previously JavaScript arrays were used to produce a unique list from an input range.  This technique relied on the JavaScript indexOf() method to check an array for value membership.  But what if one wants a list of elements and their counts?  Here is one way to achieve this using objects.

To run the following code as is:
  1. Create a Google spreadsheet
  2. Re-name or create a sheet and call it “input”
  3. Add some values to range “A1:A10” of the sheet “input”.  Make sure there are some repeated values to see how the JavaScript code counts them.
  4. Paste the code into the JavaScript editor and run function “test_getCountValues()”

The code adds a new sheet called “output” and writes the output into columns A and B of the is sheet starting in the first cell “A1”.  The code comments should clarify what is going on

// Test for function "getCountValues()".
// Need to define an input and an output range.
// Create an object with the cell value-occurrence counts by calling "getCountValues()".
// Use the enumeration "for .. in loop" to extract each object key and its associated value.
// Write the key and count values to output.
function test_getCountValues() {
   // Define an input range with values of some kind in it.
   var test_range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("input").getRange("A1:A10");
   // Pass the input range as an argument to function "getCountValues()" and
  // capture its return object.
   var count_values = getCountValues(test_range);
   // Add a new sheet for output and store a reference to the sheet object object.
   var new_sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("output");
   // Create a range object of one cell in the new sheet for output.
   var output_range = new_sheet.getRange("A1");
   // Set a row counter for output.
   var row_counter = 0;
   // Using the for ... in loop, extract the key/value pairs from the object and
  // write them to output.
   for  ( var key in count_values ) {
       var value_count = count_values[key];
       // Write the unique cell values with their counts.
       output_range.offset(row_counter, 0).setValue(key);
       output_range.offset(row_counter, 1).setValue(value_count);
       // Increment the row counter for the next iteration of the for .. in loop.
       row_counter += 1;

// Take a range object as a single argument for the parameter named "range_input"
// Return an object where the keys are the unique cell values and their associated
// values are counts of the number of times they occur in the input range.
function getCountValues( range_input ) {
   // Create an object to store the unique cell values and their occurrence count.
   var values_counts = {};
   // Process the input range in a nested for loop using the outer and inner counter
   // variables (respectively, i and j) as arguments to the range "getCell()" method.
   for( var i = 1, row_count = range_input.getNumRows(); i <= row_count; i++ ) {
       for( var j = 1, col_count = range_input.getNumColumns(); j <= col_count; j++ ) {
           // Extract the value for a cell with row and column coordinates i and j respectively.
           var cell_value = range_input.getCell(i,j).getValue();
         // Check if this cell value is already in the object:
           if( values_counts[cell_value] ) {
               // If it is increment the value for which it is the key.
               values_counts[cell_value] += 1;
           } else {
               // If it is not in the object, add it, and set they value it is key for to 1.
               values_counts[cell_value] = 1;
   return values_counts;


​Code Notes
  • The code above demonstrates a simple but useful application of JavaScript objects to spreadsheet programming.
  • It introduces the loop to enumerate the keys.  The standard for loop is used to iterate over arrays but the loop is required for objects.
  • It is important to realise that JavaScript objects are not dictionaries or hashes in the Perl/Python sense but they do support some of the key characteristics of these data structures.  The important one for this application is the ability to test for membership.
  • The code above only scratches the surface of what JavaScript objects are capable of.  They are extremely flexible and future posts will try to convey their power and applicability to spreadsheet programming.


  1. This comment has been removed by a blog administrator.

  2. This comment has been removed by a blog administrator.