Going GAS

Chapter 1. Introduction

Moving from VBA into the world of Google Apps Script (GAS) requires some adjustment of your development and planning approaches. There is not a one-to-one relationship between things you can do in VBA, Apps Script, and JavaScript. Even where one can be articulated, the environmental differences under which they each operate are such that valid approaches in one platform do not necessarily translate into wise or even valid approaches on another.

Note

With advance apologies to purists, for simplicity I refer to these different environments as “platforms.” Even though they don’t really qualify for that description, it will do.

I’ve deliberately divided the code samples into a mixture of languages and extensibility solutions, and of course there is crossover between each item. For example, to write a Google add-on, you have to use Apps Script, which is the JavaScript language with additional Google-specific services. On the other hand, you can write Apps Script without necessarily creating an add-on.

VBA merges similar capabilities to Google Forms, Apps Script, add-on-type user interfaces, and the object model into one platform. You need some or all of the Apps Script–related components to achieve the same thing.

This chapter will describe the platforms covered in this book. Some or all of this material will be evident to those who are already familiar with them, but this foundation will inform future chapters as we contrast the different solutions available on each platform.

What Is VBA?

Because you are probably already a VBA developer, or at least have some problems currently solved by VBA that you want to move to a different platform, I’ll keep the definition of VBA fairly brief and focus on its unique attributes (both positive and negative).

VBA applications are the most common way to extend Microsoft Office applications such as Excel, PowerPoint, Word, Visio, Outlook, and Access.

Extending

You can extend VBA’s capabilities by adding references to other custom objects held in dynamic link libraries (DLLs) or type libraries created in languages other than VBA. The API for each Office application itself is via a DLL containing the code and data required to manipulate the application object model.

You can make your own classes, create custom functions that can be called from worksheets, define and declare your own events, and access events that are triggerable by built-in and custom objects.

Fragility

VBA’s integration and extensibility through library references make it very powerful indeed, but they also make it fragile. Relying on the availability of myriad external shared libraries of a particular version means that transportability from one computer to another is hit and miss for substantial scripts. Furthermore, if DLL providers fail to keep their libraries current you might be unable to move to new versions of Office at will.

Security

The development object model is accessible from VBA, which means that you can write or modify code from code. This powerful capability is one of the serious security holes that has led to VBA being both a desirable and easy target for hackers.

Microsoft’s response has been to intensify lockdown with each successive version of Office. Nowadays, by default, you can’t run macros (as VBA procedures are known) or even save a document containing VBA. Even the developers’ view and access to VBA is hidden.

Asynchronicity

Some operations can be performed asynchronously, although the capability is very limited and somewhat of a science project to implement. An asynchronous operation is one that is performed while the main execution flow continues—for example, fetching data from an external website, returning control to the main flow so that it can do something else at the same time, and receiving a callback to be handled when the fetch is finished. VBA does not have good support for asynchronicity, and most operations are processed strictly in sequence. My view is that if you are worried about asynchronicity in VBA you are probably doing it wrong, or using the wrong tool.

Efficiency and Performance

VBA compiles (automatically) into an intermediate language called P-code (pseudocode). Libraries and controls written in other languages and referenced by VBA are already compiled down to P-code, and their capabilities extend VBA efficiently.

Most applications tend to be small scale, so performance becomes an issue only where memory is scarce and large workbooks are being processed. The garbage collection algorithm in the VBA engine seems to be fairly poor, meaning that you must take care when creating large memory structures with backreferences.

Maintainability

Typically the code is container-bound, meaning that it forms part of the spreadsheet or document on which it is supposed to operate. When you make a copy of, for example, a macro-enabled spreadsheet, the code comes with it, which leads to code and version sprawl that’s difficult to contain. It is possible, through add-ins, to run code in one spreadsheet that resides in another, but in my view that simply makes a messy situation even worse.

The benefits of P-code (a common intermediate language for libraries) were further diluted when a different standard was introduced for the .NET Framework, complicating things even more.

To be able to support apps written in the .NET Framework, an entirely new runtime and development environment (VSTO) was introduced, again with different versions needed for different versions of Office.

What is VBA good for?

It’s clear that writing enterprise-grade, maintainable software using a container-bound platform with difficult-to-control dependencies is challenging.

