Friday, January 7, 2011

Using the Google App Scripting API - Part 1

See chapters 4 and 5 in this book on Leanpub

  • User-defined functions which were discussed previously take input either from spreadsheet cells or as user-supplied literals and return the value to the cell in which they are invoked.  However, they cannot be used to alter formats or display dialog boxes, insert sheets, etc.  To build real spreadsheet-based applications requires manipulation of objects provided by the Google APIs.
  • Consider Excel VBA programming.  It requires the programmer to learn the programming language, the Excel VBA dialect, and the API, i.e. the Excel Object Model.  The second part is, arguably, more difficult than the first.  Furthermore, programmatic interaction with other applications like MS Word or relational databases requires knowledge of additional APIs, the Word Object Model and ActiveX Data Objects (ADO), respectively for the examples cited.  
  • Similarly for Google spreadsheet programming.  The Google App Scripting programmer has a sizable and growing list of APIs at his/her disposal but has to learn and use JavaScript to exploit their functionality.  
  • For applications that only need to work with the Google spreadsheet, one may only need to use the Spreadsheet Services API.  But just as one can extend the range and power of Excel applications by using ADO to interact with relational databases, for example, one can likewise utilise the other APIs to enhance Google spreadsheet applications.
  • As JavaScript is the language of Google App Scripting, learning and applying it in one setting, e.g. for spreadsheet programming, is a good investment in time and energy since those same skills can be applied to experiment with and utilise other APIs such as Jdbc Services for accessing databases.

The Spreadsheet Services API
  • Programmatic interaction with Google spreadsheets begins with the four base classes defined within this API:
  1. Range
  2. Sheet
  3. Spreadsheet
  4. SpreadsheetApp
  • These four classes constitute a hierarchy, ranges are contained in sheets, which are contained in spreadsheets, which in turn are created by the SpreadsheetApp class.  
  • The Google documentation provides concise descriptions of these classes along with tables of the class members (methods and properties).
  • JavaScript is used to create objects of these classes and to get and set attributes of the created objects.  Without JavaScript the classes are useless but with it, they are the building blocks of useful spreadsheet applications.
  • Some other classes in the Base Services API are also used.  A case in point is the Browser class which is  used in some of the examples below to display message and input dialogs analogous to those displayed by the VBA functions MsgBox and InputBox.

Simple Demonstrations of the Fundamental Objects Representing Google Spreadsheets
  • The examples given here are just starting points to explore the spreadsheet API.  Proper application examples will be given in later posts.
  • A spreadsheet is composed of sheets and a sheet is a grid of cells where each cell has a row and a column number.
  • This hierarchy is mirrored in the Google Spreadsheet Services API where to get to a range object one needs to create a spreadsheet object from where one can then access range objects from its associated sheet objects.
  • Code may clarify this:

