Chapter 1: Introduction

1.1 Google Sheets

Google Sheets is one of the core components of Google cloud applications. If you have a Gmail account, you can create and share your spreadsheets with others, even with those who do not have a Gmail account. Google Sheets offers a comprehensive set of standard spreadsheet features and functions similar to those found in other spreadsheet applications such as Microsoft Excel. In addition, it also supports some novel features such as the very versatile QUERY function and regular expression functions such REGEXMATCH.

What really distinguished Google Sheets from desktop spreadsheet applications like Excel is its cloud nature. The spreadsheet application runs in a browser and the spreadsheet files themselves are stored remotely. The spreadsheet files can be shared with others in read-only or read-edit modes making them ideal collaborative tools. Spreadsheets form just one part, albeit an important one, of the Google suite of products. Others are Google Documents, Gmail, calendars, forms, and so on and all of these products are inter-operable at least to some degree resulting in a very productive environment perfectly suited to collaborative work.

When I began using Google Sheets back in 2010 it was quite limited in terms of data volume, speed and functionality. It has undergone significant development since then and got a major upgrade in March 2014. If your experience of Google Sheets was negatively influenced by experience with earlier versions, I encourage you to try it again, I think you will notice a big improvement. The old 400,000 cell limit per spreadsheet is gone and is now at least 2,000,000. It will comfortably deal with tens of thousands of rows which is, I believe, quite acceptable for any spreadsheet. Other spreadsheet applications such as Excel can handle a million plus rows but when data volumes grow to this size, it is advisable to switch to a database or a dedicated statistical application to handle such data sizes.

1.2 Google Apps Script (GAS)

The Google Sheets application also hosts a programming language called Google Apps Script (GAS) that is executed, not in the browser but remotely on the Google cloud. Google define Google Apps Script as follows:

“Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services.”

If Google Sheets is so feature-rich, you might wonder why it needs to host a programming language. Here are few reasons why GAS is needed:

• Write user-defined functions for Google Sheets
• Write simple “macro” type applications
• Develop spreadsheet-based applications
• Integrate other Google products and services
• Develop Graphical User Interfaces (GUIs) that can be run as web applications
• Interact with cloud-based relational databases via Google JDBC Services.

GAS plays a similar role in Google Sheets to that played by Visual Basic for Applications (VBA) in Excel. Both are hosted by their respective applications and both are used to extend functionality and integrate with other applications and services.

1.3 JavaScript or Google Apps Script?

The emphasis here is on using GAS to enhance and control Google Sheets. Other Google services are discussed in the context of how they can be used with Google Sheets. Since GAS is JavaScript (Google describe it as a sub-set of JavaScript 1.8), there will inevitably be discussion of JavaScript as a programming language. There is, therefore, some discussion of JavaScript topics as they relate to the code examples given.

Regarding terminology, when discussing a general JavaScript feature, the code may be referred to as “JavaScript” but when dealing with a Google App specific example, it may be referred to as “Google Apps Script” or “GAS”. The meaning of whichever term is used should be clear from the context. For example, the Spreadsheet object is central to Google Sheets programming. It is, however, provided by the hosting environment and is not part of JavaScript itself. This duality of the programming language and the objects provided by the hosting environment is similar to JavaScript running on the web client and the Document Object Model (DOM) entities that it manipulates.

1.4 Summary Of Topics Covered

This book aims to provide the reader with a solid knowledge of the GAS language both as it applies to Google Sheets and how it is used to allow Google Sheets to inter-operate with other Google products and services as well as with relational databases.

Chapter 2 introduces the GAS language and sets the scene for the chapters that follow. One of the most important applications of the hosted spreadsheet language, be it VBA or GAS, is to allow users to write user-defined functions (also known as custom functions). These are covered in depth in chapter 3 and this chapter merits careful reading for any readers not familiar with JavaScript. Functions are central to JavaScript and, by extension, to GAS.

The Spreadsheet, Sheet, and Range objects are crucial for manipulating Google Sheets with GAS and these objects are covered in depth in chapters 4 and 5. All subsequent chapters assume that the reader is comfortable with these objects, their methods and their uses.

Having covered the basics of user-defined functions and the fundamental spreadsheet objects, chapter 6 explains how GAS can be used to work with a back-end relational database (MySQL). Spreadsheets are great tools but they have their limitations and, as applications increase both in complexity and in data volume, there comes a point where a more robust data storage solution is needed that that offered by spreadsheets.

In order to build spreadsheet applications some sort of Graphical User Interface (GUI) is usually required. Chapters 7 and 8 cover menus, alerts, prompts and user forms created using Google Html Service. Creating forms with Html Service offers the opportunity to develop web skills such as HTML, CSS and client-side JavaScript. For those not experienced in frontend development, this material offers a gentle introduction to a very important skill.

In addition to being an excellent collaborative tool, Google Sheets is part of a larger set of applications with which it can interact. GAS written in Google Sheets can be used to manipulate other Google products such as Google Drive, and Google Calendar and this aspect of GAS programming is covered in chapters 9 and 10.

