A Google Apps Script Boilerplate

Comments

This week’s challenge: distil my Google Apps Script (GAS) know-how into a Spreadsheets boilerplate, and document it.

About the challenge

At one stage Google Apps Script looked very promising - I even planned a whole series of GAS tutorials. That was until I realised the platform is still quite unstable and, more importantly, low priority for Google.

I still use GAS from time to time, whenever I need to exchange or format data with people who only really feel comfortable using spreadsheets. So I thought it’d be useful to crystallise the little I know into a boilerplate, perhaps adding some extra bits.

These scripts available on GitHub. You will need to create a new spreadsheet, and paste them into the script editor window (under Tools).

Minimal Google Apps Script boilerplate

1
2
3
4
Feature: minimal boilerplate
    In order to get some code up and running quickly
    As a developer
    I want the smallest possible GAS template

The script is available on GitHub.

Probably not everybody’s idea of ‘small’, but it’s the bare minimum with an example private method (one which comes useful quite often), a couple of menu entries to remind myself how to do separators, and example naming convention.

There are various ways of running scripts:

  • pressing the ‘play’ button on the script editor

  • presing the ‘run’ button on the script manager (neither very user friendly)

  • running scripts from triggers (onOpen, onEdit, onInstall)

  • choosing an item from a custom menu created by the script

  • clicking on a button or other UI element in a custom UI ‘app’ that sits on top of the spreadsheet - typically they are made up of panels and buttons to which you can assign event handler

The first two are not very user friendly. The triggers alone are not very useful - onOpen has only got permissions to run a few commands, and it is typically used to create menus and / or UI apps; onEdit is useful, but not always appropriate. So that leaves us with either a menu or a custom UI, and menus are the easiest, being simply a list of commands, each associated with a function. Also visually they are tucked away out of the way, which is nice. So that’s whay I tend to use.

1
2
3
//note that these need to be above the onOpen function below
function menu1(){ ns.menu1(); }
function menu2(){ ns.menu2(); }

These two lines (in a real script there may be more) define the functions used by the menu(s). Menus only accept top level functions, not object methods, not anything else - there is no way around it but starting your script with a list of functions you want your menus to call.

“ns” is just a placeholder for any variable name to act as namespace. It could even be done Java (or GWT) style as var example=example||{}; example.formatter = ... etc Namespacing helps make your code future proof - you never know, one day Google may relent and allow script sharing. Then not having a global function called ‘format’, say, would save all sort of hassle if your script is used with someone else’s.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
function onOpen() {
    //the Google servers are in California, and sometime timezone get all messed up
    //trying to remedy taht
    SpreadsheetApp.getActiveSpreadsheet().setSpreadsheetTimeZone( "GMT" );

    //this will hold the menu entrie
    var menuEntries = [];
    menuEntries.push({name: "Entry 1", functionName: "menu1"});
    //a null entry shows a divider
    menuEntries.push(null);
    menuEntries.push({name: "Entry 2", functionName: "menu2"});

    //create the menu
    SpreadsheetApp.getActiveSpreadsheet().addMenu( "MY MENU", menuEntries );
}

