It’s been over 5 years since my book ‘Going Gas‘ was published, and Apps Script evolution means that a number of sections of it is now pretty out of date.
I’ll use this format
then
A summary of or a direct quote from my 2016 book, Going Gas
now
How this has changed over the past 5 years
This is the second episode in a summary of the major changes. For episode 1, See here.
Chapters 4-5 are about the Properties and Sheet services. I won’t go into a lot of detail about the additions to existing services (for example new methods) as that’s what the Apps Script release notes are for, but rather go into more of the structural or significant changes.
Chapter 5 – The Properties Service
The properties service is a permanent key/value store for settings and other data specific to a given script. I really should have covered the CacheService in this chapter too as they are similar, except the CacheService is a temporary, larger store. In Going Gas, I covered the Cache Service in Chapter 4 as part of another topic.
then
now
Nothing has changed in either the Property or Cache service. They still have fairly meagre quotas, but you can compress data or spread objects that are too large over multiple entires, using this technique.
Previously the ScriptProperties store had a very handy editor for maintaining script level properties built into the old IDE (it’s still available there), but it’s missing from the new IDE. Seems like a rather lazy omission, so hopefully we’ll see it back in a future IDE upgrade. For now, you’ll need to write a script to examine or update the property store.
The UserProperties store fulfills a similar purpose to localstorage or cookies for desktop based applications. This gives a very handy way of tracking script usage, as described here.
Chapter 6 – Spreadsheet Service
Sheets API updates
then
The Excel object model is extremely extensive, and the Sheets model doesn’t come close in terms of richness. It does cover most important things.
now
There have been many, many updates to Sheets to fill in the gaps. There are still some, but it’s come a long way. As usual, the release notes contain a historical record of all the updates, but I’ll cover the main additions here that gets Apps Script closer to the capabilities of VBA for Excel.
Named Ranges
A real benefit of named ranges is that they can be used to label and abstract a set of cells in a sheet. That means you can access them in by name rather than cell address, which can of course change. From an Apps Script perspective, this means that you don’t need to worry about where things are physically – it was quite an omission – but now there are a whole set of Apps Script methods to manipulate named ranges and translate them into native Ranges.
- NamedRange.getName() gets the name of the named range
- NamedRange.setName(name) sets the name of the named range
- NamedRange.getRange() gets the underlying range associated with the named range
- NamedRange.setRange(range) sets the underlying range associated with the named range
- NamedRange.remove() deletes the named range
- Spreadsheet.getNamedRanges() gets an array of all the named ranges in the spreadsheet
- Sheet.getNamedRanges() gets an array of all the named ranges in the sheet
- Protection.setNamedRange(range) associates an existing protected range with an existing named range
Developer metadata
The idea of developer metadata is to allow properties to be assigned to a workbook, a sheet or a range. The magic of developer data is that it keeps track of renaming or moving within the workbook so the properties are still associated with the original range even it moves or resizes. We had a Totally Unscripted episode on developer metadata here
and you can read more about it here. When it first came out it was only available in the Sheets Advanced Service, but now it’s part of the SheetsApp.
Groups
The Group class was added to support grouping (collapsing and expanding) of continguous rows and columns to match the similar capability in VBA. The Group class came with a whole set of new methods.
Range.collapseGroups()
Range.expandGroups()
Range.shiftColumnGroupDepth(delta)
Range.shiftRowGroupDepth(delta)
Sheet.collapseAllColumnGroups()
Sheet.collapseAllRowGroups()
Sheet.expandAllColumnGroups()
Sheet.expandAllRowGroups()
Sheet.expandColumnGroupsUpToDepth(groupDepth)
Sheet.expandRowGroupsUpToDepth(groupDepth)
Sheet.getColumnGroup(columnIndex, groupDepth)
Sheet.getColumnGroupControlPosition()
Sheet.getColumnGroupDepth(columnIndex)
Sheet.getRowGroup(rowIndex, groupDepth)
Sheet.getRowGroupControlPosition()
Sheet.getRowGroupDepth(rowIndex)
Sheet.setColumnGroupControlPosition(position)
Sheet.setRowGroupControlPosition(position)
Charts, pivot tables, formatting and lots more
Almost anything you can do in the Excel UI is doable from UI. Much of the formatting and analysis functions in Sheets were not available via Apps Script, but all this changed in 2018-2019. Methods to improve Filtering, formatting, charts, pivot tables and a whole lot more were added to the sheets API to try to get to that same level of maturity in Apps Script. There’s too much to cover here, so I’ll direct you to the 2018 and 2019 release notes for more info.
Slicers
A nice feature of Excel was slicers. These are like versatile filters and were popular when first introduced. A few years ago I created an add-on called Dicers to emulate them, but now Sheets has slicers, and Apps Script can be used to manipulate them using these methods
Tables
then
One of the handy structures in Excel for which there is not a direct Sheets equivalent is the table. This is a contiguous range of cells that are considered together as a dynamic range (the dimensions of the table change as rows are added), and they contain various elements such as dynamic styling, totaling, sorting, and so on.
now
An advanced service for Google Tables has been added to Apps Script. The Tables service allows scripts to programmatically read and edit rows within Tables. However this service is only available in US for now, so I’ll save the details for a future post.
Macros
then
VBA comes with a macro recorder; you can record a series of key‐board actions, and the recorder will generate their VBA equivalent. This functionality seems to have been largely abandoned in later versions, where many actions that operate on the newer additions to the object model are not recorded.
I have mixed feelings about the macro recorder. The positive aspect is that a user can get some insight into how to manipulate the object model. On the negative side, I’ve seen many dreadful VBA applications based on macros that began life as a series of recorded macros. It’s not a good model from which to learn.
Apps Script does not have anything like this, and the type of skills needed to create enduring applications probably cannot be learned from such a tool.
now
Sheets now have Macros. These are recorded as a container bound script, and listed in the appsscript.json manifest. In my opinion it’s a truly dreadful idea, so I’m saying no more about it here.
Summary
then
The Excel object model is extremely extensive, and the Sheets model doesn’t come close in terms of richness. It does cover most important things.
now
The Sheets Service has come along rapidly over the past 5 years, and it’s hard to think of anything in Excel VBA that’s now missing.
You can get a copy of Going Gas from here on Amazon or Google books have a pretty large preview chunk for free.
More like this
Google Apps Script – What a difference 5 years make – Episode 1