Google Apps Script – What a difference 5 years make

apps script to vba - going gas

I’ve been doing an annual update called  ‘a year in Apps Script’ for a number of years now, but I didn’t get round to the 2021 post.  This year I thought I’d do something different.

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 don’t think I have the energy to do a rewrite – so I’m going to do a critique, chapter by chapter, of where it’s now  wrong as a way to show how much Apps Script has improved (or otherewise) over the years.

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 will be series of posts, so this, the first will cover Chapters 1-4 which are the general introduction to the environment and language. I won’t go into 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 1 – Introduction

Versions

then

Apps Script is based on the ECMAScript version 3 specification, and is not directly equivalent to any one version of JavaScript in terms of features. As a baseline it uses JavaScript 1.6, but it also contains features that were implemented in JavaScript 1.7 and 1.8—which we will use heavily in the examples later on.

now

Apps Script now runs under JavaScript V8 – the same engine used by Chrome and Node.js. Previously it ran on an emulator (Rhino – see my analysis here ). It fully supports ES6, with the exception of modules, and although Promises are supported syntactically, Apps Script remains synchronous.

The legacy Apps Script environment still works too, but we dont know for how long. See my articles on language features and migrating to V8.

Services

then

A further 18 APIs are exposed as advanced services (e.g., Analytics, BigQuery, Fusion tables, YouTube); another 14 (e.g., Cache, Properties) are available as script services.

Strangely missing from this list are Google Slides and Drawings, and although requested from time to time, scripting implementation for these doesn’t seem to have much priority with the Apps Script product team.

now

All of the above standard services (Sites only works with the old sites), plus DataStudio, Lanuguage and Slides.  There are still 18 advanced services, but 4 new ones have been added, while Fusion Tables, Google+, Prediction and URL shortener have been deprecated.

IDE

then

The development and debug environment for Apps Script is very poor. Autocomplete for services is implemented, and you can use JSDOC (comments in code are marked up to describe the purpose of and arguments to a function) to implement some limited autocomplete from libraries that you have written and reference from your script.

It is possible to develop using an add-in for Eclipse, but you have to upload the edited file to be able to run it, and it doesn’t improve the debug capabilities.

For me, the IDE is the most disappointing feature of Apps Script, and is a big step backward when you’re coming from the more integrated VBA environment.

now

The newer IDE is an improvement on the original (my fellow GDE Ben Collins does a nice walkthrough here) , but still lacks a number of basic features (some of which were present in the original IDE), has somewhat confusing deployment management and can be unreliable, and there’s no github integration.

For those that prefer to use their own development environment, we’ve moved on from the Eclipse plugin approach to a customized tool- clasp – which allows you to develop apps script offline and then load it to the script environment. This has turned out to be not that popular with a declining percentage of github Apps Script projects (30% currently) using clasp (scrviz analysis)  These kind of projects would be heavily skewed to clasp type projects as github is  essentially of the workflow so I’d expect that proportion to be both higher and increasing.

Addons

then

There are two types of add-ons:

  • Sidebar – These run in a 300-pixel-wide frame on the right of the screen and are best forpersistent data related to what’s in the main document.
  • Dialog – These run in a pop-up dialog box and can be used to collect (or display) one-time data from the user.

Add-ons are added to the applications menu system.

You can also create a “nonpublished” add-on from a container-bound script by adding entries to the applications menu system that execute a container-bound script upon selection. These can display sidebars and dialog boxes too. The difference is that a real add-on can be published to the Chrome Web Store and is available for installation by any Apps user.

now

The original add-ons are now renamed as editor add-ons and can be created for Forms, Docs, Sheets and Slides and are still based on client side apps written for HtmlService. Nowadays you can also create Google Workspace Add-ons (GWAO) which are multi platform and current support Gmail, Calendar, Drive, Docs, Sheets, and Slides.

These GWAO use a new service (CardService), which allows you to create rich and rather beautiful card based UIs without the need to resort to HTML.

Another interesting development using the Google Workspace Add-ons API, is that you can now write Add-on logic using an ‘alternate runtime’ – meaning that the server side of your Add-on no longer has to be Apps Script based. Although there’s not many examples of this out there yet, this opening up of the platform is a very significant development.

Addon publishing process

then

In order to maintain quality, publishing an add-on to the Chrome Web Store requires an additional step involving a Google add-on review team. This can be a fairly laborious process, with a back-and-forth conversation in which changes requested by the review team are implemented by the developer, who subsequently resubmits the add-on for further review, ad infinitum.

As an alternative, you can publish add-ons within a domain (not public), which is subject to a less vigorous review process. While it is important to ensure quality items are being published in the store, I believe some changes are needed to streamline the process, along with some kind of publishing option to selected people who don’t need this kind of review.

Many developers who are doing this as a hobby or learning experience (you can’t yet monetize add-ons) lose interest partway through the current process and their add-ons never see the light of day.

The balance between policing standardization and allowing fresh, innovative approaches is a difficult one.

now

