Book Image

Google Apps Script for Beginners

By : Serge Gabet
Book Image

Google Apps Script for Beginners

By: Serge Gabet

Overview of this book

Table of Contents (16 chapters)
Google Apps Script for Beginners
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
7
Using User Interfaces in Spreadsheets and Documents
Index

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:

function myAgeInHours(){
  var myBirthDate = new Date('1958/02/19 02:00:00').getTime();
  myBirthDate = parseInt(myBirthDate/3600000, 10);
  var today = parseInt(new Date().getTime()/3600000, 10);
  return today-myBirthDate;
}

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 preceding definition also tells you that the value after a date is the number of milliseconds that have passed since January 1, 1970; negative values are accepted for dates prior to this reference.

Even if you hear the word JavaScript for the first time in your whole life, you cannot possibly be scared or discouraged by this, even if, as you can imagine, those will be big numbers! And indeed they are.

January 1, 2014 at 00:00 UTC is 1,388,534,400,000 milliseconds, which is a big number indeed, but that's what computers are made for—handling numbers—aren't they?

Most times, we can simply ignore this value and use its date representation instead, which reads more naturally when speaking of dates and time; but it's good to know it because we will use it to calculate the duration between two dates as we've done in our preceding example.

One of the problems that our teachers taught us to solve when we were kids was how to calculate the hours and minutes between two events knowing that there are only 60 minutes in an hour and 24 hours in a day—our first math nightmare!

Now, I have good news for you!

You can count in decimal again, JavaScript's new Date() method will convert dates and time formatted in decimals to the proper date and time format.

The new Date(1388534400000) method will return January 1, 2014 at 00:00 UTC; if you ever forget the reference date January 1, 1970, just type new Date(0) and you'll get Thu Jan 01 01:00:00 GMT+01:00 1970 (we're even told it was a Thursday).

Let us return to our function; the very first line of this code is as follows:

function myAgeInHours(){

function indicates where the function starts; it's a keyword in JavaScript.

myAgeInHours is the name I specified for the function in this example; the rules of the language require that the name be a single word, but we'd like it to be meaningful, so we wrote this in mixed case to focus your attention on the individual words. This format is called CamelCase and is not mandatory; it's just a convention that makes code more readable.

() holds an optional parameter that the function will use. There can be more than one parameter (separated by commas) and they may be of any type: numbers, strings, arrays, objects, or whatever else is necessary for the function.

{ indicates the beginning of the function code; there will be a closing curly bracket at the end of our function code to indicate the end of the code. From here, we can start telling what we want the function to do, which variable to use, and what result to return.

var myBirthDate = new Date('1958/02/19 02:00:00').getTime();

This first line defines a variable (var name = ) and gives it a name.

Note that variables in JavaScript are defined within a function and exist only within that function; in other words, you cannot use a variable value outside the scope of the function in which you defined it.

If you want a variable to be global (useable by all the functions in your script), you have to define it outside all the functions within which you want to use it and you cannot change its value from a function, that is, they will be constants!

Everything that comes after the equality sign indicates to the program what this variable is, that is, its type and value. In this case, it is a date object with the value February 19,1958 at 2 a.m. exactly.

Right after the new Date() function, we have a chained instruction using the getTime() method; a quick look at the page we opened in our browser will tell us that getTime() returns the value of the date object in milliseconds, which is its native value.

You may have noticed that we used a dot ( ) to chain our two instructions; that is how we can modify objects' properties (we'll learn more about this later as well).

At the end of the line is a semicolon (;) that indicates we are done with the line and that what is coming next is a new line of code. In Google Apps Script, the absence of this semicolon does not cause an error (it's not mandatory), but it's a good habit to use it systematically because just about every other development environment requires it and it really does improve readability. It also allows you to add a second instruction after it on the same line or add a comment that won't be interpreted if you use a double slash (//) before it.

var test = 0;// this is a variable called test and its value is 0

Now, we know the number of milliseconds that have passed between my birth date and January 1, 1970 at 00:00 hours and that it's a big number not really scaled to accommodate our human perception. Let us convert that into hours by dividing it by 3,600,000 (3600 seconds of 1000 milliseconds) to get only the the result in integer form.

Getting the integer from a decimal number is easy in JavaScript using one of the many methods available, such as parseInt() or the Math.floor(x) method. Some of the Internet reference sites about JavaScript show complete lists of all the available methods in each category with a few examples and basic explanations to help you choose from among them.

The following one comes from Mozilla Developer Network (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/parseInt):

Summary

The parseInt() function parses a string argument and returns an integer of the specified radix or base.

Syntax

parseInt(string, radix);

Parameters

string

The value to parse. If string is not a string, then it is converted to one. Leading whitespace in the string is ignored.

radix

An integer that represents the radix of the above mentioned string. Always specify this parameter to eliminate reader confusion and to guarantee predictable behavior. Different implementations produce different results when a radix is not specified.

It parses a string as an integer in decimal form when using a radix of 10; that's what we needed.

The next lines of code are almost the same:

  var today = parseInt(new Date().getTime()/3600000, 10);
  return today-myBirthDate;
}

In the preceding code, today is a new variable that represents the number of hours since the birth date until now and return tells the function what value to return; in this case, it's the difference between now and my birth date, which will naturally be a positive integer.

The closing curly bracket terminates the function body as mentioned earlier.

This small code can now be saved in the script editor and doing so will force you to specify a name for this first script; just let your imagination flow freely and in the next section we will examine how to actually use it.