IntroductionFive 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 WithI 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:
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.
CodeAll the code shown here can be checked out from GitHub.
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.
NB: Make sure you have first executed addRangeNames() or you will get an error!
Named Ranges and Excel Import/ExportNamed 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!
SummaryThe 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.