Notes on Google Apps Script (GAS) for programming Google Spreadsheets.
Leanpub Book: https://leanpub.com/googlespreadsheetprogramming Currently updating this book, will release updated version this year (2016).
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.
Because the source HTML table is dynamic, your ordering and numbers will differ depending on when you execute the IMPORTHTML function.
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.