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. 

YearCapability
2009Apps 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)
2012Admin 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)
2013Admin 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)
2014Native 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
2015Add-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)
2016Android Add-ons
Early access program for App maker
2017Slides 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
2018Apps 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
2019DataStudio 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

Spread the word – Follow me and send me your feedback


Twitter


Google-plus-g


Linkedin-in


Github


Slideshare