More On JavaScript Arrays

The previous post concentrated on the range methods getValues() and setValues() that can be used to read range values into JavaScript arrays and return JavaScript array values to ranges, respectively.  But JavaScript arrays are extremely useful objects in their own right and come with a large number of methods.  For spreadsheet programming, Excel VBA arrays appear limited and inflexible in comparison.  When studying JavaScript arrays, it is advisable to use up-to-date sources that cover at least version 1.6 of the language since new and useful methods have been added to them.  Older sources often give example code showing how to test an array for element membership by looping through the array, for example.  But JavaScript arrays since version 1.6 have supported two methods that can perform this useful task, namely, indexOf() and lastIndexOf().  These methods behave similarly to string objects of the same name where they are used to locate sub-strings.   Two other useful methods are map() and filter() that can be used return new lists where map() applies a function to each element and filter() selects elements based on user-specified criteria.  All four of these JavaScript 1.6 array methods indexOf(), lastIndexOf(), map(), and filter(), are available in Google App Scripting.  A few examples showing how these methods can be used to good effect are given below.

Ensure Element Uniqueness In A JavaScript Array

// Demonstration of array methods.

// Use Browser.inputBox() in a do loop to repeatedly prompt for a value to add to an array.

// Only add the value if it does not already exist in the array.

// If it does already exist, display a message to indicate this.

// Exit the loop when the 'cancel' button is pressed.

// Display the list of unique elements.

function noDuplicatesArray() {

   // Declare an empty array to hosld the unique list

   var unique_elements = [];

   var element;

 

   // Set up a loop to repeatly prompt for values.

   do {

       // Capture the text entered to a variable.

       element = Browser.inputBox("No Duplicates List",

                                  "Enter an element or cancel",

                                  Browser.Buttons.OK_CANCEL);

       // Exit loop when 'cancel' button is pressed.

       if ( element === 'cancel' ) { break; }

       // Check if the entered element is already in the array using indexOf().

       if( unique_elements.indexOf(element) > -1  ){

           // Alert the user if the element has already been added to the list.

           Browser.msgBox("Element " + element + " already added!");

       } else {

           // If the element is not already in the list add it to the end of the array

           // using the push() method

           unique_elements.push(element);

       }

     // Loop while 'element' is true, entering an empty string will cause the loop to terminate.

   } while ( element );

   

   // Display the elements as a string by using the array join

   Browser.msgBox(unique_elements.join(', '));

}

Code Notes

• When invoked, the function “noDuplicatesArray()” repeatedly prompts for user entry and only exits when either the input dialog ‘cancel’ button is pressed or an empty string is entered (JavaScript treats empty strings as false!).
• If an attempt is made to add an element already in the array, it is rejected and the MsgBox dialog is displayed to that effect.
• If the newly entered element is not already in the array, it is added using the array push() method which adds the new element to the end of the array.
• The key method is indexOf(), it returns -1 if its argument is not already in the array, lastIndexOf() could also have been used and would yield identical results.

Generating A Sub-Set Of An Input Array Using The filter() Method

// Apply a filter to the test array that uses an in-line function

// that is applied to each element returning true for elements passing

// the test, false otherwise.  Only elements for which the test is

// true are returned to the new array.

function test_filter() {

   // Create and populate an array object with test data.

   var array = ['rat', 'cat', 'cow', 'horse', 'dog', 'mouse', 'pig'];

   // Return only elements with less than four characters to the new array.

   var short_names = array.filter( function( element ) {

                                       return element.length < 4 ? true : false; } );

   Browser.msgBox(short_names);

}

Code Notes

• Using one array as input to generate a new array, based on some programmer-defined rule, is a common task.  In a more realistic setting, the input array would be generated at run-time rather than pre-populated as it is here.  
• One could use a for loop to process the input array and implement the filtering in the body of the loop.  However, the filter() method is specifically designed to do this.
• The filter() method takes either a function object or an in-line function as used above, and returns a Boolean for each element.  Elements generating true are included in the return array.
• The code above uses the JavaScript ternary operator to determine the Boolean return value.  An if else statement could also have been used.

