Google Apps Script – What a difference 5 years make – Episode 2

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

Properties service

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.

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.

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

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.