Monday, December 15, 2014

Object-Oriented JavaScript (Part 2) - Practical Google Apps Script Example

Updated: 2014-12-15:
Update Notes: 
  1. The Range method setComment() is now deprecated and has been replaced by setNote().
  2. Added code formatting
  3. Corrected some typos and poorly phrased sentences

The previous blog entry introduced JavaScript object-oriented programming but did not go into any detail about the pattern used. After re-reading this entry, I think that an example focused on Google Spreadsheets might be more useful for explaining the concepts. To that end, the example given here encapsulates a spreadsheet header row. To envisage how encapsulating a spreadsheet header row might be useful, it helps to think of how a header row differs from the standard data rows it describes. Most sheets contain a header row and comments may be added to its cells to document the sheet contents. A range name for the header row can also be useful for referencing the header row cells. These and many other attributes can usefully be packaged up in a custom type by using the constructor/prototype pattern.

The objective of the code example given here is twofold:
  1. To discuss object-oriented JavaScript using the constructor/prototype pattern. This is a compound pattern that has become the standard mechanism in JavaScript for making custom types. It offers many of the benefits of classes in class-based object-oriented languages like Java. The details of the implementation of the constructor/prototype pattern will be discussed after the code is presented. 
  2. To provide potentially useful and modifiable example JavaScript code. This code is designed to have practical utility. It could be used, for example, whenever a new sheet has to be set up. It allows the header line to be customised programmatically and can be re-used whenever such a task is required. 