So now I can create my menu(s). Typically I only need one, but you can create more if you want. It’s rather self explanatory - you create an array of tuples and pass it, together with the menu name, to the addmenu method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
var ns = (function(){

// =========================================================
// PROPERTIES
// =========================================================
  var spr  =  SpreadsheetApp.getActiveSpreadsheet()      //shortcut

    /**
     * VAR NAMING CONVENTION
     * r1xxx = row for range (where first cell has ref 1,1)
     * c1xxx = column for range (where first cell has ref 1,1)
     * r0xxx = row for js arrays (where first cell has ref 0,0)
     * c0xxx = column js arrays (where first cell has ref 0,0)
     * rc1xx = a pair of range coordinates
     * rc0xx = a pair of js coordinates
     */

    // RANGE INDICES
    , R1START = 1  //starting Row
    , C1START = 1  //starting column

    // JS INDICES - as typically returned from range.getValues()
    , r0End = spr.getLastRow() - 1
    , c0End = spr.getLastColumn() - 1

    //pairs of points
    , rc1Start = [ 1, 1 ]  //where the value of Page name is
    ;

This is my namespace object, and illustrate a naming convention for variables. The naming convention is quite handy, as you are constantly mixing 0 and 1 indexed arrays and it’s good to have a reminder of which one is which. You could also use the “A1:B2” notation instead, but I find constantly building strings a nuisance.

I use spr as a shortcut for SpreadsheetApp.getActiveSpreadsheet(), which is needed all the time.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
/ =========================================================
// (pseudo) PRIVATE METHODS
// =========================================================
  /**
   * _indexOf
   * @private
   * goes through a sheet, doing each row. It can be limited to a range ( by rows and by cell) and finds the first occurrence of a value
   * @param {String} findme the value to find
   * @param {Sheet} shFindme the sheet with the range
   * @param {Number} rFindmeFrom the range in which to search, default 1
   * @param {Number} cFindmeFrom the range in which to search, default 1
   * @param {Number} cFindmeTo the range in which to search, default getLastRow
   * @param {Number} cFindme the range in which to search, default getLastColumn
   * @return {Array} the row and column indexes of the cell, -1, -1 if not found
   */
  function  _indexOf( findme, shFindme, rFindmeFrom, cFindmeFrom, rFindmeTo, cFindmeTo ){
    var r, c  //coords
      , r2, c2 //loop bounds
      , grid  //gets the range as js multi array
      ;
    if( !shFindme ){
      return [ -1, -1 ];
    }
    rFindmeFrom || ( rFindmeFrom = 1 );
    cFindmeFrom || ( cFindmeFrom = 1 );
    rFindmeTo || ( rFindmeTo = shFindme.getLastRow() );
    cFindmeTo || ( cFindmeTo = shFindme.getLastColumn() );

    grid = shFindme.getRange( rFindmeFrom, cFindmeFrom, rFindmeTo, cFindmeTo ).getValues();
    for( r = 0, r2 = grid.length; r < r2; r++ ){
      for( c = 0, c2 = grid[r].length; c < c2; c++ ){
        if( grid[r][c] === findme ){
          return [ r+rFindmeFrom, c+cFindmeFrom ];
        }
      }
    }
    return [ -1, -1 ];

  }

The namespace object keeps some methods as ‘private’, which is just a best practice.

_indexOf above is an example “private” method. It is a utility function to search a value within a range and return a pair of indices for the first occurrence.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
/ =========================================================
// PUBLIC METHODS
// =========================================================
    var facade = {

      /**
       * menu1
       * only doing JavaDoc from habit
       */
      menu1 : function(){
        Browser.msgBox( "Entry 1 works");
      }

      /**
       * menu2
       * only doing JavaDoc from habit
       */
     , menu2 : function(){
        Browser.msgBox( "Entry 2 works too");
      }

  }
  return facade;

 })();

Finally, the public method - these are the ones associated with the menu at the beginning of the script.

GAS boilerplate with version check

1
2
3
4
Feature:boilerplate with version check
    In order to ensure every copy of a common script is up to date
    As a developer
    I want a way to compare version of the script against a master version

The script is available on GitHub.

Since scripts can’t be shared [UPDATE: they can now], I find myself copying and pasting them into inidividual spreadsheets all the time. Which is ok, except that versioning becomes a pain. There are two ways around this. They both rely on GAS ability to open a spreadsheet from another.

It has been suggested you can save a script as text inside one of the cells of a spreadsheet, then read that cell from a script in another spreadsheet, and eval it. Personally I am not sure Google will allow that to continue - they made it clear one of the reason GAS development is moving at snail’s pace is security concerns. And also, there have been reports of large scripts passed that way failing randomly.

What I do instead is to keep a master spreadsheet with a version string for all the scripts I write, and then have each of the script check their own version number against it before it runs. It is surprisingly easy to do.

First of all, here’s an example spreadsheet - version number use semantic versioning

The first thing to do is tro create such a document, then decide on a unique label for the script you’ll be working on. Then add an entry with the script label in the first column, and the current version (typically 0.1.0) in the second.

1
2
3
4
5
//REPLACE WITH YOUR VALUES
var masterKey = "0Avx75VC2FEmAdEVmWl9uQkRuVjlxc0tmVDFSQ2wtaFE" //the key for your master document, the one with version info
  , thisVersion = "0.1.0"  //the version of this script
  , thisID = "script"   //label to find this script in the master version document
  ;

These three variables are used for locating and comparing the version number. masterKey is the key of the spreadsheet - to get it go to the master spreadsheet, look at the url, there should be a bit that looks like: key=0Avx75VC2FEmAdEVmWl9uQkRuVjlxc0tmVDFSQ2wtaFE&hl;. Copy the part between the = and the &hl.; Incidentally, you can also expose the id from within a script, the API offers a getId() method. thisVersion and thisID should be selfexplanatory, they are two cells in the master spreadsheet you have just created.