For those coming to GAS from Excel VBA, appendix A will be of especial interest as it gives example code and explanations of how to perform common spreadsheet programming tasks in both languages. Appendix B gives an example of a quite complex spreadsheet application written in GAS that brings together much of the material covered earlier. It describes how to build an application that takes spreadsheet data as input and uses it to generate SQL for table creation and row insertion. Appendix C discusses additional GAS and JavaScript resources that will be of interest to readers.

1.5 Software Requirements For This Book

Not many! Modern cloud-based applications such as Google Sheets greatly reduce the technical barriers for new entrants. In the old days, you might have needed a specific operating system running some proprietary and often expensive software to get started. Not anymore! Anyone with a modern browser, an internet connection and a Gmail account running on Windows, Mac OS X or any version of Linux will be able to follow along with this book and run the code examples given. The code examples should run in any modern browser but I mainly use Chrome and Firefox and never use Internet Explorer (IE) although I expect that all the code will run fine in any post IE7 version.

1.6 Intended Readership

This book is written for those who wish to learn how to programmatically manipulate Google Sheets using GAS. I began learning GAS for two reasons. Firstly, I was using Google Sheets as a collaborative tool in my work and, secondly, I had become aware of the increasing importance of JavaScript generally and was keen to learn it. Being able to use the JavaScript-based language that is GAS in spreadsheets appealed to me because I was already quite experienced in programming Excel using VBA so I felt that learning GAS to manipulate Google Sheets would offer a familiar environment. I reasoned that there are many experienced Excel VBA programmers around who might feel similarly so that is why I wrote this book. There are of course now many people who use Google products who are familiar with JavaScript but who may not know much about spreadsheets. This book might also be of interest to this group of users. This book assumes programming knowledge in some programming language though not necessarily JavaScript.

1.7 Book Code Available On GitHub

The emphasis on this book is on practical code examples. Some of the code examples are short and only practical in the sense that they exemplify a GAS feature. GAS is a “moving target” in that new features are added and deprecated frequently thereby making it difficult to keep all code up-to-date. At the time of writing, all the examples worked as expected but please email me if something is broken or if you get a warning of something having been deprecated. To allow readers to follow along, I have tried to document the code extensively using JSDoc and in-line code comments. To run the examples, you can copy and paste from the book to the GAS Script Editor. This will work but I recommend getting the code from GitHub. All the code examples in this book are available on a Github repository created specifically for this updated version of the book. The user name is Rotifer and the repository name is GoogleSpreadsheetProgramming_2015. The full URL is here. You can use the Git command line tool to check out the repository to your local machine or simply copy the examples directly from the GitHub repository.

1.8 My Blog On Google Spreadsheet Programming

I maintain a blog that pre-dates this book. The blog began in late 2010 so some of the early examples use deprecated or unsupported features. That said, the blog inspired this book and is actively maintained so it can be viewed as a complementary resource to this book. It is worth checking for new entries from time to time because I use it to explore and discuss new spreadsheet and GAS features and I cover some more advanced JavaScript material there.

1.8 Guideline On Using This Book

I learn best from examples so this book is heavily example-driven. I first like to see something working and then examine it and learn from it. In order to derive maximum benefit from this book, it is important to execute the code from your own spreadsheets. Read the book descriptions and code documentation carefully and make sure that you understand both the objective of the code as well as how it works. Chapter 3 goes in to some depth on core JavaScript concepts such as functions, arrays and objects but if you are relatively inexperienced in JavaScript, then some background reading will help enormously. Remember, GAS is JavaScript so the better you understand JavaScript, the better you will be at writing and understanding GAS. Do not be afraid to experiment with code, change or re-write my examples at will! If you find better ways of doing things, please let me know!

1.9 2015 Update Notes

The first version of this book was released in November 2013 and the feedback I have received on it has been largely positive. However, I realise that some of the material is now out-of-date. For example, UiApp and DocsList have both been deprecated since that version of the book was released. I am also aware that the book could be improved by better explanations, better examples and generally better writing. I hope this version delivers a better product to its readers.

As I stated earlier, Google Sheets and GAS are “moving targets” that are subject to constant change. I intend to release a new version of this book yearly from now on so that the material remains current and the overall quality of the book improves. I will continue to blog about new features and topics that I have not covered so far and, if these look like a good fit and attract interest from readers, I will incorporate such subjects into later version of the book.

Something I find annoying and expensive is when I buy a technical book only to learn a few months later that a new edition has been released. I have one shell programming book from 1990 that I still use but shell programming is one of the few stable technologies that I use (SQL is another, yes, features are added but the core is quite stable). Most technical books that I have bought in the past become obsolete, at least in part, in a year or two. My idea with this book is to make sure those who buy it once get all the updates for free and I plan to keep updating it indefinitely. Leanpub make this possible, so a big thanks to them and thanks also to all of you who bought the first version of this book!

Time to write some GAS!