Friday, March 4, 2016

Returning Arrays of Named Ranges in GAS

Introduction

Five years ago, I added a blog entry on how and why to use named ranges in Google Sheets, see Named Ranges.  GAS provides Spreadsheet object methods to get Range objects using the range name and to set names for cell ranges but a feature that was conspicuously lacking was the ability to programmatically get an array of named ranges in a spreadsheet. Therefore, when presented with a Google Sheet, the only way to see the range names it contained was by selecting the menu Data->Named ranges... to show the "Named ranges" display that appears on the right hand side.  Some Sheets can contain a lot of named ranges so having to manually inspect them to find out what names are present and what ranges they refer to was a chore. Thankfully, Google have finally added the ability to do this ranges in GAS, see Google Issue 917. Excel VBA has had this feature for as long as I have been using it and its absence in Google Sheets GAS was an annoying omission. Thankfully, it is now fixed I will provide some demo code here.

Some Data To Work With

I am going to use the very useful Google Sheets function IMPORTHTML to return some test data to work with. I have chosenan example from football; The English premier league.

  • Create a new sheet.
  • In cell A1 enter the formula:
=IMPORTHTML("http://www.espnfc.co.uk/barclays-premier-league/23/table", "table", 1)
This should bring in the test data as shown in the figure below:

Because the source HTML table is dynamic, your ordering and numbers will differ depending on when you execute the IMPORTHTML function.
I am going to add named ranges to this sheet based on ordering rules. For those unfamiliar with football (also known as soccer in some parts of the world). I will give a brief summary of the how teams are ranked in the English Premier League and what the ranking means. I will then assign names to the relevant ranges based on this ranking.

Skip the following explanatory paragraph if you are familiar with the English Premier League!

Each team in the league plays  every other team at home and away; Each team plays a total of 38 games. You get three points for a win and one for a draw. The team with the most points after the 38 ganes wins the league. The winner and the next three highest finishers are eligible for the Champion's League (a lucrative competition played between the top teams in Europe). The fifth placed team enters the less lucrative Europa League. The bottom three clubs are relegated to the second tier of the league. In the event of teams being tied on points, goal difference is used to rank them. Goal difference is simply the number of goals scored minus the number of goals conceded. Throughout the season, the league table displays the rankings and often uses colours and lines to show the leader, the Champions League places, the Europa League place and the three relegation places.

In this example I am going to add the following named ranges:
  • The header row that contains the column names.
  • The league champion position.
  • The four Champions League positions.
  • The Europa League position.
  • And finally, the three relegation positions.
To keep the code and explanation as simple as possible, we will ignore all data outside of the range A2:I51.

Code

All the code shown here can be checked out from GitHub.


/*
Add range names to the spreadsheet
containing data returned by spreadsheet function call:
=IMPORTHTML("http://www.espnfc.co.uk/barclays-premier-league/23/table", "table", 1)
*/
function addRangeNames() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      shName = 'Sheet1',
      sh = ss.getSheetByName(shName),
      rngAddressNames = {
        "column_names": "A2:I2",
        "league_champion_position": "A3:I3",
        "champions_league_positions": "A3:I6",
        "europa_league_position": "A7:I7",
        "relegation_positions": "A20:I22"
      },
      rngName,
      rngAddress,
      rngToName;
  for (rngName in rngAddressNames) {
    rngAddress = rngAddressNames[rngName];
    rngToName = sh.getRange(rngAddress);
    ss.setNamedRange(rngName, rngToName);
  }
}
/*
Use JavaScript introspection to determine the object type
and methods of one element returned by the Spreadsheet method
"getNamedRanges()" and print them to the logger.
Throws an error if there are no named ranges in the active spreadsheet.
*/
function examineNamedRangeObject() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      namedRng = ss.getNamedRanges()[0];
  Logger.log(namedRng);
  Logger.log(Object.keys(namedRng).join('\n'));
}

/*
Clean-up: Remove ALL range names from the active spreadsheet.
*/
function clearAllNamedRanges() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      namedRngs = ss.getNamedRanges();
  Array.forEach(namedRngs, function(namedRng) {
    namedRng.remove();
  });
}

Discussion

The first function above, addRangeNames(), uses a JavaScript object to map the range names to be used to the target addresses. When executed, the named ranges should become apparent so that selecting  Data->Named ranges... from the menu displays something like the following:



There is nothing new in any of this; Adding range names was discussed in the earlier blog entry referenced above.

The interesting and novel aspect of the code above is the Spreadsheet method getNamedRanges() used in the second and third functions above. Until now, there was no way to programmatically retrieve all the named ranges in a Google Sheet. Now we can do that because this method returns an array of objects of type NamedRange for each named range in the spreadsheet.

The second function, examineNamedRangeObject(), uses the new method to return an array of NamedRange objects and then uses standard JavaScript introspection techniques to examine the first element of the returned array. When executed, it prints the object type, NamedRange, and all the NamedRange properties to the logger. All theese properties are methods with self-explanatory names:
setName
getName
setRange
getRange
toString
remove


NB: Make sure you have first executed addRangeNames() or you will get an error!

The third and final function, clearAllNamedRanges(), uses the NamedRange remove() method in an Array forEach() call to delete the range names. We could have used a standard JavaScript for loop to iterate over the array but I prefer the forEach approach.

Named Ranges and Excel Import/Export

Named ranges of course exist in Excel and can be manipulated in VBA. In the past, named ranges were lost when transferring spreadsheets between Excel and Google Sheets. Thankfully, this is no longer the case. Now range names survive import and export so if you have a complex Excel file with lots of range names, you can import it into Google Sheets and the names survive intact. The reverse is also true; range names in Google Sheets  survive export to Excel and are available in that application. This is very convenient!

Summary

The implementation of the Spreadsheet getNamedRanges() methods addresses an important requirement. We can now easily get an array of NamedRange objects that implement all the methods we need to manipulate them programmatically.
You can easily make your own spreadsheet and add the data as described, but for convenience, here is a  link to the spreadsheet.

That link contains both the data and the code so just make your own copy and you can then do what you like with it.


Follow-up

I will update my leanpub book Google Sheets Programming With Google Apps Script to cover usage of the Spreadsheet getNamedRanges() method. That update will come some time this year.

6 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. I made a video showing how I would use it. All errors are mine:
    https://drive.google.com/file/d/0B19mmCKTPBRYNnFvZ3ItMk83LXM/view

    thanks for your blog post it is very helpful!

    ReplyDelete
  3. Hi Mick, I was wondering if you have found a way to query a given sheet cell/range to see what named range it may belong? Something like: activeCell.getNamedRange() which would return the name of the named range as a string, if any... Thanks

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. Very helpful - thanks.

    Have you found a way to manage named ranges whose cells no longer exist - such as those related to a range on a sheet which has been deleted?

    It seems that the getNamedRanges() function doesn't return these.

    Alex

    ReplyDelete