Google Apps Script for Beginners

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.

The script editor is a dedicated online development environment that allows for text editing, automatic code formatting, and basic error detection, it can be opened from the tools menu of a spreadsheet, the tools menu of a text document, or directly from the Google Drive home page at https://drive.google.com | Create | Connect more apps | Script. This last document type is optional in the drive interface, but once you have it activated, it will remain available. I strongly recommend that you do so if you are interested in Google Apps Script.

For now, we will use the spreadsheet interface, open the script editor from the Tools menu, and write the code snippet we just prepared.

The script editor is probably the best thing that has happened to me in my long journey trying to learn programming in Google Apps Script because it has a very useful feature that some people still seem to ignore: autocompletion.

The advantages of autocompletion

Autocomplete or Content assist (which is presently the official name of this tool) can be activated from the Edit menu in the script editor; the keyboard shortcut to activate it is Ctrl + Space bar.

This writing assistance will help you avoid a lot of errors, from simple typos to type mismatch or syntax errors because it simply suggests all the possible methods available while you are typing your code.

For example, imagine we want to get the value from the cell that is selected in the sheet with which we are working (later, we'll see in detail what this really means). The code for it is as follows:

Let's admit that this is not so simple to type in when we are new to this; using the uppercase in the right places and the importance of sequence order are not really easy to manage.

Now press s, just s and nothing else, and then hold Ctrl and press Space bar. The following is a screenshot of what you will see:

These are all the class objects available in Google Apps Script starting with letter S; you can choose the one you want with your mouse or the keyboard arrows, or even by simply adding more letters.

We want SpreadsheetApp, so we can select it right from the list or just type p and the following screenshot shows what we get:

Isn't it magic? No, there's no magic there, but it's really helpful and so easy!

This feature sometimes shows new services and methods even before they are officially announced! (Believe me, it's happened a few times that some of us discovered undocumented features just by accidentally using a keystroke.)

Anyway, even if it probably won't happen anymore since Google rewrote all its documentation recently and makes real efforts to keep it up-to-date, it is still really helpful for everybody, from an absolute beginner to a Google Apps Script expert.

As mentioned earlier, we have to use a dot to chain methods in JavaScript, so let's type a dot followed by pressing Ctrl + Space bar again, as shown in the following screenshot:

Now, the list in the previous screenshot is too long to show entirely in the small UI window, so let's narrow it down to what we are looking for, by typing the letter g as shown in the following screenshot:

The choice is clear. Actually, we could use any of these methods to finally get the value in the selected cell, so let's go to the end of the process by just repeating the same workflow, as shown in the following screenshot:

I'm very happy to tell you that you just typed a beautiful line of code that is completely useless! But, at least it didn't cost you much effort.

That code is indeed not very interesting; it doesn't assign a value to a variable nor does it do anything else. It is just an example of correct syntax; correct but not useable.

The following screenshot shows a code that we could use:

In the single line of code shown in the previous screenshot, we create a variable (cellValue) and we assign it a value, which is taken from the content of the active cell. That's a good starting point.

In this example, we've assumed that the meanings of all these methods used have been clearly understood and that the choice has nothing to do with chance or hazard; that was indeed the case, but of course it won't be so clear if you have just started to work with these tools, so it might be interesting to have a quick look at it now.

Reading the documentation

The documentation on Google Apps Script is available on the Google Developers Products page as part of the Google Apps platform (https://developers.google.com/apps-script/) and shows a mix of guides, tutorials, and reference documentation along with a few links to helpful resources, including the Stack Overflow forum (http://stackoverflow.com/questions/tagged/google-apps-script) that I've already mentioned and where I've been participating since May 2012, which is when Google closed their Google Groups Help forum where I took my first steps in this subject. The Stack Overflow forum uses tags to sort the numerous posts; you can use this tagged link to restrict your search results to the relevant language (see Chapter 8, How to Expand Your Knowledge).

The very first thing we find on this web page is a search window, as illustrated in the preceding screenshot, in which we can type any keyword that we want to run a search for.

The search engine is Google's primary business, so we can be quite sure that it will return the results in a fast and reliable manner.

The results are by default restricted to Google Apps Script and suggest references to every documentation item in various categories from which you can choose. It shows a subset of its content as well.

When we choose one of the search results, we arrive at the reference documentation page where we find an exhaustive list of all the available methods in the relevant category.

Most of the important methods include code examples that show you how to use them and what data they return. In many cases, the example code can be a good starting code for a beginner.

You can also look for what you need manually, of course, using the sidebar index where every Google Apps Script class opens a folder tree containing every possible method you can use.

Another option is to have a look at the quickstart and tutorials links in the Overview section at https://developers.google.com/apps-script/overview. I cannot recommend enough that you read this at least once, particularly if you are not familiar with program writing but also in every other case because it will show you all the possible ways to use Google Apps Script with the help of various simple examples and video presentations.

I am not going to reproduce the documentation's content in this book as that would be completely useless, so let us continue with our first step in JavaScript programming.

Earlier in this chapter, we wrote our first Google Apps Script function as follows:

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;
}

But, we haven't really executed it yet, so let us try it now.

On top of the script editor, we have a Run menu and a play icon that will start executing the selected function, as shown in the following screenshot:

Clicking on the play icon or navigating to Run | myAgeInHours triggers the script execution; but if you try it, you will see that nothing happens. That is normal, as the function we wrote returns a result, but only in the context in which it is intended to work.

In other words, if another function would have called this function, it would have got the result as a number representing the duration in hours between now and my birth date, or if we had used the same function as a spreadsheet function, we would have seen the result in the spreadsheet cell.

Let us try this last method, better known as custom spreadsheet functions, in the next section.

We have seen how to use built-in spreadsheet functions using an equals sign before the function name; custom functions work exactly the same way.

If we type in cell A1 the following: =myAgeInHours(), we will almost instantaneously get the value returned by the function, which will be something like 489,584, and it will change every hour.

This is all very simple and quite attractive at first glance, but we'll see in the next chapter that it is not always the best way to use Google Apps Script as there are a few annoying behaviors in this workflow.

If we want to preview the result without using the spreadsheet interface, the best way to do it is using the built-in Logger or the JavaScript keyword throw.

The Logger result is available from the View Logs menu item and shows every occurrence of every Logger.log call that we insert in the script. In our simple example, we could write it as follows:

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

The preceding code will return the following result in the Logger view, which is interesting but not very easy to read because it shows only numbers and we need to concentrate on the code to determine what values are exactly shown:

We can easily make it more user friendly by simply adding a little information to our code. This can be achieved in two different ways: either by literally composing your result with strings and variables or using the format parameter in the Logger.log method and using the %s placeholder for variables (https://developers.google.com/apps-script/reference/base/logger#log(String,Object...)).

I'll use both methods in the following example:

function myAgeInHours(){
  var myBirthDate = new Date('1958/02/19 02:00:00').getTime();
  Logger.log("myBirthDate = "+myBirthDate);
  myBirthDate = parseInt(myBirthDate/3600000, 10);
  Logger.log("myBirthDate in hours (parseInt(myBirthDate/3600000, 10)) = "+myBirthDate);
  var today = parseInt(new Date().getTime()/3600000, 10);
  Logger.log("today in hours = %s",today);
  Logger.log("today-myBirthDate = %s",today-myBirthDate);
  return today-myBirthDate;
}

This previous code will return the following result:

That is far more readable, isn't it?

Another way to get a value from a script is using the throw command that literally throws a message over your browser page just like any script would do, but I personally don't like it much because it shows up the same way as an error does; it makes me feel like something bad just happened.

Finally, since we tried this code in a spreadsheet, we have two more options to show the result:

The following screenshot shows the output for the preceding example:

This last possibility is specific in that it pauses the execution of the script and waits for the user to execute some action. In the simplest case, it is just a click on the Ok button but we can also ask for some value using other methods from the Browser class, for example, we could use the following code:

var name = Browser.inputBox('Enter your name', Browser.Buttons.OK_CANCEL);

The preceding line of code will ask the user to enter a name that will be assigned to the variable name as illustrated in the following screenshot:

Now that we are hands on with all the tools available in the script editor and know how to set and show variable names, let us try some practical examples that can make our life easier.

When looking at the Spreadsheet Service documentation (https://developers.google.com/apps-script/reference/spreadsheet/) and particularly the Sheet class (https://developers.google.com/apps-script/reference/spreadsheet/sheet) and the Range class (https://developers.google.com/apps-script/reference/spreadsheet/range), we can see that there are a lot of available methods that allow us to modify the sheet format; in other words, we can create functions that will set up a custom page layout with a single click.

This could be very helpful when creating shared documents with people who have to fill in some data and have a bad habit of making a mess of the page layout.

For example, they might have to change the column width and sorting, background color, or even the font size and family.

It can become a real pain to check for that all the time and reset it the way you want by proceeding step-by-step in the spreadsheet user interface (this has been a live experience for me many times as I have a few, very undisciplined collaborators).

Let us wipe out the code we used so far and try the following new one:

function resetPageLayout() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Sheet1');
  ss.toast('Now processing your sheet','Wait a few seconds',5);
  var header1 = sh.getRange('A1:G1').mergeAcross().setBackground('silver').setValue('Party menu suggestion');
  var header2 = sh.getRange(2,1,1,7).setBackground('#aaaaff').setValues([['First Name','Last Name','Drink','Softs','Appetizers','Meal','Dessert']]);
  sh.getRange(1,1,2,7).setBorder(true,true,true,true,true,true).setHorizontalAlignment('center').setVerticalAlignment('middle').setFontWeight('bold').setFontSize(14);
  var columnWidth = [150,150,180,180,180,300,200];
  for(var n=0; n < columnWidth.length ; n++){
    sh.setColumnWidth(n+1,columnWidth[n]);
  }
  sh.insertColumnAfter(7).deleteColumns(8,sh.getMaxColumns()-7);
  sh.insertRows(sh.getLastRow()+1,20);
  sh.deleteRows(sh.getLastRow()+1, sh.getMaxRows()-sh.getLastRow()-10);
  sh.getRange(3,1,sh.getMaxRows()-2,sh.getLastColumn()).setBorder(false, true, false, true, true, false);// top, left, bottom, right, vertical, horizontal
  for(var n=sh.getLastRow() ; n > 3 ; n--){
    Logger.log(n+'  '+sh.getRange(n,1,1,7).getValues())
    if(sh.getRange(n,1,1,7).getValues().toString().replace(/,/g,'')==''){
      sh.deleteRow(n);
      Logger.log('row '+n+' deleted');
    }
  }
  sh.setFrozenRows(2);
  SpreadsheetApp.flush();
  Browser.msgBox('Now your sheet should be clean again !');
}

Be sure you have nothing in your sheet that you would like to save as it might be deleted when this function will execute.

Now you can run the code from the script editor and you will get the following pop up requesting your authorization for this script to run:

When clicking on Continue, you will see a second pop up with the actual authorization process along with the details of every service used by the script. In this particular case, the only service the script is using is Spreadsheet Service as we can see in the following screenshot:

Once you accept the authorization request, the script will continue to execute and you can go back to your spreadsheet to see it working live!

The following screenshot shows the formatted spreadsheet:

You will probably notice in the preceding screenshot that the page suggests to add 1000 rows. That is because I am using the new spreadsheet in this test. If you are still using the current standard version the number will be 20. Everything is growing with time!

I have to admit that it takes some time to write such a script and that its use case is quite limited, but I chose this example to show a sample of the very broad panel of possibilities Google Apps Script offers and to introduce the next section in this chapter.

Menus and custom buttons in spreadsheets

Running a script from the editor might quickly become annoying as it forces us to switch between two tabs or browser windows.

That's a good opportunity to look at the menu customization and the embedded buttons.

Let's start with creating a menu by executing the following code:

function createMenu(){
  var menuEntries = [ {name: "resetPageLayout", functionName:"resetPageLayout"}];
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  sh.addMenu("Format utilities",menuEntries);
}

Note

If you are using the new spreadsheet version and as described in the release note published on January 21, 2014 (https://developers.google.com/apps-script/releases/#january_2014), a new method is available to create custom menus (the old method is still usable).

The syntax is quite different, the following code shows the new version:

function createMenu_new() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu ('Format utilities');
  menu.addItem ('resetPageLayout','resetPageLayout');
  menu.addToUi();
}

These few lines will create a custom menu in your spreadsheet as shown in the following screenshot:

When you click on the available item, the function is called; you don't even need to open the script editor anymore.

The problem with the preceding code is that the menu will disappear when you close the document.

When you open it again, you will have to reopen the editor and run the createMenu function again. To get rid of that annoying task, we will use a special function named onOpen.

The function onOpen will execute automatically when anyone opens the spreadsheet; we'll see later that there are other such special functions that execute automatically when some event occurs.

The preceding code needs very few modifications to run automatically; just change the function name from createMenu()to onOpen().

After refreshing your browser window, you will see the new menu appear right between Tools and Help. A new bit of magic!

The other option is to include a custom button in your spreadsheet to trigger a script function you created.

From the spreadsheet menu, go to Insert Drawing and use the toolset to draw a button the way you like; save and close the drawing editor and your new button will be waiting for you in the spreadsheet as shown in the following screenshot:

If you click on the New button, you'll see a small menu that will allow you to assign a script function to that button as shown in the following screenshot:

Type the name of the function you want to use as shown in the following screenshot:

You're done! Place it where you want and enjoy.

You can use the same procedure to insert a figure that you will be able to scale to your needs and assign a function to.

Note

This feature (the script assignment of both drawings and figures) is not yet available in the new version of spreadsheets.

This is a pleasant feature that makes spreadsheet script comfortable to use but there are a few limitations that are listed as follows:

• The position of figures and drawings is fixed on the sheet and not on the screen; that is, when you scroll down a long list of data, the figure might disappear from the view area

• Figures and drawings cannot be inserted in the frozen rows or frozen columns area

• They can be changed, moved, or deleted by any user who has editing rights to the spreadsheet so it's not necessarily a good idea to use them in shared documents

The script from this example is probably a bad example of button or custom menu use, as both will be available to every user who can edit the document; your friends might be surprised by its effects but this was, of course, just an example.

We'll see in the next part that this example script is not ideally written as far as speed and efficiency are concerned.

You probably noticed when playing with the preceding script example that it takes some time to execute. You must remember how these spreadsheets and scripts actually work.

The sheet we see in our browser does not exist as a file in our computer; all its data is stored on a Google server somewhere and it is rendered as HTML content that can be understood by any web browser.

Each time we make a change manually or using a script, the information is sent to the server, interpreted, processed, and sent back to us using our Internet connection.

This can be a time-consuming journey for all those bytes; some parameters are predictable (our average connection speed, for example) and some are not, such as the distance from our computer to the Google server and its load at that moment. Remember that you are not alone in using this drive app! There can be thousands of users sending requests simultaneously.

To make things better, or at least the best we can achieve with the parameters we have control over, we should take care to use as few separate requests as possible to any distant service.

For example, if we want to fill 100 cells in a sheet column, it would be a bad idea to fill each cell one by one. We should try to use the batch method to set the values in all 100 cells in one single step.

Example

The following are two scripts that do what we suggested in the preceding section: filling a column of 100 cells with some text. Try both versions successively and compare the execution times of both versions, which will be shown in a browser pop up. The following code snippet fills a column of 100 cells with some text:

function fill100Cells(){
  var start = new Date().getTime();
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  for(var n=1 ; n<=100 ;n++){
    sh.getRange(n,1).setValue('This cell is filled');
  }
  Browser.msgBox('Execution time : '+(new Date().getTime()-start)+' milliseconds');
}

function fill100CellsatOnce(){
  var start = new Date().getTime();
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sh.getRange(1,1,100,1).setValue('This cell is filled');
  Browser.msgBox('Execution time : '+(new Date().getTime()-start)+' milliseconds');
}

The following screenshot displays the execution time for filling a column of 100 cells with some text:

I'll let you guess which one is the most efficient.

This simple example illustrates the very few annoying aspects of cloud computing and Google Drive applications, in particular, the execution speed of code.

Knowing that, we should always be very careful when writing scripts, do it as efficiently as possible, and using as few service calls as possible. These recommendations and a few others that we shall examine later on are clearly explained in Google Drive's documentation. I suggest you read it twice rather than once and keep it in mind when writing your future applications (https://developers.google.com/apps-script/best_practices).

There have been a couple of very interesting posts on that subject on the Stack Overflow Help forum and I suggest that you read this as well, as it goes quite deeper in the speed testing and optimization process (among others: http://stackoverflow.com/questions/15145918/what-is-faster-scriptdb-or-spreadsheetapp/15149959#15149959).

An event is when something happens. That's a rather basic definition but it gives a pretty exact idea of what it means in the context of a Google spreadsheet document.

If a user modifies a cell, deletes a row or column, or even just opens a spreadsheet, all of these events can be caught by a script and we, as script writers, can decide to use these triggers to execute some task.

The online documentation (https://developers.google.com/apps-script/understanding_events) provides an exhaustive list of all the event sources and how to get information from them; I won't reproduce all the descriptions here but I'd like to present some interesting, useful perspectives.

Application examples are numerous: we could interact with range values, colors, font sizes or weights, and even use services that are not actually related to spreadsheets, such as modifying another document, a website's content, or sending an e-mail.

We'll see in Chapter 4, Embedding Scripts in Text Documents that the different Google services can interact very easily and manage some complex workflows automatically, but for now let us concentrate on the basic spreadsheet-related triggers and events (https://developers.google.com/apps-script/understanding_triggers#ActionTriggers).

There are basically two categories of triggers: simple triggers and installable triggers.

The major differences between these triggers are about authority and permissions, so the real question is which trigger is doing what?

As presented in Google's documentation, on one side, we have the user at the keyboard and on the other side, the script's author who installed the trigger; that is one aspect of the question and the other is the type of trigger: simple or installable.

Simple triggers run as the user at the keyboard, whether they are anonymous or logged in as an authorized editor, and therefore, simple triggers can only perform actions that don't require specific authorization or to connect to any service that needs authentication.

Following the same logic and knowing that triggers respond to events without informing the user, that is, silently, any installable trigger created by the script author will always run as if the author was running it, that is, under the authority of the author who installed the trigger.

That logic is very simple and easy to understand, but it's important to remember that when setting up a script that uses triggers.

We have seen before that some scripts require authorization before they actually execute; this is the case when a script is able to modify something or perform some action under the user's authority. For example, if you use a script to send an e-mail from your account, you would expect the script to warn you before doing it, right?

So it does; every reference or call to a nonanonymous service in a script file is analyzed when we try to run any function in that script or try to save a trigger for that script (by navigating to Resources | Current project's trigger | Add a new one in the menu bar) and ask for explicit authorization before its execution.

If you are not the owner of the script and you try to execute such a function, you will also be asked for explicit authorization, receive an e-mail that confirms that you have granted this script access to your data, and get a link that you can use to revoke the authorization.

This might seem like a complex procedure, but it's really a major security and privacy aspect that Google took care of, to handle in a secure manner.

While we are taking care of data privacy, let's have a quick peek at data protection using Google Apps Script.

Google has introduced this ability quite recently and it opens a few interesting perspectives.

The following is an example of a script that protects a sheet after a user has added a value in a particular cell:

function myFunction(e) {
  var sheetIndex = e.source.getSheets().length;// to know how many sheets we already have
  var sourceValues = e.source.getActiveSheet().getDataRange().getValues();//get all the data from this sheet
  var cell = e.source.getActiveRange().getA1Notation();//get A1 notation for comfort of use
  Logger.log('SheetName:'+e.source.getActiveSheet().getSheetName()+'  user:'+Session.getActiveUser());
  if(cell=='A1' && e.source.getSheetName()=='Sheet1'){// execute only if cell A1 and Sheet1, else do nothing
    var copy = e.source.insertSheet('SheetCopy_'+sheetIndex,sheetIndex);//create a copy at the last index
    copy.getRange(1,1,sourceValues.length,sourceValues[0].length).setValues(sourceValues);//clone sheet1 values only, no format
    var permissions = copy.getSheetProtection();
    permissions.removeUser(Session.getActiveUser());//who is editing ? remove him from editors (does not work for owner of course)
    permissions.setProtected(true);
    copy.setSheetProtection(permissions);//protect the copy, the original editor of the sheet can't change it anymore
    e.source.getSheetByName('Sheet1').activate();//reset the browser to Sheet 1, not on the copy
  } 
}

The previous code is pretty funny as it will make a copy of the active sheet on certain conditions and prevent even the user from modify it. One could imagine following such a process as signing a document and preventing its modification thereafter.

It shows that any workflow can be automated quite easily.

Also note that this code does not work with new spreadsheets for now (as of January 2014, the new version of spreadsheet (which is an optional update) doesn't support the onEdit trigger, so this function can't be implemented) and must be tested on a shared document; the actual user must NOT be the owner of the document, but an editor (perhaps with shared edit permissions).

It is indeed not possible to restrict the sheet access for the sheet owner themselves (and that's a good thing!).

Spreadsheets can be published on a website (https://support.google.com/drive/answer/37579?hl=en) but cannot be edited from within a web page:

For this reason, there is no way to execute script functions from a web-published spreadsheet. There is very little chance that this would ever happen because of the obvious security issue it would represent.

As of this writing (December 2013), Google Apps Script has no possible way to print a document. Printing all or part of a spreadsheet is a common activity, one that we might want to automate using a script. Unfortunately, security considerations limit Google Apps Script access to local resources such as printers.

As a workaround for this limitation, our script can export spreadsheets to PDF and send them by e-mail or store them in Google Drive.

A quick example that won't need too much explanation is as follows:

function sendThisSsAsPdf(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = ss.getId();// get the file unique id to use with driveApp
  var pdf = DriveApp.getFileById(ssID).getAs('application/pdf');//get the file content in PDF format
  var saveCopy = DriveApp.createFile(pdf);//create a copy in your drive
  MailApp.sendEmail(Session.getEffectiveUser().getEmail(),'This is a copy of your spreadsheet','This is a pdf copyof your spreadsheet as an attachment to this message\n'+
                    'This mail was sent to you on'+Utilities.formatDate(new Date(), Session.getTimeZone(),"MMM-dd-yyyy @ HH:mm"),{attachments : [pdf]});//send the email with a simple message
}

The preceding script shows how to simply and rapidly create a PDF version of the current spreadsheet and send it to the user at the keyboard; this function can be called from a menu of course, but can also be sent at a fixed time every single day if you need an archive copy for any reason.

In the next chapter, we'll see how to create and use Google forms as they are probably the second most-frequent entry point to Google Apps Script for most new users.

This long chapter about Google Apps Script in spreadsheets has shown that many aspects of a spreadsheet document could be modified using Google Apps Script; not only the layout and format, but the sharing and publishing features as well. What we'll learn in the coming chapters is how we can use it to exchange data with other Google services, either as a recipient or as a data source. The first example of this interaction is probably the most common: Google forms.