Thursday, December 30, 2010

Getting Started

Note Added 05-FEB-2013: There are three free chapters in PDF format that introduce this topic with examples and discussion here @Leanpub

Motivation For Learning Google App Scripting
  • JavaScript is an important programming language these days especially for developing web applications.  It is the language used to write Google App Scripting scripts.   
  • Used VBA in the past to program Excel so learning JavaScript to do the same for Google spreadsheets seemed like a logical place to begin.
  • There is a lot of hype about “The Cloud”, software as a service, etc.  It would be nice to get some practical programming experience in using it.
  • Day-to-day work involves collaboration with external groups and Google applications are excellent collaboration tools.  The ability to enhance them using Google App Scripting makes them even more useful.
  • Learning Google App Scripting for spreadsheets can serve as a springboard to applying it to other Google applications and services.  This area is developing rapidly so skills acquired now might be even more useful in the not-too-distant future!

The following notes were while learning Google App Scripting, they assume basic programming knowledge in a language other than JavaScript and will probably be most accessible to those who have experience in programming some other spreadsheet application.  

Some worked examples are given in both Excel VBA and JavaScript in order to highlight similarities and differences between these two environments.  This is not a “sell” for Google App Scripting but rather an exercise in learning a new programming language and a new programming environment.

These notes have a very practical bent, theory is well covered in books and on-line resources.  The emphasis here is on working code that exemplifies JavaScript and the Google App Scripts API.

Google App Scripting - What Is It?
The best definition is probably given by Google themselves: “Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services.” (  

Note that the JavaScript written in the Google spreadsheets editor is not executed locally, instead, it is executed in the cloud on Google servers.  This is in contrast to JavaScript in web pages where it is executed by the browser on the client machine.

Spreadsheet applications are for historical reasons known as “macros”, that term is not used by Google nor is it used here.

Getting Started
To begin using JavaScript to write Google spreadsheet applications, all you need is:
  1. A modern web browser.
  2. A gmail account.
  3. An Internet connection.  

Given these three things, you can begin to write extensible, shareable, platform-independent spreadsheet applications.

There are two challenges
  1. Learning JavaScript itself.  JavaScript is considered “easy” by some though it has its own idiosyncrasies and idioms so truly learning it is not a trivial task.  Also those coming from VBA will quickly see that JavaScript does many things very differently.
  2. Learning the Google Spreadsheets Application Programming Interface (API) and other APIs such as the Base Services API.  

These two challenges are also faced when learning JavaScript for web page development.  In that environment, the Document Object Model (DOM) must be learned in addition to the language itself.  

Some source books on JavaScript
  • Powers, Shelley (2009). Learning JavaScript: Add Sparkle and Life to Your Web Pages (2nd ed.).  O’Reilly & Associates. ISBN 0-596-52187-1
  • Flanagan, David (2006). JavaScript: The Definitive Guide (5th ed.). O'Reilly & Associates. ISBN 0-596-10199-6.
  • Crockdord, Douglas (2008). JavaScript: The Good Parts. O’Reilly & Associates.  ISBN 0-596-51774-2.

The Powers book assumes no programming experience, the emphasis is on web development but it is a good starting point to learn JavaScript.
The Flanagan book, known as the Rhino book due to its cover, is as it says, definitive. A new edition is due out 2011.
The Crockford book is for experienced programmers and is highly opinionated in the best sense of the word.  The author is a recognised JavaScript authority who, among other things, invented and popularised JSON (JavaScript Object Notation) as a data interchange format.  The notes given here and the coding style used owe a lot to this book.

General Notes on Spreadsheet Programming

  • Spreadsheet applications should be developed with the same degree of rigour as any other application.

  • Spreadsheet applications in general and Excel VBA applications in particular, have a bad reputation in many quarters.  This bad reputation owes more to poor coding standards than to inherent weaknesses in VBA or Excel where many so-called “macros” are released to users with little or no testing or error handling code.

  • A good book on spreadsheet programming
    • Bovey, Rob et al (2009). Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd ed.).  Addison-Wesley Microsoft Technology. ISBN 0-321-50879-3.

Google App Scripting Resources
  • The main source of information on the Google Spreadsheet API is the Google documentation itself and the Google tutorials.  The documentation contains extensive information on the classes and their associated methods and properties in addition to relevant code examples while the tutorials provide longer examples of working code.  
  • There is also a video link on the Google App Scripting home page that gives background and worked examples that showcase Google App Scripting generally.
Note on Google Spreadsheet Limits

  • An Office 2007 Excel sheet can contain 1,048,576 rows and 16,384 columns, that is 17,179,869,184 cells!
  • In contrast, at time of writing, a Google spreadsheet is limited to 400,000 cells, see Google spreadsheet limits.

Writing Google App Script Code
  1. In the Google Documents window, select “create new spreadsheet” and save it with any name you wish.
  2. From the spreadsheet menu, select “Tools->Scripts->Script editor...”.
  3. The script editor displays the following JavaScript function stub:

function myFunction() {
Over-write the above function with the following function definition:
// Declare a variable called 'user_name' and assign it to the user name of the the Google account user.
function displayUsername() {
   var user_name = Session.getActiveUser().getUsername();
   Browser.msgBox("Hello, " + user_name);
Save the script file and run the function “displayUsername”.  A message box is displayed with the text “Helo, <your user name>”.  You may need to select the browser spreadsheet window to see the displayed message box.  Click its “OK” button to dismiss it. Nothing very exciting or useful in any of the above but it does demonstrate how to:
  • Create and run a JavaScript script in a Google spreadsheet.
  • Chain method calls to return a value that is then assigned to a variable.
  • Display a message to the user.

The above function does not use any spreadsheet API classes but uses two classes, Browser and Session, from the Base Services API.  The Browser class provides equivalent functionality to the MsgBox and InputBox functions in VBA and will be used extensively throughout to prompt for program input and display output and options.  The JavaScript editor autocomplete is a very useful feature for learning what members a given class contains though it is not nearly as feature-rich as the Excel VBA editor.
The “displayUsername” function above does not use Google spreadsheet API so let’s rectify that by changing it to write to a spreadsheet cell rather than displaying it in a message box.

// Write the user name to cell A2 of the active sheet.
function displayUsername() {
   var user_name = Session.getActiveUser().getUsername();
In the code above extensive method chaining is used for both getting the user name and for setting the cell value.  Both of these lines can be broken down into separate method calls and the return values of each method call can be assigned to intermediate variables.

For comparison purposes, here is the Excel VBA code to do something similar

' Write the user name to cell A1 of the active sheet
Sub DisplayUserName()
   Dim user_name As String
   user_name = Application.UserName
   ActiveSheet.Range("A1") = user_name
End Sub

Notes on the Example
  • ActiveSheet.Range(“A1”) could also have been written as ActiveSheet.Range("A1") .Value but since “Value” is the default property of “Range” in VBA it can be omitted.  
  • As this is spreadsheet programming, the code in the two languages looks quite similar.  In both instances a value is being written to a range.
  • When more substantial examples are encountered that manipulate ranges of cells, sheets and workbooks, the similarities will become more apparent   
  • The great difference between the JavaScript and VBA examples lies not in the code itself but rather in the mode of execution:  The VBA code was executed locally on a client machine inside the Excel application while the JavaScript code was executed in the cloud on a Google server.

Next posting will be on User-Defined Functions

1 comment:

Note: Only a member of this blog may post a comment.