Getting Started With Google Apps Script

Comments

Google Apps Script (GAS) is an implementation of Javascript that runs on Google cloud servers and allows access to Google services. Support is still somewhat flaky, but if you are already familiar with Javascript it is an exciting technology to get into. In this simple Google Apps Script primer, we will use a Google Docs spreadsheet to learn the basics

A generic spreadsheet utils

The easiest way to get started with GAS is by playing with spreadsheets, so let’s create one in Google Docs. The aim is to create a generic collection of utilities to perform simple text manipulation tasks. Let’s start from the very beginning.

Signing up to Google Docs

If you have never used Google Docs before, you’ll need a Google Apps account - confusingly, this is not the same as a GMail account, although many people end up using the same login name. In fact you can register at docs.google.com using any email address you want.

Either way, create a new spreadsheet, give it a name (for example GAS utils), and then you are ready to go.

Hello world

Let’s start with the traditional ‘Hello World’ script. From the app menu underneath the Google Docs log, select Tools, then Scripts, and Script editor.



A popup window will appear - that’s where you are going to write your scripts. It has some useful features (code completion, parenthesis balancing) and some annoying ones (code formatting), but you have no choice in the matter.

In that editor you will create a list of Javascript functions - think of each function as an entry in a menu like “Convert to upper case”, “Generate emails” or, indeed, “Hello World”. Later on, all these function WILL, in fact, become entries in a menu. You can create all sort of complicated classes and inheritance chains, and so forth, but ultimately you will only be able to run one of these functions - if you are planning anything fancier you will have to wrap your code into a function in the global scope.

The script editor opens with an unsaved script, pre-populated with an empty function

1
2
3
function myFunction() {

}?

change the function name to helloWorld, and enter this code:

1
2
3
function helloWorld() {
  Browser.msgBox("Hello World");
}

Then click on the save icon - the first time you do so, the editor will ask you to give a name to the script (I used TEST). Once saved, the editor will populate the pulldown menu above the script with the name of all functions it can run - in this case there is only one, helloWorld. Incidentally, you can stop a function appearing in the menu by ending its name with an underscore, e.g. function test_ () { …


Click on the triangle next to the function name and there you have it - a simple alert window with a message in it.

Google Apps Script reference

That was a simple script with a single instruction in it. It told the global Browser object (a GAS object, not a standard Javascript one) to run the msgBox method. The Browser object is one of a few global GAS objects and classes available to you. The other global object (or Service, in GAS talk) you will be using is SpreadsheetApp. Its methods return objects of class Spreadsheet, Sheet, and Range, so you may want to have a look around the GAS reference to see what their available methods are. You’ll see a lot more objects in the reference - they deal with other apps like GMail or Google Finance, but that’s a topic for other tutorials.

How to run functions in GAS

That’s all well and good, but will you have to open the script editor and select a function name from the menu every time you want to run a function? Function names are often geeky, and having to open a popup window is not ideal. An alternative is “Manage…”, under the Script menu - it shows a pop up list of all functions. But that comes right in the middle of the spreadsheet, covering most of it. That means that you can’t select a cell for example. Another non-starter then. Luckily, there is another way - you can actually create menus and have them go in the menu bar, to the right of Help

The spreadsheet class has an addMenu method - it takes a string as name, and an array for the entries. You can create a list of functions, put their name into an array, and feed that to addMenu, and then use that to create the menu (note the ‘null’ to create a separator):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
function helloWorld() {
  Browser.msgBox("Hello World");
}

function ciaoWorld() {
  Browser.msgBox("Ciao World");
}

function salutWorld() {
  Browser.msgBox("Salut World");
}

function createMenu(){
  var menuEntries = [
    { name : "Say it in English", functionName : "hellowWorld" },
    null,
    { name : "Dillo in Italiano", functionName : "ciaoWorld" },
    { name : "En Fran??ois aussi", functionName : "salutWorld" }
   ];
   SpreadsheetApp.getActiveSpreadsheet().addMenu( "TEST MENU", menuEntries );

}

Save it, find createMenu in the Script editor, run it and there you have it - your very own custom menu.

Using trigger to create menus in Google Docs spreadsheet

So that’s all well and good, but you still have to run createMenu manually from the Script editor or the Manage pop up. Spreadsheet have some built in event handler (or triggers) - at the time of writing they are document open, cell edit, and form submit.

The onOpen trigger

When a spreadsheet is opened, the GAS engine generates an open event, and will run functions you have associated with that event. You can do that in two ways.

The Script editor has a Triggers menu, which lets you associate functions to events (or call them at regularl intervals). In the case of the open event though, it is much simpler to rename createMenu to onOpen - the GAS engine will automatically run it when the document is opened. Try that, save the script, and close and re-open the spreadsheet - the menu should now appear automatically (it may take a few seconds for it to happen)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
function helloWorld() {
  Browser.msgBox("Hello World");
}

function ciaoWorld() {
  Browser.msgBox("Ciao World");
}

function salutWorld() {
  Browser.msgBox("Salut World");
}

function onOpen(){
  var menuEntries = [
    { name : "Say it in English", functionName : "hellowWorld" },
    null,
    { name : "Dillo in Italiano", functionName : "ciaoWorld" },
    { name : "En Fran??ois aussi", functionName : "salutWorld" }
   ];
   SpreadsheetApp.getActiveSpreadsheet().addMenu( "TEST MENU", menuEntries );

}

Part 2

I have abandoned the idea of a complete serious of tutorials, but I have created a a couple of GAS boilerplates as one of my weekly challenges

Further reading

Google has some GAS tutorials which are well worth going through.

Comments