Summary – for the developer it’s become even more burdensome, although the process certainly reduces the possibility of abuse of terms of service by some unscrupulous developers.

  • Firstly you now need a cloud platform project other than the default Apps Script assigned one. This brings in the cloud auth process into play in addition to the Apps Script one.
  • You need to submit your add-on for branding and Oauth checking by the Oauth team. I believe this is the GCP side of the process. There are multiple levels of restrictions here depending on the scopes required by your add-on.
    • sensitive scopes – most common scopes
    • restricted scopes – Gmail, some Drive and Fit API scopes
  • Furthermore, your app may need to go for an external security review which may cost 15k-75k – way beyond the reach or desire of the ‘citizen developer’ or indeed anybody who just wants to share their work for free.
  • Now we can head back to the App Script verification process and configure the Google Workspace Marketplace API
  • Next, similar to the previous Chrome store process, your App and all its assets can be listed on the Google Workspace Marketplace – but first it needs to be reviewed by the Apps Script team, with the back and forwards as before

If you haven’t yawned out by now (and many of us will have), then your Add-on will be available for installation.

Chapter 2 – Language basics

There’s not much to say here, except that everything that worked before will pretty much work now – except that we can now use Es6 syntax – so that’s a good thing. The old syntax still works, but now there’s a better way of doing many things. There are some notes on migration here.

Classes

then

Because JavaScript is a prototype-based language, there is no such thing as a class. Classes are an important element in properly constructed VBA projects, so let’s look at some patterns for creating pseudoclasses (by defining functions) in JavaScript.

now

Well yes – of course classes are supported in ES6, and therefore now available in v8. For and intro to classes in v8 classes see here.

Accessing the IDE

then

You would create a script either via the tools menu in a container bound script or via the Drive interface for standalone scripts. You can also examine logged info from Logger.log and the execution log via the menu.

now

Nowadays all your scripts can be conveniently managed via the Apps Script Dashboard which is a much cleaner solution. Your log files and execution information can be found there too. Of course, the old methods still work too.  In the new IDE logged messages appear in real time on the log window as well as being logged to the cloud logger (you can now use console.log in addition to Logger.log for logging).

One huge omission (it’s also been removed  from the legacy IDE) is the Execution log. This was a brilliant tool that showed every interaction your script made with Apps Script services and was really useful for debugging.

Libraries

Management of libraries has always been a little bit of a poor relation in Apps Script. We’ve all become used to dependency management with yarn/npm in Node and managing library version dependency in Apps Script is difficult as there’s no enforcement process, nor one for automatically updating dependencies.

You’ll notice that the Apps Script documentation discourages the use of libraries with this warning, and it’s still the same recommendation for v8.

Warning: A script that uses a library doesn’t run as quickly as it would if all the code were contained within a single script project. Although libraries can make development and maintenance more convenient, use them sparingly in projects where speed is critical. Because of this issue, library use should be limited in add-ons.

I haven’t tested this in V8 yet, but in the legacy environment I found no measureable evidence that this was actually true – see Measuring library load speed. A much bigger disincentive for libraries is the difficulty in keeping them up to date and in sync and the potential for compromise from using someone elses code, if you’re planning to use public libraries.

Managing Library Versions

then

Apps Script keeps a revision history of managed file versions, which translates into library version numbers. You save a version of a script from the file menu and include it in a script by referencing a particular version number.

now

New versions of library are created via the deployment menu and deployments are included in a script via an option in the IDE.  In the previous process, handy comments describing the library version are viewable from the library resources menu which helps to ensure you are adding the version you think you are adding. It’s not there now.

Another issue is that recently deployed versions of libraries don’t show up as options until (I think) you refresh the IDE.  These 2 things together make library management a bit  error prone nowadays.

Another approach is to use the manifest file (appsscript.json)  to manually add or amend script configuration options, including any library dependencies.

Chapter 3 – Translating VBA functions

There’s not much to say about this as everything in this chapter, which shows equivalent ways of emulating VBA functions in Apps Script, is still relevant except that some of them can be more elegantly written nowadays using V8.

Recently Google released a VBA Macro to Apps Script convertor. This is a nice bulk conversion tool for when you are migrating from Excel to Sheets (it only works on Excel VBA for the moment), and is really aimed at container bound Macro conversion rather than conversions of significant VBA apps. The process of analysis, reporting and conversion is well thought out and works well for probably the bulk of VBA conversions that dont stray too far from regular Excel Object model methods. My VBA portfolio doesn’t have much of that, so I wasn’t able to use it with much success. Would love to hear of your experience with it.

Chapter 4 – Living with the Quotas

Daily limits

then

now

Many of these new limits are based on new services that have been added, and there’s not much change in the older services. Perhaps the most useful one is the removal of the daily limit on UrlFetch data which was a real bind previously, especially if you use external APIS.

Limitations

then

 

now

Some more welcome movement on UrlFetch payload. Note also the limitation on custom functions runtime and simultaneous executions to prevent runaway behavior

That’s about it for the first few chapters. In the next post I’ll get into some of the more significant changes within the services.

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 2

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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*


15 + 9 =