VBA is great for small, often one-off, computational tasks and for organizing data extracted from other sources.

Despite its age, the integrated development environment (IDE) is a pleasure to work with, and so much has been added over the years you can do almost anything you want with VBA. It also comes with a macro recorder; you can record a series of keyboard 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.

Overall, VBA offers graceful automation coupled with easy implementation. It is a hard act to follow.

What Is JavaScript?

You’ll sometimes hear ECMAScript and JavaScript used interchangeably. I’m going to do the same thing, and usually just refer to JavaScript, which is the branded version of ECMAScript controlled by Mozilla. The ECMA organization controls the overall language specification used by all implementations, such as JScript by Microsoft and (of course) Apps Script by Google.

A Quick History

ECMAScript has been around in browsers since 1996. The JavaScript brand was introduced by Netscape for its Navigator browser. JavaScript’s main purpose was to enable user interaction and dynamic modification of the document object model (DOM), which was previously created from HTML hosted or created on a server and served up as static content.

JavaScript allowed dynamic manipulation of the DOM from within the client browser, revolutionizing user experience possibilities. The JavaScript name became the property of Sun Microsystems through a Netscape/Sun development alliance, and when Oracle bought Sun, it acquired the JavaScript name, which it still owns today.

Versions

JavaScript continues to evolve, and it is not only a skill much in demand, but also one that many developers learn in addition to their server-side language specialty. With the rise of Node.js (and Apps Script), JavaScript is increasingly being perceived as a viable server-side language too.

Because of the differently branded ECMAScript implementations, it’s hard to exactly pin down which features a particular implementation supports. The usual way is to refer to the ECMAScript version specification, the latest of which is version 6, with version 7 under development.

Because there is such a wide variety of browser versions, JavaScript developers have to use various strategies to ensure that their code will run on clients of an unknown vintage or brand. Common strategies are the use of frameworks (e.g., Modernizr and jQuery) that take care of feature detection and use an appropriate JavaScript syntax, or polyfills (code that emulates new features not available in older JavaScript versions when they’re required).

In principle, JavaScript tries to always be backward-compatible—meaning that code written using old vanilla JavaScript will still execute in a modern version, and the use of polyfills will attempt to ensure that code written in new versions will run on older JS engines.

JavaScript Is Not Java

The names hint that one should be some kind of subset of the other, but they are not related. JavaScript was originally called Mocha and LiveScript, and only later named JavaScript by Netscape, at the same time that Java was being promoted by Sun Microsystems. Here are some other important distinctions:

• Java is compiled into a universal bytecode language that can (theoretically, anyway) be run on any Java Virtual Machine (JVM). The idea is that a Java binary can be transported between devices with a JVM, whereas JavaScript suffers from different browser implementation inconsistencies.
• JavaScript is interpreted from the original code at runtime. It doesn’t go through a compile/build validation process, so coding errors are detected on running.
• Although Java was intended to run on both the server and the browser (as a plug-in), and JavaScript was designed for the browser, these distinctions are becoming blurred. JavaScript is the language of Node.js and Apps Script, both of which are server-based, and the use of Java plug-ins in a browser is increasingly being blocked and falling into disuse.
• Both JavaScript and Java are syntactically based on C, but the languages themselves have fundamental differences—notably, prototypal inheritance as opposed to classes, and dynamic versus explicit typing.

Learning JavaScript

There are probably more generic “get started in JavaScript” tutorials around than for any other language, and it is not my intention to add to their number. An alternative approach that focuses on differences can effectively leverage existing skills and knowledge. Understanding the language fundamentals and built-in capabilities compared to their VBA equivalents is a good way to quickly become operational in JavaScript, irrespective of your current skill levels.

Key things you’ll notice about JavaScript, as a newcomer or VBA practitioner, include the following:

• JavaScript is not a typed language.
• Everything is an object, including functions.
• Variables can be “almost equal.”
• Key/value pairs and JSON are part of the language.
• Everything is case-sensitive.
• The IDE doesn’t help you as much as it should (compared to the VBA one).
• There are always many ways to do the same thing.
• I’m pretty sure you’re going to like it—maybe not at first, but soon.

