A Google Apps Script boilerplate

google apps script
This post is old, and probably obsolete

I used to do a lot of GAS programming, but Google's never seemed to interested in that product so I gave up. Here I document a simple boilerplate project so that I don't forget how to do it.

About GAS

At one stage Google Apps Script looked very promising - I even started a whole series of tutorials (since deleted). 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.

Minimal Google Apps Script boilerplate

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.

//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 (EDIT they have). Then not having a global function called 'format', say, would save all sort of hassle if your script is used with someone else's.

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.

var ns = (function(){

// =========================================================
// PROPERTIES
// =========================================================
  var spr  =  SpreadsheetApp.getActiveSpreadsheet();
    /**
    * 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 - excel style, i.e. 1-based
  //starting Row
  var R1START = 1;
  //starting column
  var C1START = 1;

  // JS INDICES - as typically returned from range.getValues()
  var R0End = spr.getLastRow() - 1;
  var C0End = spr.getLastColumn() - 1;

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

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.

/ =========================================================
// (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
    var r2, c2; //loop bounds
    var 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.

/ =========================================================
// 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.