Apply A Function To Each Element Of An Input Array Using The Array map() Method

// Apply an in-line function to each array element and add the return value to the return array.

function test_map() {

   // Create and populate an array object with test data.

   var array = ['rat', 'cat', 'cow', 'horse', 'dog', 'mouse', 'pig'];

   var upcase_names = array.map( function( element ) {

                                     return element.toUpperCase(); } );

   Browser.msgBox(upcase_names);       

}

Code Notes

• The map() method, like the filter() method shown above, applies a function to each element of the input array.  However, unlike filter(), it returns an array of the same length as the input array.  
• All or some of the elements of the input array may be altered in the output array.
• The above example applies the string toUpperCase() method to each element.
• map(), like filter(), is more useful when the input arrays are populated at run-time.

A More Practical Example - Filtering Duplicates From Spreadsheet Input

• As stated above these array methods are more useful when the arrays that they operate on are generated at run-time.  
• One practical application is to use them to filter out duplicates from an input Google spreadsheet range.
• The writeUniqueList() below performs this filtering by taking an input range to filter and an output range to where it writes the unique list.
• It is tested in the function test_writeUniqueList() where the input and output ranges are defined.
• To use, add some data to a sheet, the test code below uses range "A1:A9", paste the code into the Script Editor and run the test function:

// Define range arguments for writeUniqueList() and call it with these arguments.

function test_writeUniqueList() {

   // Define an input range

   var input_range = SpreadsheetApp.getActiveSheet().getRange("A1:A9");

   // Define an output range as one column to the right of the input range

   // Warning, it will over-write

   var output_range =  input_range.getCell(1, 1).offset(0, 1);

   // Call the function with the correct arguments

   writeUniqueList(input_range, output_range);

}

// Write a unique list of values in an input range of a single column to an output range.

// Disallow input ranges with >1 column.

function writeUniqueList( range_input, copy_to_cell ) {

   // Check that the input range is single-column, otherwise throw an exception.

   if ( range_input.getNumColumns() > 1 ) {

       throw("Input range can only contain a single column");

   }

   // The range method getValues() always returns an array of arrays.

   // For single column input, the inner arrays are all single element.

   // Apply the map() method to turn the inner arrays into scalar values

   var values_input = range_input.getValues().map( function( inner_array )

                                                   { return inner_array.join(''); } );

   // Declare an array to which values are added

   var unique_values = [];

   // Declare a variable for the range row offset for output.

   var offset_row = 0;

   // Loop over the array of values from the single-column range input.

   for( var i = 0, values_input_count = values_input.length; i < values_input_count; i++ ) {

       // Perform array membership test.

       if ( unique_values.indexOf(values_input[i]) === -1 ) {

           // Add the element only if it is not already present.

           unique_values.push(values_input[i]);

           // Write the value to output if it has not been added to the array in a previous iteration.

           copy_to_cell.offset(offset_row,0).setValue(values_input[i]);

           // Increment the row offset counter for the next iteration.

           offset_row++;

       }

   }

   

}

Code Notes

• The above code uses array methods to add some useful functionality, namely, produce a list of unique values from an input range.  There does not appear to be a way to perform this task using the Google spreadsheets interface.
• To simplify matters, it throws an exception if the input range is not single-column.
• Once again, the key method that is used to determine uniqueness is  indexOf().
• Possibly the trickiest part of the code is the application of the map() method to the array of arrays returned by the range getValues() method.  It is important to remember that the array that getValues() returns is not a simple array of scalars, to turn it into an array of scalars, the map() method applies the array join() method with an empty string argument to each input element which turns a single element array into a scalar value.
• When defining the output range in the calling function, the range method getCell() was used.  This is a one-based method, like the Cells property of the range object in Excel VBA.  getCell(1,1) returns a range object representing the top left cell of the given range object, just like Cells(1,1) in Excel VBA.