What Is Apps Script?

In the previous section, I covered the difference between ECMAScript and JavaScript. Apps Script is Google’s dialect of ECMAScript that runs not in a browser, but on Google’s servers. This means that it is essentially browser-independent (but more about that later when we get to running things on the client from Apps Script).

Versions

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.

It Runs on a Server

Because Apps Script does not run in a browser, it does not have access to client-specific objects such as the DOM and Window APIs. Apps Script is server-based, but happens to use a JavaScript variant as its language. Google could have just as easily used Python, PHP, Java, or even some entirely new language, but it decided to use JavaScript (or more precisely, ECMAScript).

It seems counterintuitive that a language originally designed to enable client-side DOM manipulation and user interaction would be a good candidate for a server environment with no DOM and very limited built-in user interaction capabilities. Nevertheless, it turns out that selecting JavaScript as the Apps Script language was a wise choice, not only because it is widely known, but also because HTML service extensions (which allow client-side interaction from Apps Script and run in the browser) are standard web applications with HTML and JavaScript components.

Services

The purpose of Apps Script is to extend Google Apps. This means that it needs access to the object model for Sheets, Docs, Drive, and so on.

At the time of writing, the services in Table 1-1 are all exposed as standard Apps Script services.

Table 1-1. Standard Apps Script services

Service interface	Accesses Google service	VBA counterpart object model
CalendarApp	Calendar	Outlook
ContactsApp	Contacts	Outlook
DocumentApp	Docs	Word
DriveApp	Drive	Local files via Windows.Scripting
FormApp	Forms	Userforms via VBA in Office apps
GmailApp	Gmail	Outlook
GroupsApp	Groups	N/A
LanguageApp	Language translation	N/A
Maps	Geocoding, Directions, and static maps	N/A
SitesApp	Sites	N/A
SpreadsheetApp	Sheets	Excel

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.

Fully Authenticated Environment

OAuth2 APIs are used for both authentication (i.e., that the requestor is who they say they are) and authorization (i.e., that they are allowed to perform the requested operation on the targeted resource). Google’s authentication implementation (like those of many others, such as Microsoft and PayPal) is built to the OpenID Connect specification, and its role is to validate identity. Once identity has been verified, confirmation is required to ensure that the validated identity has the authorization to access the resource requested. This combination of operation and resource is known as scope.

OAuth2 can be a fairly complex business when you’re working from a browser-based client, and generally involves the use of a helper API. In the server environment of Apps Script, authentication and scope authorization is built in and handled automatically, removing significant complexity barriers to getting started.

Quotas

Because you are running in a shared server environment, Google goes to some lengths to ensure that you don’t accidentally create runaway processes, or abuse its service (which is, after all, free) by running processes for hours that really should be run on some of its other (paid) cloud offerings such as App Engine.

A process can run for a maximum of six minutes, and there are various rate and quota limits on what you can do with each service.

Rate limits

Rate limiting is where Google has set a ceiling on how rapidly you can repeat an operation. For example, if you create and publish an Apps Script web app, it will fail if you attempt to execute it more than 30 times a second—even if that is the only time you invoke it in a day.

Quota limits

Quota limiting is where a total number of operations is restricted over the duration of some time period. For example, you cannot send emails to more than 100 different recipients in the course of one day. Although there is clearly a need for such restrictions, they’ll be unfamiliar to those accustomed to VBA, and it can be very frustrating and complex to build schemes (such as sleeping between service calls or using exponential backoff for queries¹) to negotiate these obstacles.

Performance

Just like Office and VBA, Apps Script is best suited to small tasks that extend docs rather than large, resource-intensive systems. It is certainly possible to create complex and sophisticated workflows on the Apps Script platform, but that is the exception.

In addition to the quotas, performance is also controlled, so as a rule of thumb, tasks will take longer on Apps Script than in VBA.

The tradeoff is cloud-based accessibility against client-based complexity.

Various performance aids, such as the caching service, are available (and don’t use up any quota), but you need to be extremely careful how you structure your app in order to minimize service calls and unnecessary repetition (all good things but less critical in VBA).

A discussion of how to implement these methods of efficiently using Apps Script services will be built into some of the examples in later chapters.

Asynchronicity

