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.

Friday, December 12, 2014

Copying Tab-Separated Variable (TSV) File Contents Into A Google Spreadsheet

Note: Google Spreadsheets comes with a ready-made function for importing data tables (including tab-separated and comma-separated tables), it is called IMPORTDATA here is the Google documentation for it.  The code below is useful if you need to grab the data and process it first or do something with it other than just write it to a spreadsheet. I wrote the code here before I was even aware of the function IMPORTDATA.

Tab-separated value (TSV) files are a a simple and popular data interchange that are generated by many tools and applications. They can be easily imported into spreadsheet applications like Excel. I frequently like to view and share them in Google Spreadsheets so, to facilitate this, I wrote a few GAS functions to take an uploaded TSV file in Google Drive and copy its contents into a sheet. Links to the sample data and the spreadsheet output with the GAS code are available below.

Summary of the GAS Code
  • Given a TSV file name, return its file ID
  • Read the entire file contents into a single string
  • Create an array by splitting the string using the newline (\n) character
  • Loop over each line and split line on tabs (\t) thereby building an array of arrays
  • Copy the array to a sheet

Here are the functions that implement the above tasks

Get the file ID for the target file
function getFileIDFromName(fileName) {
  // Return the file ID for the first
  // matching file name.
  // NB: DocsList has been deprecated as of 2014-12-11!
  // Using DriveApp instead.
  var files = DriveApp.getFiles();
  while (files.hasNext()) {
    var file =;
    if (file.getName() === fileName) {
      return file.getId();
Return the file contents as an array of arrays
function getTsvFileAsArrayOfArays(tsvFileID) {
  // Read the file into a single string.
  // Split on line char.
  // Loop over lines yielding arrays by
  //  splitting on tabs.
  // Return an array of arrays.
  var txtFile = DriveApp.getFileById(tsvFileID),
      fileTextObj = txtFile.getAs('text/plain'),
      fileText = fileTextObj.getDataAsString(),
      lines = fileText.split('\n'),
      lines2DArray = [];
  lines.forEach(function (line) {
  return lines2DArray;
Write the array of values to the active sheet
function writeArrayOfArraysToSheet(tsvFileID) {
  // Target range dimensions are determine
  // from those of the nested array.
  var sheet = SpreadsheetApp.getActiveSheet(),
      arrayOfArrays = getTsvFileAsArrayOfArays(tsvFileID),
      dimensions = {rowCount: Math.floor(arrayOfArrays.length),
                 colCount: Math.floor(arrayOfArrays[0].length)},
  targetRng = sheet.getRange(1, 1, 
Prompt user for a file name and run the TSV to sheet operation
function runTsv2Spreadsheet() {
  // Call this function from the Script Editor.
  var fileName,
  fileName = Browser.inputBox('Enter a TSV file name:',
  fileID = getFileIDFromName(fileName);
  if (fileID) {
To run, you can paste the above functions into a Script Editor and try them out on your own data. To run, just upload a TSV file to your Google Drive, and execute the function named runTsv2Spreadsheet().
If you want some sample data, I have made my test TSV file public so you can just copy it, link here!
If you wish to see what the spreadsheet output should look like, the generated sheet is linked here! The code is also there, so just open the script editor and have a look. You can copy both the spreadsheet and TSV file to have a play with them.

Important Points About The Code Above

  • Memory usage: The code given reads the entire file into a GAS string. I have used it for files with circa 13,000 rows but if the target TSV file is very big, you may run into problems.
  • The code assumes each record is separated by a new line and that each filed (column) in the record is tab-separated.
  • Duplicate file names: The function used to retrieve the file ID, getFileIDFromName(), returns the ID for the first matching file name. You can have duplicate file names in the same folder in Google Drive because they are distinguished by unique file IDs and not names!
  • DocsList has been deprecated as of 2014-12-11 so DriveApp has been used in its place in function  getFileIDFromName()
  • The examples above provide a re-cap on subjects covered in earlier blog entries and in my Leanpub book.