Wednesday, January 12, 2011

Named Ranges

See chapters 4 and 5 in this book on Leanpub  

JavaScript objects were supposed to be the subject of this piece but this is a slight digression before getting to that.  A topic omitted previously but worthy of some attention is the named range.  
Range Names
  • Simplify many programming tasks.
  • Simplify use of lookup functions such as VLOOKUP where the range name can be used in place of the “$” signs create an absolute reference to the input.  Incorrect usage of these $ characters and omission of the final “FALSE” argument are common sources of VLOOKUP errors, especially when the formula is is copied down using the bottom right “black cross”.  Using a range name eliminates the need for the $ characters and makes the VLOOKUP much easier to read and debug.
  • Their use in Google spreadsheets and their manipulation via JavaScript has close parallels with their use in Excel.
  • Named ranges are defined in Google spreadsheets using the menu sequence Data->Named and protected ranges. Click the "Add a range" in the resulting dialog! Enter a name for the range and the range address in the dialog. (Change from earlier version when this was under the Edit menu, was easier then)!
  • A name is unique across the spreadsheet, a pre-existing range name can be assigned to a new range, by doing so, the name silently loses its association with the previous range (same as in Excel).


Test Input (Excel and Google Spreadsheets)

Name Phone
Smith 0181-344-9876
Patel 0121-657-8762
James 0171-744-4999
Gartland 0992-633-722
Cho 0898-244-7499
Ramirez 071-983-8866
Mulcahy 0204-544-3822