In Apps Script, every operation is blocking. There is no asynchronicity, even for fetch operations. This is hard to get accustomed to if you are coming from a regular JavaScript environment, where asynchronicity and event-driven behavior is ingrained. For those coming from VBA, it won’t be so much of a culture shock.

Events

There are only a few handleable events in Apps Script, the most useful of which is related to enabling change detection in Sheets data. Like the lack of asynchronicity, this will be frustrating for users already proficient in JavaScript, and a big omission for those accustomed to VBA’s extensive portfolio of event interactions.

Triggers

One of the killer features of Apps Script is that it enables you to schedule and run scripts without being logged on. This means you can, say, run regular reports, or summarize data every hour or at 5 a.m. These kinds of scheduled events are called time-based triggers.

There are strict quotas on the number of triggers you can have and how much daily runtime they consume, which lessens their usefulness and increases complexity, as the number of workarounds you need to build increases with the more triggers you have.

Web Apps

Another killer feature is the web app. You can publish a web app using the HTML service (to serve up web pages generated by your app), or Content service (to serve up data from your app).

These web apps can service both GET and POST requests by executing Apps Script functions server-side, and provide a convenient way for non–Apps Script apps to get access to docs resources, effectively turning Apps Script into a web server or RESTQuery server.

Because these web apps run server-side, fetching data from servers on different domains does not hit the same cross-origin-request restrictions normally faced by client-based JavaScript, and you can even use an Apps Script web app as a proxy to fetch data you could not otherwise reach from your client application.

In VBA terms, this is a little like the difference you’ll see between using the xmlHttp object (which is subject to client restrictions similar to a JavaScript app running in a browser), and the serverXmlHttp object (which is similar to an Apps Script running on a Google server).

Maintainability

Previous versions of scripts are maintained for fallback, and there is an extensive library system that you can use to share common code between apps.

You can create both standalone scripts and container-bound scripts—the latter of which reside in a particular document, from which it’s clearly more complicated to share code. For this reason, I tend to stick to standalone scripts.

If you are writing custom scripts (i.e., a script accessible from within a formula in a spreadsheet cell), you can do so only from container-bound scripts—which could lead to the kind of code sprawl you see in VBA.

Similarly, templates intended to be used by the HTML service must also be local (although there are ways around this using libraries), which again can lead to code sprawl.

IDE

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.

Inexplicably, though, IDE autocomplete is not implemented for functions in the current script, yet it is for custom functions accessed from a worksheet cell.

The debugger has a number of limitations. You can set breakpoints and see the values of variables at those points, but there is no console/immediate mode, and in any case certain kinds of JavaScript constructs cause it to fail.

HTML service apps are especially hard to debug, because they execute dynamically generated code in the browser. Some limited interactive debugging can be done with the browser’s Developer Tools, but often the code (as you wrote it) is not visible.

Logging is also fairly limited, as you cannot see any results until the script is finished executing, but the execution transcript, which shows the call details to many Apps Script services, is a useful feature.

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.

What Is Apps Script Good For?

As with VBA, if you are writing enterprise-level scalable applications, then Apps Script is probably not the right platform. Apps Script is perfectly suited for connecting to and using other APIs, opening up great integration possibilities for building apps that take data from many places and enrich your docs experience.

The HTML service exposes Apps Script functionality, and allows for the building of much more usable (but harder to achieve) UIs than those you could create with VBA.

Apps Script is very effective for getting something running quickly, whether a production solution or a prototype of a more scalable solution. Because it is part of the Googleverse, authentication to other services is largely built in, so identity and access management are part of the fabric of the platform.

Because it is in the cloud, development across multiple devices is no longer the complexity (and license) problem that it is with VBA, and the platform is always at the most recent version. These benefits allow you to build much more collaborative workflows and manage who is running what more confidently.

Many enterprises are still concerned about the security aspect of running apps and storing their data in the cloud. In many ways, centralizing one version of a document that supports real-time collaboration, and to which access to data and scripts can be finely controlled and monitored, is an extremely secure model.

In a distributed solution using various versions of Windows and Office on files stored on PCs, laptops, and shared network drives, email is often used for collaboration outside the enterprise, leading to externally exposed documents with scripts and data at different revision levels. Having one copy of a document, and being able to control access to it from anywhere, seems a more secure paradigm—at the very least it’s cleaner from a version control and document sprawl perspective.

