- 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.
- 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.
The Spreadsheet Services API
- Programmatic interaction with Google spreadsheets begins with the four base classes defined within this API:
- 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).
- 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
- The sheets array could have been created in a single line like so:
- 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:
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.
Worked Example 2 - Referencing a Range and Setting a Range Property
- 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.
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:
- 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.
- rng_obj.getRange( String a1Notation )
- rng_obj.getRange( int row, int column )
- rng_obj.getRange( int row, int column, int numRows )
- 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: http://excel.tips.net/Pages/T002778_Official_Color_Names_in_VBA.html
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.
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
There will be some more detailed examples in the next and subsequent posts.