1
2
3
//note that these need to be above the onOpen function below
function menu1(){ if( gotofritz.utils.isUpToDate( masterKey, thisID, thisVersion ) ){ ns.menu1(); } }
function menu2(){ if( gotofritz.utils.isUpToDate( masterKey, thisID, thisVersion ) ){ ns.menu2(); } }

A couple of changes here. First of all, I have created a namespaced utils class, gotofritz.utils, which I keep separate from the main object. The class has a version check function, which only runs a meny entry if the script is up to date. It is rather inelegant to have the checks within the function themselves, not to mention inefficient, but other alternatives include having to declare the utils class at the top of the script, and I rather leave it tucked away at the bottom. Caching the results of the isUpToDate check would also be a good idea, but in practice it’s not something I’d need. Normally I load a spreadsheet, run a couple of commands, then I am done. Caching is not something I need, but if you do then it shouldn’t be too hard to add.

The onOpen function hasn’t changed. One thing I have changed is removing the private _indexOf method and add it to the utils class. This makes sense, not to mention that the isUpToDate method also needs to use it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
var gotofritz = gotofritz || {}
gotofritz.utils = (function(){

    //before checking other scripts, I need to check this object itself is up to date
    var utilsMasterKey = "0Avx75VC2FEmAdEVmWl9uQkRuVjlxc0tmVDFSQ2wtaFE"
      , utilsVersion = "0.1.0"
      , utilsID = "gotofritz.utils"
      , facade //wraps public nethods
      ;

//code....



      if( facade.isUpToDate( utilsMasterKey, utilsID, utilsVersion ) ){
          return facade;
      } else {
          return {
            isUpToDate : function(){ Browser.msgBox( "please update gotofritz.utils" ); return false; }
          }
      }

The new utils class is namespaced as gotofritz - note that using net.gotofrit or com.example won’t work, both net and com are reserved words. Also note that before checking anything else, the utils class checks its own version. If it’s not up to date it simply doesn’t initialise all the methods. I suppose one could have all the methods overwritten, but again it’s not something I really need in practice.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
/**
       * isUpToDate
       * checks a version number against one in a master document
       * @param {String} masterKey the key of the master document
       * @param {String} thisID a label for this script to allow us to find it in the master spreadsheet
       * @param {String} thisVersion the semantic versioning number
       * @return Booelan whetner thisVersion is loewer or equal than the one in the master document
       */
      isUpToDate : function( masterKey, thisID, thisVersion ){

          var shMaster = SpreadsheetApp.openById( masterKey ).getActiveSheet()
            , coords = facade.indexOf( thisID, shMaster )
            , latestVersion
            , aLatestVersion, aThisVersion  //used to compare the infividual components A.B.C in the version number
            , found = false
            , errorMsg = ""
            ;
          if( -1 === coords[0] ){
            errorMsg = "Could not compare " + thisID + " with master version: label not found";
          } else {
            latestVersion = shMaster.getRange( coords[0], 1+coords[1] ).getValue();
            if( "" !== latestVersion ){
              aLatestVersion = String( latestVersion ).split( "." );
              aThisVersion = String( thisVersion ).split( "." );
              if( 3 === aLatestVersion.length && 3 === aThisVersion.length ){
                if( ( +aLatestVersion[0] <= +aThisVersion[0] ) && ( +aLatestVersion[1] <= +aThisVersion[1] )  && ( +aLatestVersion[2] <= +aThisVersion[2] ) ){
                  found = true;
                } else {
                  errorMsg = "Your version is out of date. Please update "+thisID +" from the master spreadsheet";
                }
              } else {
                errorMsg = "Version number in wrong format. Master:" + latestVersion +", this: " + thisVersion;
              }
            } else {
              errorMsg = "Could not find a version number for " + thisID;
            }
          }
          if( !found ){
            Browser.msgBox( errorMsg );
          }
          return found;
      }

isUpToDate is quite simple and self-explanatory. Note that the indexOf method is used to locate the version number in the master document.

GAS boilerplate with UI.app

1
2
3
4
Feature:boilerplate with UI.app
    In order to see the possibilities
    As a developer
    I want to create a new UI interface to a spreadsheet

This is too long and will need its own weekly challenge.

Challenge 66% complete

Basically I managed to polish all the bits of scripts I had into a template, but didn’t have enough time to learn new stuff.

Comments