Friday, January 14, 2011

Putting JavaScript Arrays to Work in Google Spreadsheets

JavaScript arrays are very flexible objects, earlier posts have used them but not discussed them in any depth.  They are an important tool in the Google App Scripter’s armoury and worthy of some detailed discussion.

JavaScript Arrays Overview
  • Can contain heterogeneous data (JavaScript primitive data types or other objects, including arrays, of arbitrary complexity.
  • Zero-based, no need to worry about Lbound as in VBA arrays.
  • To access an element of an array use the square bracket syntax, e.g.  array_var[numeric index] (definitely not () like VBA).
  • Can  grow and shrink as required.  No Need for the tedious Redim Preserve statements as in VBA.
  • Support a variety of methods that can be used to implement queues and stacks.
  • A good on-line resource for JavaScript arrays entitled “Mastering JavaScript Arrays” can be viewed here.  To use the examples given on that page in the Google App Script editor, substitute document.writeln(<output>) with Browser.msgBox(<output>).
  • Arrays play well with the Google Spreadsheet API where many methods accept arrays as arguments and return arrays.

A Worked Example Using JavaScript Arrays in Google App Scripting
  1. Enter some test data (anything you wish) into range “B1:M11” of Sheet 1.  My demo used the month names Jan-Dec in row 1 with the numbers 1 to 10 beneath each month.
  2. Add a new sheet called “Sheet2”.
  3. Copy the following code into the script editor.
  4. Execute test_copyColumnNumbers.
  5. Check Sheet2 and see how the columns have been re-ordered in the output.

// Copy a selection of the columns in the range “B1:B11” of sheet 1 to sheet 2
function test_copyColumnNumbers( ) {
   var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   var source_sheet = spreadsheet.getSheetByName("Sheet1");
   var target_sheet = spreadsheet.getSheetByName("Sheet2");
   var range_input = source_sheet.getRange("B1:M11");
   var range_output = target_sheet.getRange("A1");
   var keep_columns = [11,10,9,8,7,6,5,4,3,2,1,0];
   copyColumnNumbers(range_input, range_output, keep_columns);

// Using arrays with ranges.  The getValues() method of the range object returns an array of arrays.
// Copy a selection of columns from an input range to another range based on an input array (zero-based)
// of columns to keep.  The values in this array are indices that are used to select columns to keep and
// their order in the array dictates the order of the output.
function copyColumnNumbers( range_input, range_output, columns_keep_num ) {
   // Create an array of arrays containing the values in the input range.
   var range_values = range_input.getValues();
   // Loop through each inner array.
   for ( var i = 0, row_count = range_values.length;  i < row_count; i++ ) {
       // Loop through the indices to keep and use these indices to
      // select values from the inner array.
       for ( j = 0, col_keep_count = columns_keep_num.length; j < col_keep_count; j++ ) {
             // Capture the value to keep
             var keep_val = range_values[i][columns_keep_num[j]];
             // Write the value to the output using the offset method of the output range argument.


Notes On Code Above
  • Re-ordering or selecting just a sub-set of columns in a spreadsheet is tedious.  There is nothing equivalent to an SQL SELECT statement and repeated copy and paste is not very pleasant.
  • The function copyColumnNumbers() above can be used to process an input range so that the output contains a subset and/or a re-ordering of the input columns.  
  • The function takes an input range and uses an input array of column indices, where the first column has index zero, and uses the offset() method of the output range argument to copy all values from specified columns.
  • This function can also be used to re-order columns, this is also potentially useful functionality.  In the test data used here, there were 12 input columns and the array of column indices to keep includes all the columns in reverse order.  This produces output where the column order is the reverse of the input.
  • The key range method used here is “getValues()”.  This method always returns an array of arrays, even if it is invoked on a range object of just one cell!
  • Once again, there are no checks or error trapping in the function copyColumnNumbers(). An exception is thrown if either of the range arguments is not a range object. However, if the columns_keep_num array contains a a column index that is greater than or equal to the input range column count, no exception is thrown, it simply results in a column with the value "undefined" which is the string representation of the undefined type. No "Subscript out of range" errors here as one would see when accessing a non-existent array element in VBA.
  • The Range object also has a setValues() that method that writes a JavaScript array of arrays, such as that returned by the method getValues(), to a a range.  The target range must have the same dimensions as the array for this to work.  This technique can be very useful, a range can be loaded into a JavaScript array, edited in some way, then written back to the same range.  The example below just shows how to create an array of arrays in JavaScript and write it to a range, more involved examples will be given later.
// Caution: This will over-write any values in range A1:B3 of the active sheet!
function test_setValues() {
 // Create an array of arrays.
 var arr_of_arrs = [['codon','amino acid'],['UGG','Trp'],['UAU:UAC','Tyr']];
 // Create a range object.
 var rng_write_to = SpreadsheetApp.getActiveSheet().getRange("A1:B3");
 // Write the array to the range

    1 comment:

    1. Hi

      Below mentioned script, i am using for data pick based on the value from another sheet but the problem is entire row is pick while using this script but want a and c and e column pull. Please suggest on this

      function Copy() {
      var sourceSheet = SpreadsheetApp.openById('1cwnUpOGFQOjNTZ8zg1SncHloFgOaJv_qPdpRZMwhp-w')
      var range = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), sourceSheet.getLastColumn();
      var arr = [];
      var rangeval = range.getValues()
      .forEach(function (r, i, v) {
      if (r[1] == 'shashank') arr.push(v[i]);
      var destinationSheet = SpreadsheetApp.openById('1TtExlWXIt7SV2jzLe0JDwJqwVAsLK7nTOHnkHT6AWdI')
      destinationSheet.getRange(destinationSheet.getLastRow() + 1, 1, arr.length, arr[0].length)