Worked Example 1: Retrieving a List of Sheet Objects and Displaying Their Name Property
// Display the name of each sheet in the active spreadsheet.
function listSheets () {
   // Create an object representing the active spreadsheet.
   var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   // Retrieve an array of sheet objects from the activeSpreadsheet object.
   var sheets = activeSpreadsheet.getSheets();
   // Loop over the array of sheet objects and display the name of each sheet object.
   for ( var i = 0, sheetCount = sheets.length; i < sheetCount; i++ ) {
Code Notes on Worked Example 1
  • The sheets array could have been created in a single line like so:

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
Doing so sacrifices clarity for brevity.   Note to VBA programmers, the parentheses are not optional when no arguments are required!
  • The above code simply retrieves a property of the sheet object but this code can be adapted to get other properties, get and return the total sheet count (sheets.length), set properties, e.g. setName(name), or return the sheets array.
  • Excel VBA code for Comparison:

'  Display the names of all worksheets in the active workbook by
‘ iterating over the Workbook Sheets collection.
Sub ListSheets()
   Dim sheet As Worksheet
   For Each sheet In ActiveWorkbook.Sheets
       MsgBox sheet.Name
   Next sheet
End Sub

The Range Class
  • Spreadsheet programming revolves around manipulation of ranges where ranges represent one or more spreadsheet cells.
  • At the time of writing, the Google App Scripting Range class has 88 members all of which are methods.  These methods can be used to get and set properties such as formats of cells and to return other ranges.
  • Consider a single cell, the smallest possible range, in terms of where it exists, how it is referenced, what it can contain, how it is formatted, and what one can do to it and with it:
    • Contained in a sheet, which is contained in a spreadsheet that is composed of one or more sheets.
    • Referenced by a user- or programmer-defined name or address.
    • Contains data (text, numbers, or dates) calculations (formulas), and/or meta-data as comments.
    • Contents and cell background have formats such as colors and fonts.
    • Can be sorted in relation to other cells, it can be copied, it can be used to reference and return other cells.
  • All aspects of a cell can be pragmatically manipulated in JavaScript by using methods of the Range class.

Worked Example 2 - Referencing a Range and Setting a Range Property
  • The following code does not do anything useful but it does showcase in a visual manner how to deploy JavaScript to manipulate the Google spreadsheet.  
  • The first function calls the second one repeatedly and changes the background color of a range of cells.  
  • To use, paste the two functions into the script editor (Tools->Scripts from the spreadsheet menu), save the content of the script editor, select “call_changeMe” from the dropdown list and hit the run button.  Ensure that the spreadsheet is visible and watch the color of range A1:A10 change repeatedly.

// Set range "A1:A10" of the active sheet to different background colors.
// Display each color for one second and then cycle to the next color
function call_changeMe() {
   colors = ["red","blue","yellow","green","black","white"];
   for ( var i = 0, len = colors.length; i < len; i++ ) {
       // Suspend script for one second to allow time to see color change
       // Call "changeMe()" passing in a cell address and a color from the array.
       changeMe("A1:A10", colors[i]);
       // Force an update to see the color change

// Given a cell address and a color, change the background color of
// that cell on the active sheet.
function changeMe(cellAddress, bgColor) {
     // Get an instance of the active sheet.
   var activeSheet = SpreadsheetApp.getActiveSheet();
  // Get a range object from the active sheet object.
   var cells = activeSheet.getRange(cellAddress);
   // Call a Range method to set the range background color.

Example 2: Code Notes
  • The above code demonstrates one way to get a reference to a range object by using the class SpreadsheetApp to return the active sheet.  It then retrieves a range object from the returned active sheet object by using a range address string.  The setBackgroundColor() method is then called on the range object passing a color string as an argument.
  • The method calls in the function “changeMe()” can be chained so this function could be written as:

// Given a cell address and a color, change the background color
// of that cell on the active sheet.
// Chain the method calls so that there are no local variables need be declared.
function changeMe(cellAddress, bgColor) {
  • The above code works as before and is more concise, albeit at the expense of being less readable.  Note to VBA programmers, the parentheses are required even if there are no arguments and their omission returns a function object.
  • Note when using a cell address, the sheet object that the cell address belongs to must be specified.
  • The getRange() method has four overloads that are distinguished by their arguments, see Google documentation.
  1. rng_obj.getRange( String a1Notation )
  2. rng_obj.getRange( int row, int column )
  3. rng_obj.getRange( int row, int column, int numRows )
  4. rng_obj.getRange( int row, int column, int numColumns )
  • Overloads 3 and four are variations on overload two where both parameters numRows and numColumns are optional.
  • The first overload was the one used in the example above, that is a string giving the address was passed as an argument to the a1Notation parameter.

  • Here is Excel VBA that does something similar

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) ' Does not work on Mac Excel
'links to VBA sources used:
' Setting up the sleep command
' VBA colors:
Sub call_ChangeMe()
   Dim colors: colors = Array(1, 2, 3, 4, 5, 6, 7, 8, 2) 'Colors are stored as variant type
   Dim cellAddress As String: cellAddress = "A1:A10"
   Dim i As Integer
   For i = 0 To UBound(colors)
       Call ChangeMe(cellAddress, Int(colors(i)))
       MsgBox "See Color Change!"
       'Cannot run Sleep on Mac Excel so use the MsgBox to pause so the color change can be seen
       'Sleep 1000 ' Use this on PC and comment out previous line.
   Next i
End Sub

Sub ChangeMe(cellAddress As String, colorIdx As Integer)
   Dim cells As Range: Set cells = ActiveSheet.Range(cellAddress)
'  ActiveSheet can be omitted here as VBA assumes ActiveSheet if no sheet is specified.
   cells.Interior.ColorIndex = colorIdx
End Sub

There will be some more detailed examples in the next and subsequent posts.

No comments:

Post a Comment