In another sheet, we can look up names
A name that does not exist in the lookup range (Gives “#N/A” with a comment indicating that name “Clarke” was not found in Google spreadsheet, the same #N/A is given in Excel but the warning is given as the top left green flag ):
=VLOOKUP("Clarke",Sheet1!$A$2:$B$8,2,false)

Testing a value that is present in the lookup range (unsurprisingly, the phone number returned for Ramirez):
=VLOOKUP("Ramirez",Sheet1!$A$2:$B$8,2,false)

The above VLOOKUP code is ugly, named ranges can simplify matters in both Excel and Google spreadsheets:
=VLOOKUP(“Clarke”,phone_numbers,2,false)
=VLOOKUP("Ramirez",phone_numbers,2,false)

The VLOOKUP versions using named ranges are much cleaner, no need for the sheet name since range names are unique across a given spreadsheet file, the $ and ! characters, and the addresses.  This trick works in both spreadsheet applications.  Note the range name is not enclosed in double quotes in either instance!

Setting and Using  Named Ranges in Google Spreadsheets Using JavaScript

function test_nameRange () {
 nameRange("Sheet1","A2:B8","phone_numbers");
}

// Given a sheet name (assumed to be in the active spreadsheet, an address using "A1" notation,
// and a name string, assign that name to the range address in the given sheet.
function nameRange(sheet_name, address, rng_name) {
   // Create an spreadsheet object.
   var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   // Get a range object by firstly getting a sheet object from the spreadsheet object and then pass the
   // "address" argument to the sheet "getRange()" method.
   var rng_to_name = active_spreadsheet.getSheetByName(sheet_name).getRange(address);
   // Use the spreadsheet object method "setNamedRange()" to assign a range name to a range object.
   active_spreadsheet.setNamedRange(rng_name, rng_to_name);
}


Code Notes
  • Copy the two functions above into the JavaScript script editor and execute function “test_nameRange()”.  The named range can then be seen and selected from the spreadsheet by selecting: “Edit->Named ranges”
  • The range name is assigned by a method of the Spreadsheet class, this makes sense when one remembers that range names are unique across a spreadsheet.
  • The code can be summarised as follows: Create a range object and a spreadsheet object and call a method on the latter to assign a name to the former.


Creating an Array of Values for a Named Range
Just using JavaScript to set a name for a range is not very useful since one can easily set the names using the spreadsheet menu.  So once a name is assigned, how can it be used in scripts?  Consider the following code:
// Use the range name create previously to test function "getNamedRangeValues()"
function test_getNamedRangeValues() {
   var range_name = "phone_numbers";
   var range_values = getNamedRangeValues(range_name);
 
   // Display the contents of the first cell in the named range.
   Browser.msgBox(range_values[0][0]);
}

// Return an array containing the values in the range name argument.
// Passing a non-existent range name raises the following error:
// TypeError: Cannot call method "getValues" of null. (line 0).
function getNamedRangeValues ( range_name ) {
   // Create an spreadsheet object.
   var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   // Create the range object.
   var range = active_spreadsheet.getRangeByName(range_name);
   // Call the getValues() range object method to create an array
   // containing the values for the cells in that named range.
   var range_values = range.getValues();
 
   // Return the array
   return range_values;
 
}
​Code Notes
  • The previously assigned name has been used to reference the range object whose getValues() method is invoked to return an array.
  • The getValues() method could have been invoked on a range object created explicitly as in the function nameRange() above but in larger applications named ranges can facilitate cleaner code.
  • JavaScript displays its flexibility here - if the range has more than one column, it returns an array of arrays whose elements can be accessed using a pair of square brackets as in [row][column].
  • The returned array of arrays could then be processed in a nested loop.
  • Once again, there is no error handling in the function and passing in a non-existent name  throws a TypeError exception when the range object getValues() is invoked.  JavaScript supports structured exception handling that will be used in future posts to address this shortcoming.
  • In Excel VBA, variant arrays can be assigned to the return value of the range property Value. Here is commented VBA code showing how ranges can be loaded into variant arrays and how the variant array can be manipulated and then written back to an Excel sheet in one fell swoop.  This is a neat and powerful trick that also offers performance benefits, it is well documented in the Bovey book listed in the Getting Started post on this blog.


VBA Code Using Named Range and Array Variants
'  Demo of named range and variant arrays
Sub NamedRangeToArray()
   ' Assign a name to a range
   ActiveWorkbook.Worksheets("Sheet1").Range("A2:B8").Name = "phone_numbers"
   ' Declare a variant array to hold the values of the named range
   Dim rangeNameData As Variant
   
   rangeNameData = Range("phone_numbers").Value

   ' Display the first value in the variant array using two array subscripts, row, column
   MsgBox rangeNameData(1, 1)
   
   'Change a value in the variant array
   rangeNameData(1, 1) = "Jones"
   
   'Write the array back to the range, all in one go!
   Range("phone_numbers").Value = rangeNameData
End Sub

  • Note, variant arrays returned by the Range Value property are one-based, not zero-based like JavaScript arrays.  VBA is annoyingly inconsistent in this respect, the Offset property and VBA arrays themselves by default are all zero-based while these variant arrays and the Range Cells property is one-based!  Traps for the unwary!  
  • Note added retrospectively: The Google App Scripting spreadsheet range object method getCells(row_num, column_num) is one-based, e.g.
// Range getCell() method is one based!              
function test_getCell({
    var rng SpreadsheetApp.getActiveRange();
    // Displays the value in the top left cell of selected range in the active spreadsheet
    //  getCell(0, 0) below generates a  "Cell reference out of range" error
    Browser.msgBox(rng.getCell(11).getValue());         
}

  • The above VBA code shows how variant arrays can be altered and then written back to worksheets.  Can this neat trick be performed in Google spreadsheets using JavaScript?  Of course it can and this will be covered in the next post when JavaScript arrays are discussed in detail.

2 comments:

  1. The first part of this post on how to create named ranges in a spreadsheet with Javascript is very helpful, as I was looking for exactly that. Thanks. I'm now wondering if there's a way to assign permissions to the named ranges in Javascript as well (for example, if a user has edit permissions on a sheet but has view permissions for a named range).

    Thanks,
    Carol

    ReplyDelete
  2. Hi Carol,
    I've checked Range (same whether range is named or not) methods and cannot find a way to do this programmatically.

    As an alternative, you might be able to use the "onEdit()" trigger to do this.

    Other alternatives:
    Read the data from the protected Range into a JavaScript array using the Range "getValues()" method at the start of the script, then write these values back to the sheet at the script's end. That way you can ensure that any edits made by the user will be over-written.

    Hope that helps, if I or you can come up with a better idea, i'll post it.

    Cheers

    Mick

    ReplyDelete