2019 a decade in Apps Script
I usually do an annual post on what’s been going on in the world of Apps Script, like this one but since we’re coming up to the platform’s 10th anniversary in August this year (here’s a story back from 2009 when it got the green light), I thought I should do something a little different.
By Bruce McPherson
What is Apps Script
It’s a scripting language for G Suite, primarily to automate manipulation of documents. In its simplest form, it provides a convenient way to get access to the document’s object model and data without having to use REST APIS directly. In the early days there was, for example, no external API for the Document service, so Apps Script was the only way to programmatically access it. There are generally 3 ways to access a G Suite service, although not all are available for every service, and not all capabilities are available to each method.
- As an Apps Script regular service – the object model is built into Apps Script and no special settings are needed
- As an Apps Script advanced service – this is essentially an Apps Script wrapper for the underlying API and needs to be enabled both in Apps Script and in its associated Google Cloud Platform project. There is no specific Apps Script documentation for advanced services. You need to read the REST API docs for how to interpret the object model
- Accessing the underlying REST API via an external fetch, and untangling the JSON response manually.
The language is derived from an older JavaScript (or more pedantically EMCAScript) dialect than is commonly in use today. It doesn’t run in the browser, but rather runs in a JavaScript engine on Google servers. I did some research into the details of the emulator and the JavaScript level it supports in a while ago, and I think it hasn’t changed that much in the intervening period.
VBA
The Microsoft Office ‘equivalent’, VBA, is 26 years old, and although it serves a similar purpose the capabilities, design and execution are very far apart, but more of that later.
A brief history of apps script capabilities
There’s been much coming and going over the past 10 years. Here’s a non-exhaustive list of some of the winners, and losers.
| Year | Capability |
|---|---|
| 2009 | Apps script debut with support for the basic document APIs and other connectivity aids to access external APIs. Finance (now deprecated) |
| 2010 | The script gallery - now deprecated Google sites (now only supports the old Google sites) UIApp - now deprecated. Groups Domain (now part of Admin SDK) Adsense (advanced service) Lock Cache Charts |
| 2011 | Prediction (now deprecated) Tasks (advanced service) UrlShortener (now deprecated) Gmail Document GUI builder (now deprecrated) Debugger. Many enhancements and bug fixes mainly related to all the new 2011 services, and in particular to UiApp (now deprecated) HtmlService ScriptDb (now, sadly, deprecated) |
| 2012 | Admin SDK (advanced service) directory and reports Fusion tables (now deprecated) Google+ domains (advanced service) Mirror (advanced service) YouTube (advanced services) and analytics Forms Drive (replacing Docslist) |
| 2013 | Admin SDK (advanced service) directory and reports Fusion tables (now deprecated) Google+ domains (advanced service) Mirror (advanced service) YouTube (advanced services) and analytics Forms Drive (replacing Docslist) |
| 2014 | Native mode in HtmlService (the removal of the caja sandbox was a great step forward for enabling flexibility of what you could do with Apps Script client side) Support for custom Oauth flows via the Script service Add-ons developer preview Cache, Lock and Properties service enhancement with separation of user, script and document mode Linear Optimization |
| 2015 | Add-ons in GA Almost all the enhancements in 2015 were in support of Add-ons node-google-apps-script (now deprecated in favour of clasp) |
| 2016 | Android Add-ons Early access program for App maker |
| 2017 | Slides advanced service Sheets advanced service Execution API Stackdriver logging Reviews introduced for OAuth scopes, in addition to the Add-on review processes Gmail Add-ons Card Services |
| 2018 | Apps Script API Clasp was enabled by the Apps Script API, and is a way to allow developers to use their own tool chain preference to develop Apps Script projects Macros for Google Sheets This was also a big year for enhancements to the Slides and Sheets services |
| 2019 | DataStudio integration enhancements BigQuery data connector |
Evolution
So is there a clear evolutionary theme developing? For insight, let’s look at Microsoft VBA over its long lifetime. It started as a way of automating repetetive tasks (the Macro), but soon developed a life of its own. What it had going for it was its tight integration into the object model of the document hosting it, but that is also its problem. Being container bound means that document sprawl also means script sprawl, and sprawl it does. Into every Office application, and across many copies of the same documents across many instances of local PC storage.
Although cloud based, Apps Script’s early container bound heritage could have led to the same issue, but soon it became clear that libraries and standalone scripts could be much more reusable when properly controlled.
For the developer or for the office professional?
This is the same dilemma as VBA. Whether to make it super comprehensive and powerful, but with it, dangerous and elite, or whether to make it simple to create apps but with more limited capability. Creating front-ends is always the most complex problem to solve for both developers and end users, and in the case of Apps Script, it’s doubly complex because the front end runs in the browser with no direct integration to the underlying object model which is detached and on the server.
With VBA, it’s somewhat easier, since the front end runs in the same context as the document it is referencing. Microsoft tried to solve this problem for Office 365 with their JavaScript for Office and ‘add-ins’, by tight integration with their JavaScript libraries and some limited binding between the client side and server side data model, but you end up with less capability and the same complexity. I did a port of an Apps Script Add-on to an Office Add-in a while back if you want to see more of the comparison between approaches.
Controlling server side Apps Script with an HTMLService generated app is extremely powerful and flexible (essentially this is how an Add-on and a webapp work), but leads to security concerns. Locking down what can be done client side leads to unappealing, unimaginative solutions with limited utility.
Identity crisis
This makes it seem like Apps Script has gone through several cycles of an identity crisis.
Phase 1
In the early days it was clear that a way of creating an effective front end was required, so UiApp came along – a way of building apps from predefined widgets. Along with ScriptDb as a database a decent front end came within reach of a sophisticated end user. GuiBuilder was introduced to try to make this capability even more widely available.
Phase 2
These ‘generated’ apps were of relatively low capability and something more flexible was required. At that time, it seems that Apps Script gained many more capabilities such as an ‘unsandboxed’ HtmlService, Webapps, libraries, the execution API, the Script service, OAuth2 enablement and Add-ons to leverage this new found flexibility.
All of this, and latterly, Clasp and the API service to professionalize the developer experience, made Apps Script a more appealing and serious platform for developers, was probably the ‘golden age’ of community participation, and a great demonstration of what Apps Script could be made to do. The previous generation of ‘end user design tools’ were largely deprecated.
Phase 3
The potential for an Apps Script ‘wild west’ has given rise to a more sandboxed, user orientated environment again, with tools such as App Maker, Card Add-ons, scope refinements and restrictions and reviews on what can be published through Add-on and OAuth reviews to the extent that many of those that have produced free add-ons in the past have either withdrawn them or have stopped developing new ones.
More recently, the creators of those Add-ons that require an external security reviews find themselves faced with a cost that most will not be able to bear – as below
The assessment fee is paid directly to the assessor and not to Google. A certified third party will complete the security assessment to ensure the confidentiality of your application. Depending on the scope and complexity of your app, the cost for the third-party assessment may vary from $15,000 to $75,000. Smaller apps will be on the lower end, while more complex apps will require more review and expense.Existing assessments that meet the security assessment program standards might reduce the scope and cost of your review. The assessors will consider existing assessments in their review.
All this means that quality and security will be enhanced, but at the cost of many useful community contributions and developer resistance
VBA never solved this problem – it continues to cater for both the developer and the end-user, but it doesn’t have the same flexibility as Apps Script, and its platform means that most of the decision points around Apps Script direction simply don’t apply
So what is next for Apps Script? For the many or for the few? What do you think?
Access all my blog posts here or use the full search facility on the website to find content relating to your interest. All posts and pages relating to the topic will be displayed