What Are Google Add-Ons?

I’ve referenced the HTML service a number of times in earlier sections. This is a service that provides a templating system through which you can use HTML and JavaScript to create applications that run on the client browser but can communicate with server-side Apps Script.

With the HTML service, you can create an engaging UX that’s responsive (it runs client-side) yet still has (indirect) access to the Google Apps Script services and data.

Types of Add-Ons

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 for persistent 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.

Security

When the HTML service was first introduced, Google was concerned (at least I assume so) that it created security issues by allowing client-based JavaScript and HTML.

As a result, generated code was sanitized through the use of Caja, which had very strict rules about the structure of client-side JavaScript, with the outcome that most JavaScript frameworks were simply blocked and would not run. The performance of sanitized code was also extremely poor compared to its original state.

Nowadays, though, client code is run in an iframe environment, and the restrictive and poorly performing regime has now been removed, paving the way for more responsive add-ons.

The Publishing Process

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.

What Are Google Forms?

Google Forms are a simple way to collect data in a questionnaire format. A link to a form is sent to potential respondents.

Forms are designed using an interactive tool. The main features are as follows:

• Data is automatically stored in a spreadsheet.

• Data validation takes place in form fields.

• Routing can be controlled based on answers to specific questions.

• Forms can be extended through Apps Script.

For a quick solution, using Forms avoids the complexity of creating a UI with the HTML service and Apps Script and is a good compromise for short and simple data collection work, although more complex workflows can also be handled in Forms through Apps Script and add-ons.

What Are Microsoft Add-Ins?

VBA has access to an object called ScriptControl, which allows VBA to submit JavaScript (actually JScript) code to the Windows scripting engine for execution and return an answer.

Add-ins can also refer to Office app extensions loaded from separate template files, or from COM or VSTO libraries and managed by the Office app’s add-in manager.

Office add-ins have nothing to do with these capabilities.

Add-ins are the Office version of Docs add-ons. Microsoft has been playing around with this concept for a few years now: the idea of bringing JavaScript to Office was originally achieved with what they called the “JavaScript API for Office” and “Apps for Office,” but this capability has now been renamed to “Office Add-ins.”

In principle, an add-in is very similar to an add-on. There are two kinds of add-in:

• The taskpane add-in, which is equivalent to the sidebar add-on
• The content add-in, equivalent to the dialog add-on

Comparison

Microsoft and Google between them have created a complex soup of acronyms that’s hard to fathom. Here’s a summary:

• Microsoft Office add-ins have been around for a long time and refer to extensions than can be loaded to a document from a separate extension file.
• Microsoft has renamed what was previously known as “Apps for Office” to “Office Add-ins.” These are extensions written with the JavaScript API for Office that display in an HTML window, either to the side of the hosting document (taskpane add-in) or as a dialog box (content add-in).
• Google Apps add-ons are HTML applications that can communicate with server-based Apps Script, but that run in the client browser and present as either a sidebar (equivalent to the Office taskpane add-in) or as a dialog box (equivalent to the Office content add-in).

At first sight, Office add-ins and Apps Script add-ons look similar. They are both HTML- and JavaScript-based apps that run on the client under the control of Office and Apps Script, respectively. However, they are very different in realization.

The Microsoft JavaScript API:

• Has a fixed repertoire of methods and properties that can be used to fetch certain data from the underlying document
• Supports some level of binding (e.g., a client event is triggered if a bound cell changes value)

The Apps Script API:

• Can launch any server-side script from the client and get back a response of your own design
• Does not have any binding, but can emulate binding by periodically calling a server-side script that monitors for changes from the client.

This means that Microsoft add-in capabilities are limited to whatever is implemented in the API, whereas Apps Script can execute any customized script.

I believe we’ll see some changes with the Office 365 unified API (currently in preview). This exposes Office 365 services via a REST API, in much the same way as Google APIs expose Google services.

This may lead to a much more open solution for Office services.

¹ Exponential backoff is a technique for progressively slowing down the rate at which an operation is attempted to improve its chances of executing successfully.