Note: The discussion that follows draws heavily from an excellent book by Nicholas Zakas entitled “Professional JavaScript for Web Developers”. This book is now in its third edition but I have used the second edition here. The code for this is on GitHub: ( However, since this is the most complex Google Apps Script example to date, the code is reproduced below to make it easier to discuss.

The code (See Github link for code download):  

Define Constructor Function
function HeaderRow(spreadsheet,
                                       overwritePrevious) {
 if (arguments.length !== 6) {
   throw {'name': 'Error',
          'message': '"HeaderRow()" ' +
          'constructor function requires 6 arguments!'};
 this.spreadsheet = spreadsheet;
 this.sheetName = sheetName;
 this.headerRowNumber = headerRowNumber;
 this.startColumnNumber = startColumnNumber;
 this.columnTitles = columnTitles;
 this.overwritePrevious = overwritePrevious;
 this.sheet = this.spreadsheet.getSheetByName(this.sheetName);
 this.columnTitleCount = this.columnTitles.length;
 this.headerRowRange = this.sheet.getRange(this.headerRowNumber,
Add Methods To The Prototype
HeaderRow.prototype = {
 constructor: HeaderRow,
 freezeHeaderRow: function () {
   var sheet = this.sheet;
 setHeaderFontWeightBold: function () {
 setFontStyle: function (style) {
 addCommentToColumn: function (comment, headerRowColumnNumber) {
   var cellToComment = this.headerRowRange.getCell(1,
 addColumnTitlesToHeaderRow: function () {
   var i,
   for (i = 1; i <= this.columnTitleCount; i += 1) {
     titleCell = this.headerRowRange.getCell(1, i);
     if (titleCell.getValue() && !this.overwritePrevious) {
       throw {'name': 'Error',
              'message': '"HeaderRow.addColumnTitlesToHeaderRow()"' + 
     titleCell.setValue(this.columnTitles[i - 1]);
 setHeaderRowName: function (rngName) {
   this.spreadsheet.setNamedRange(rngName, this.headerRowRange);
Define A Test Function
function test_HeaderRow() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
   sheetName = ss.getActiveSheet().getSheetName(),
   headerRowNumber = 3,
   startColumnNumber = 2,
   columnTitles = ['col1', 'col2', 'col3'],
   overwritePrevious = true,
   hr = new HeaderRow(ss,
 hr.addCommentToColumn('Comment added ' + Date(), 2);

Code Summary
The code is broken into three parts:
  1. The constructor function called HeaderRow() 
  2. The prototype part that assigns an object literal to the prototype of the HeaderRow() function
  3. The driver function called test_HeaderRow() that instantiates (creates objects of) the HeaderRow() constructor function and then calls its methods. 

The discussion that follows describes each of these in detail with reference to the Zakas book above.

The Constructor Function 
Constructor functions are just JavaScript functions that are called with the new operator. Calling the constructor function with the new operator creates a new object, sets this to point to the new object, executes the code inside the function, and returns the new new object. In order to distinguish constructor functions from ordinary functions, constructor function names are capitalised, hence “HeaderRow()” in place of “headerRow()” . This is an important convention. The code capitalisation informs calling code that the new operator should be used. In the example given here, removing the new on line 65 in the calling function test_HeaderRow() will cause an error. This error arises because, in the absence of new, the function call binds this to the global object!

Properties and this object reference: The constructor function above defines six parameters and checks the number of arguments given but it does not check argument types. Each of the given arguments are bound to the the newly created object referenced by this in the lines following the “ = argument” pattern. For example, “this.spreadsheet = spreadsheet;” binds the spreadsheet argument to the property named spreadsheet of the newly created object. Parameters and properties do not have to have the same names as they do here, but it helps readability if they do. Up to line 14, the code simply assigns the object properties to the given arguments. Once all the properties have been assigned to arguments, the code becomes more interesting. The code “this.sheet = this.spreadsheet.getSheetByName(this.sheetName);” uses the sheetName property to create a reference to the sheet object by calling the Spreadsheet object method getSheetByName(). This line shows how Google Apps object methods can be called from within the constructor function. the line beginning “this.headerRowRange = this.sheet.getRange” then uses sheet property to define the Range object for the header row. Note, the getRange() method is overloaded and the overload used here takes four arguments. These arguments, in the order given, are (1) the start row, (2) the start column, (3) the number of rows, and (4) the number of columns. The last line of the constructor function, “this.addColumnTitlesToHeaderRow();” calls a method defined in the prototype that uses the object properties to write the values in the array property columnTitles to the range defined for the header row. Note how this method uses the this.overwritePrevious Boolean property to check if it can over-write values in the header destination range and throws an error if this property is set to false and it finds a value in any cell of this range.

The prototype property of a function: Every function has a prototype property and this prototype property is also an object with its own properties. In this example, the prototype of the constructor function is assigned to an object literal. Doing this over-writes a property called ‘constructor’ so this is reassigned to the constructor name inside the object literal, the line reading “constructor: HeaderRow,”.  Methods in JavaScript are functions attached to objects. A number of methods are defined in the code example. These methods provide the object actions. They use the this reference to access object properties and to execute the methods of these object properties. Note how Google Apps objects can be accessed and manipulated just as easily as built-in JavaScript objects such as arrays.

The Calling Function To see the custom object in action, calling code is required. The function test_HeaderRow() does this. It creates a HeaderRow object (instance) by calling the constructor function using the new operator and passing six arguments. The arguments (in order) are:
  1. Spreadsheet object - the example uses the active spreadsheet. 
  2. Sheet name - the example uses the active sheet, the header row will be added to this sheet. 
  3. Header row number - the row number to where the header will be added, 3 in this example. 
  4. The column number where the header starts - 2 in this example. 
  5.  An array of strings where the elements are the column titles to be used in the header 
  6. A Boolean value indicating whether values in the header range can be overwritten. It is set to true here. 

The function then calls methods defined in the HeaderRow() prototype to customise the header. One of the methods freezeHeaderRow() calls a Google Apps Sheet method to freeze the rows.  All the other methods given in the prototype use methods defined in the Google Apps Range object to alter the appearance of the header and to add comments.

Advantages Of The Object-Oriented Approach Used Above
  • Code re-use! The custom object HeaderRow can be used in any application that needs to write header rows in a customised way programmatically. That’s most spreadsheet applications! 
  • All the functionality is grouped together in a tidy package thereby making the code more modular. 
  •  It provides a namespace mechanism. All properties are contained within an object rather than all being thrown together in the global namespace. This helps prevent naming collisions and other undesirable effects of globals. 

Why Constructor And Prototype Combined? The recommended approach for object-oriented programming in JavaScript is to combine these two patterns. The reasons for this are because each of the patterns has advantages and disadvantages. By combining them, we get all the advantages without the disadvantages. The constructor function is suited for defining instance properties because, unlike with the prototype pattern, the instance properties are not shared. This is important if the instance properties are reference types such as arrays where changes made in one object instance will interfere with changes made in another instance. However, if the constructor pattern is used for methods, it creates separate function objects in each instance. This is wasteful and unnecessary because if the methods are defined on the prototype object, then function objects are shared by all instances. All of this is very well explained in the Zakas book mentioned earlier.

This can be summarised in a succinct rule: Define instance properties in the constructor and define methods and shared properties on the prototype.

Code Improvements
The constructor function only checks the number of arguments given and not their types. Improvements in the constructor function could be made by adding type checking for each of the arguments.  Six parameters were defined for the constructor function and all are required. The code shown above only works when each of them is passed in the correct order. Passing arguments in this way is cumbersome and error-prone. The use of a configuration object pattern would help alleviate these drawbacks.

Postscript for clarification:
Running function test_HeaderRow() with overwritePrevious = true should run without error (Though you will be required to authorize the script the first time you run it). When running with  overwritePrevious = false you should see the following error as on this screenshot:

This error will stop the script, if you wish to deal with it, wrap the function call in a try ..... catch and do something appropriate in the catch block.

To see the code in action, I have shared this sheet at this link. To use it, first make a copy and you can then work on that and play with the code.


  1. Fairly new to Apps Script/Javascript, I appreciate the code including a test. I got everything to work except the IF statement containing the THROW, which fired every time (despite overwritePrevious being true) and then errored out on the THROW code.

  2. To see this working, I've made this sheet public:

    Go into the script editor and run the test function by setting "overwritePrevious = true" and "overwritePrevious = false". It works ok for me!

    1. Mick,

      sorry I missed your (amazingly fast) reply. When I try to access the link above it changes ?usp=sharing to #gid=0, so the script is not available. I presume that the error lies in my script. The problem is that when I copy the code above exactly the code won't save with an error on the line

      throw {'name': 'Error',
      'message': '"HeaderRow.addColumnTitlesToHeaderRow()"' +

      I am not familiar enough with the syntax to make the correction required.

    2. Mike,
      Yep, my fault, you can view but not edit so the script won't run as it changes the sheet. To solve, under the file menu in the spreadsheet, select "Make a copy", you'll then be able to work with that.



    3. Thanks Mick. I was able to copy and run the code. It worked creating the column headers, but failed with an error (not the error from the throw command) [object Object]line 48 file "Code")

      Is there some setup that the throw command requires because I get an error on your code at the throw command when the script runs with overwriteprvious set to false. It is almost like it doesn't know what to do with the throw. I am not familiar with it so is there some setting required for its use?

  3. Replies
    1. I got a notification of this post but no email with screenshot.

  4. Clarification and screenshot is in the added postscript.

  5. This comment has been removed by a blog administrator.

  6. This comment has been removed by a blog administrator.