Chapter 1. Enhancing Spreadsheets

Spreadsheets are probably the favorite entry point for any future Google Apps Script user; their primary use is to process data one way or another.

A Google spreadsheet has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations.

The major differences compared to other spreadsheets are that it opens in a generic browser without needing any specific program installation or license and all the data processing is handled outside your computer on Google's servers.

It is also shareable among any number of users in real time as long as they have an Internet connection and it doesn't need to be saved because every single-cell edit is recorded seamlessly and every change is logged in a so-called revision history (accessible from the File menu) that allows you to recover any previous version while being able to see who made the change and when.

When Google introduced this spreadsheet service back in 2006 as an experimental feature, I found it attractive because of the shareable access and also because cloud-based applications were new and seemed a bit magical. Now that almost everything can be hosted in the cloud, the magic has gone; nevertheless, it's still very comfortable to use.

Spreadsheet functions versus Google Apps Script - how to choose?

As mentioned before, spreadsheets are capable of performing all sorts of operations, from math to string manipulation and resolving complex financial formulas.

To be convinced of its interest, you just have to take a look at the drive help page or simply type = in a spreadsheet followed by any letter (for example, S) and you get a list of all the spreadsheet functions whose names start with the letter S (https://support.google.com/drive/table/25273?hl=en) as shown in the following screenshot:

These functions are built into the spreadsheet; they can be put together and combined in many ways. They also execute pretty fast and most of them are compatible with other spreadsheet formats such as Microsoft Excel or Apache OpenOffice such that they will continue working if ever you download the document for any reason. That's undoubtedly an advantage and most of the functions have names that are explicit enough to understand easily (well, that becomes less true if you read what experts are able to do with them but that is definitely not the subject of this chapter).

An example

Let us take a simple example of a function that we can build using a combination of spreadsheet functions and then translate in Google Apps Script; I want to get the number of hours that have passed since my birthday.

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

The spreadsheet-formula approach

A quick look at the spreadsheet function list tells us that we have a function called TODAY() that returns today's date and another one called DATE(date as a string) that returns a date in its string representation.

If we decide to subtract these two values TODAY()-DATE(1958,2,19), we obtain the number of days that have passed since my birth date until now. Let's multiply it by 24 and get the result in hours.

I was born at 2 a.m. and when I try the two functions, I get 6 p.m., so we still have to improve the formula.

Looking at the results when you run a search for the letter H, I see a function HOUR(); the documentation tells me that HOUR() returns the hour component of a specific time in the numeric format. So I try HOUR(TODAY), but this doesn't work because TODAY() has no time information; it has only the information of today's date. What about NOW()?

If I quickly try the =NOW() function in my spreadsheet; the autocomplete feature tells me that it returns the date and time information of this moment, which is exactly what I am looking for. HOUR(NOW()) returns 18-that's correct. Since I was born at 2 a.m., let's just write HOUR(NOW())-2 to get the difference.

Now if we put it all together, we get =24*(TODAY()-DATE(1958,2,19))+HOUR(NOW())-2, which is the formula that will return the number of hours since I was born. Great!

As you can see from the previous basic example, this is all very logical and can almost be written intuitively, following a normal human/mathematical sequence.

Now I can easily use this formula in other cells using references instead of numbers; it will adapt itself automatically and I can even use it in a table as shown in the following screenshot; dates are in column E and hours in column F and every row has a formula in column G that adapts itself automatically to match the row numbers:

In the previous screenshot, you can see the formula that applies to all my family members; I just wrote it in G1 and dragged the formula down so that it is applied automatically to all the cells it was dragged over while updating its reference cells.

The previous simple example illustrates the following two elements that I wanted to focus on:

• Spreadsheet formulas are fast and relatively easy to expand when data is properly organized and we get used to their logic
• Spreadsheet formulas are rigid as they use fixed references and apply only to data that is on the document (or at least in some document/sheet, even if it is hidden)

The Google Apps Script approach - a few comments

Before we show the Google Apps Script as an equivalent, let me remind you of a couple of things.

As I mentioned before, Google spreadsheets were introduced in 2006 and had to conform to the de facto spreadsheet standard that was established by Microsoft Excel, the latter being designed in 1986 for all the spreadsheet functions but not necessarily for the included macro development.

Twenty years is a very long time in the history of computers. In 1986, few people knew about the Internet and the most popular programming languages were Fortran, Cobol, or Basic, almost all of which have now disappeared, and C was only beginning to allure a few experts.

Microsoft developed Visual Basic as the macro language behind spreadsheets. Its structure was similar to Basic, using labels, line numbers, and go tos-a structure that looks quite old fashioned to today's programmers and offers a limited development perspective.

JavaScript will have to wait another 10 years before invading our computers.

In a way, one could say it was easier for Google engineers to create the Google spreadsheet with a smarter and more powerful macro language borrowed from one of the most popular languages on the Internet behind HTML.

This little introduction is just to mention that JavaScript is probably one of the most easy-to-learn languages as far as documentation availability is concerned.

There are literally thousands of websites and blogs that offer thousands of examples, tutorials, and references about JavaScript, a lot of them being directly useable in Google Apps Script.

The Google Apps Script approach - the code

The following code illustrates the Google Apps Script approach to finding the number of hours that have passed since my birthday:

That's about it!

I'm joking of course, but not that much. Let's look at it more closely.

If you're reading these lines on a computer, just open your favorite browser on your favorite search engine page and type JavaScript date because what we are trying here concerns dates and the first line of code starts with new Date().

The Internet page should now be filled with links to hundreds of pages that will explain:

Creates a JavaScript Date instance that represents a single moment in time. Date objects are based on a time value that is the number of milliseconds since 1 January, 1970 UTC.

Constructor

new Date();

new Date(value);

new Date(dateString);

new Date(year, month [, day, hour, minute, second, millisecond]);

(The preceding example is taken from: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date.)

Having read this preceding short definition, you know everything you need to know about dates in JavaScript: it is an object (this is an important concept, so we'll come back to this later) and built using the function new Date(). The next three lines tell us how to give a specific value to this Date object, with no argument resulting in today's date and time.

The...