Google Apps Script, Google Sheets, getRangeList, 3D arrays
In a number of situations in my day-to-day work, I need to grab data from only Selected Columns in Google Sheets. Then using a selected column as a key, remove any duplicates in a similar way that a pivot table does.
I might want to use this information as part of a report or grab emails from a sheet and send custom emails to clients or staff.
I don’t always need all the data in each row, but I may need a lot of it. The two standard approaches would be to:
- Get all the data and then in Google Apps Script just select the relevant columns you need.
- Iterate through a list of columns you want to get ranges of and select each range individually making calls to your Google Sheet in each turn.
This might be useful for small data sets and, to be perfectly honest, I have used both these approaches in the past, but recently I stumbled across a method in the Google Apps Script Sheet Class called:
sheet.getRangeList([A1Notation,...,...])
Now I think that the awesome gods of GAS may not have purposed this method with the thought in mind that it would be used for collecting range values. I kinda get the impression that it was more designed for applying formatting to the selected column ranges.
However, what I have found is that it seems to be pretty fast at collecting all the ranges and then, on the Google Apps Script end, getting their values.
The resulting values of using getRangeList() can be then pushed into a 3D array of columns with row values.
For those of you in a hurry, the code is below. We will move on to an example and explanation after. Feel free to reference what you need and discard the rest.