Google Apps Script, 2nd Edition

Chapter 4. Adding Actions

In Chapter 3, you began creating an application to collect email addresses from visitors who would like you to contact them. The only problem is that clicking your “Subscribe” button doesn’t do anything yet. In this chapter, we will add actions to that button to store the visitors’ email addresses, thank them if they entered an email address, and send them an email verification.

Handling User Actions

In order to work, a button needs an event handler. However, buttons are not the only elements that can have handlers, and click is not the only handler type. For example, a text box may have a handler that responds to the user pressing the Enter key, or a listbox sometimes needs to fill a second list for situations like choosing a city after the state has been selected. In such a case, we might use an onChange handler. In more advanced UIs, mouse handlers like over and off can create rich user interaction by displaying information before a selection is made. It’s also important to note that an element can have more than one handler. When you have a process that may take some time, like receiving data from a web service or uploading files from the hard drive, it’s a good idea to show a progress indicator to the user after he has clicked, and an additional handler may take care of that detail.

In this chapter, we will keep things simple and only work with the submit handler to provide action for our simple form.

Anatomy of a Handler

Google Apps Script allows us to use jQuery, which is the preferred technique in HTML programing to date. I highly recommend learning as much jQuery as possible. In the long run, it will save you countless hours of trying to figure out how to write something in pure JavaScript—because that thing has already been included in jQuery and can be accomplished by simply pasting in a single command.

Let’s start where we left off in Chapter 3 by opening our existing Contact Me code. The first thing we need to do is import the jQuery library into our index.html file. You can import the jQuery library in a number of different ways, such as by saving a copy on your server or in Google Drive. There are quite a few publicly available custom versions, but because Google Apps Script uses Caja and can be very picky about any imported code, I recommend using a particular version provided by Google.

To import this version of jQuery, add the following line of code to the end of your index.html file:

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>

Now that we have installed jQuery, let’s add an event to the “submit” button. We want the page to always display as quickly as possible, and we want to avoid any issues with a browser that might try to load things in a strange order. When we want to add an event to any object on the page, that object must be there to attach to, or the process will fail. The safest way to accomplish this is to use the jQuery ready method, which will watch for the page to be fully loaded before it starts executing JavaScript.

The ready method looks like this:

$( document ).ready(function() {
    console.log( "ready!" );
});

To attach our event handler to the button, we will use the submit method because it will detect a mouse click or Enter keypress. Again, this is one of those examples where jQuery saves us a line of code to take care of a simple task.

Add the following code to the end of your index.html file:

<script>
  $( document ).ready(function() {
    $( "#email_subscribe" ).submit(function() {
      $( "#thank_you" ).show();
    });
  });
</script>

Now let’s break that down. After the page is loaded and ready, we use the jQuery $ to get the Document Object Model (DOM), which you can think of as all the code on the page, to get the form element by its ID. If you look in the HTML section of the index.html code, we used <form id="email_subscribe"> to identify the form itself. Now we can call on it by name:

$( "#email_subscribe" )

Calling Elements

jQuery uses CSS style tags to identify the elements, which has the dual purpose of making it easy to use and allowing you to style and use elements with less code. However, when you call on an element you must use the form id=<name> and . for class=<name>. This way jQuery knows what it’s looking for and why you see the # to access ( "#email_subscribe" ).

Next we attach the submit method to the form. This method takes a function to perform tasks, and we will be unhiding the “Thank you” message by again using jQuery to get the #thank_you span element and asking jQuery to show it.

Save your work and give it a test by loading the “latest code” dev link. You now have a working “submit” button, and we are performing actions on the page. In the next section we will start working with server-side callbacks in order to process the data to a spreadsheet.

Here is the full code for index.html so far:

<div>
  <form id="email_subscribe">
    <input type="email" name="email" id="email" placeholder="Enter your email">
    <input type="submit" value="Subscribe">
  </form>
  <span id="thank_you" hidden="true">Thank you!</span>
</div>

<?!= HtmlService.createHtmlOutputFromFile('CSS').getContent(); ?>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>

<script>
  $( document ).ready(function() {
    $( "#email_subscribe" ).submit(function() {
      $( "#thank_you" ).show();
    });
  });
</script>

The Concept of the Callback

When your app needs to do something—like update information on the screen, add data to a spreadsheet, or send an email—it performs what is known as a remote procedure call, or RPC for short. An RPC includes sending code to the server for processing, referred to as a call, and having the server send back the results, known as a callback. I am sure that Java developers are pulling their hair out at this simplistic definition of RPCs, but for what you need to know in Google Apps Script, this about covers it.

In our case, we will want to get the input from the text box and write it to a spreadsheet. This means we need to construct a call from our index.html file and pass our text box value to a function in the Code.gs file, which we can consider as our server. To make it a little easier to understand, let’s get the server-side part started, so there is something to refer to when making the call.

Open up the Code.gs file and add this function:

function addEmail(form){
  Logger.log(form.email);
  return 200;
}

This is a simple function that will let us know that we are indeed getting a response from the server. Remember that you will only be able to check the log if you run the app, but for our case we are going to return the value 200, which is the web standard for a successful execution.

Tip

You can pass JavaScript and other elements in the server request. Google Apps Script is smart enough to know that the elements in a form can be converted to object properties. In this example we will pass the form element, which means we can access the child elements by calling form.<name>.

Now, back in the index.html file, it’s time to make the call by modifying the submit function. Google Apps Script has a few special ways to run an RPC, but the one we will use most of the time looks like this:

google.script.run.withSuccessHandler(serverResponseFunction).serverFunctionName();

What we are doing is calling a server function in one of our .gs files; if it does not send back an error, we run a function here on the client side to process whatever the server returned.

Note

If the server does find an error, then it will log it in the JavaScript Console. You can change this behavior by using .withFailureHandler and then doing something to notify the user.

Replace $( "#thank_you" ).show(); within the submit method function with the following code:

google.script.run.withSuccessHandler(function(ret){
        console.log(ret);
      }).addEmail(this);

Here we are asking to run a function called addEmail on the server, which we will pass the whole form: i.e., this. If you look back at the submit handler, you’ll see that we attached it to the form so that when the button is clicked this represents the form. If the server call is successful, when we get the callback we will run the function within the withSuccessHandler arguments. Because we expect a return value, we add the argument ret to represent what’s returned. It can be just about any JavaScript element.

Just to check out the functionality, we will give a little output to the JavaScript Console. Be sure you know how to open the JavaScript Console on your browser, because we use it a bunch when building frontends.

At this point you can save your work and reload the dev page. In the JavaScript Console you will see a 200, as shown in Figure 4-1.

Figure 4-1. Logging in the JavaScript Console

Believe it or not, the frontend and user actions are almost done. All we need now is to hide the form and display the “Thank you” with a little style. Just above the console.log line, add:

        $( "#thank_you" ).show("slow");
        $("#email_subscribe").slideUp();

Now give it a whirl, and you have finished the user experience side of this application (see Figure 4-2).

Figure 4-2. The final user view

That was a lot to cover, so here is what the entire code for index.html looks like:

<div>
  <form id="email_subscribe">
    <input type="email" name="email" id="email" placeholder="Enter your email">
    <input type="submit" value="Subscribe">
  </form>
  <span id="thank_you" hidden="true">Thank you!</span>
</div>

<?!= HtmlService.createHtmlOutputFromFile('CSS').getContent(); ?>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>

<script>
  $( document ).ready(function() {
    $( "#email_subscribe" ).submit(function() {

      google.script.run.withSuccessHandler(function(ret){
        $( "#thank_you" ).show("slow");
        $("#email_subscribe").slideUp();
        console.log(ret);
      }).addEmail(this); //"this" is the form element

    });
  });
</script>

Functions Are Where the Action Happens

Applications built on the server side in Google Apps Script have four basic types of functions: doGet, which you are familiar with; doPost, which you will learn about later; functions that return values directly; and functions that are intended to be used via a trigger. They aren’t really that different; however, when you use them can be important. For example, you always need doGet to display the UI in a gadget or web app, and if you are using a form from a different service, you will likely need a doPost function.

When you operate a function using an event handler and your intent is to send some information to the server, you must use google.script.run.

Storing the Values

I have saved the best for last—or maybe the easiest part, depending on how you look at it. The app you have been building has all the features needed to interact with the user, but we are lacking the most important thing: a place to store the data and a way to get it there. Not to worry: Google has provided us with several data-storage options that should work for most applications. One great option is a spreadsheet, and this is typically where you would store data like this. Next, there is ScriptDB, which we will get to later in the book. Another option for very important or very large sets of data is Cloud SQL. This is an excellent service and requires payment, but the cost is small given all the features.

Storing in a Spreadsheet

If you have been following along, your script is living on a Google Site, and you have been accessing it through the published URL. What we need to do now is write the visitor’s email address to a spreadsheet. First, we will need a spreadsheet, so please open Google Sheets and make one. You can name it anything you like.

Setting Up the Spreadsheet

The top row of our spreadsheet will be the column names, so we know what the data is. (Yes, it is obvious for such a small app, but you know how things can grow.) In column A1 (top left), name the column “Time Stamp.” Next, name B1 “Email.” See Figure 4-3 for an example.

Figure 4-3. Spreadsheets can be set up for storing values much like one would use a database

Heading back to your script, you will need to add the code to open your new spreadsheet in the script so you can write the email values to it.

In the Code.gs file, above the line Logger.log(form.email);, insert:

var ss = SpreadsheetApp.openByUrl('<YourSpreadsheetUrlGoesHere>')
.getSheets()[0];

Here we are creating a variable, ss, that will represent the first sheet in our spreadsheet. SpreadsheetsApp is a Google Apps Script service, like the HTML Service. Having built-in services that we can call on in the same script makes using Google Apps Script so easy. Next we will use the openByUrl method to get the sheet where we need to write data. Simply skip over to your spreadsheet and copy the URL from the address bar.

What is a spreadsheet ID, you may be asking yourself? In Google Docs, everything—including pictures, PDFs, presentations, and spreadsheets—has a unique ID by which it can be tracked. Fortunately, these IDs are very easy to find. Select your spreadsheet and look in the address bar where the URL is. Find key=. The long string from there up to # is the unique ID. When you want to open a document or spreadsheet you can use .openById and this unique ID. Alternatively, you can use .openByUrl, as we have done here. It should look something like this, with the URL in quotes:

.openByUrl("https://docs.google.com/a/apps4gaaps.com/spreadsheet/ccc?key=0Aq1-
            C9Nl4dO-dHzM9fdXlLV1E#gid=0")

The last part of the code string, .getSheets()[0], simply gets the sheet furthest to the left, or the first one. Note that this is a zero-based call because .getSheets returns an array of the sheets in the spreadsheet. This is an application built to do just one thing, and it will have only one sheet, but if you are concerned that your sheet may move to a different place, it would be a good idea to use .getSheetByName(name). This way it doesn’t matter where the sheet is, just as long as the name stays the same.

It can be useful to know when a visitor submitted a request, so we will need to create a timestamp to go along with the email. When using Google Apps Script, it’s always good to look for ways to make your code more efficient. One of the best ways to do this is to minimize the number of calls you make to any of the other services, inside or outside of Google. While you can certainly write the data to a spreadsheet one cell at a time, you will likely run into timeout issues, and it’ll take forever.

The next thing we need to do is get the range of cells where we want to write our values. When accessing a spreadsheet you get the cells with the getRange method. There are several constructors for the method, such as using “A1” notation, where the column letter and row number are used much the same way they are in spreadsheet formulas (as in A1 or A1:C6). Letters can get confusing, so there is also a constructor that accepts numbers in place of the column letters. We will use .getRange(<row>, <column>, <numRows>, <numColumns>):

var range = ss.getRange(ss.getLastRow()+1, 1, 1, 2);

ss.getLastRow returns the number of the last row of data. We want to write our new submission to the row after that, so we simply add one (+1). Next, we want to start a column, so <column> is represented by a 1. There is only one row of data to write, but with the date, it is going to be two columns wide, hence the 1 and 2 at the end of the set.

Warning

When converting columns from letters to numbers, the first column is number one, A=1. However, after you get the values, they will be in a JavaScript array that is zero-based, meaning column A is now zero, not one.

Setting Up the Data

The data will need to be placed in what is known as a 2D array, so that we only need to write to the spreadsheet once. Here is how that looks: \[[row1 column1, row1 column2] , [row2 column1, row2 column2]], and so on, as shown in Figure 4-4. Any amount of consecutive rows and columns can be written simultaneously in this way.

Figure 4-4. The getRange method creates a 2D array

Because we only have one row, the data looks like this:

var values = [[new Date(), form.email]];

Now that we have the range of cells and the data in a matching configuration, all that is left is to write it:

range.setValues(values);

Save your work and then click the “Run” button in the “Script Editor.” You need to grant access to the SpreadsheetApp Service or face the wrath of an Auth error in the UI. Now load up the web app dev page. Remember that you need to use an email address in the form or the validation will trigger. After pressing the “submit” button, you should receive a “Thank you” message. In the spreadsheet, you will see the results of all your hard work over the last two chapters (Figure 4-5). Please refer to Chapter 1 for a refresher on placing the web app as a gadget in your site.

Here’s the final code for the Code.gs file:

function doGet() {
  var html = HtmlService.createTemplateFromFile('index').evaluate()
               .setTitle('Ch4 Contact Me')
               .setSandboxMode(HtmlService.SandboxMode.NATIVE);
  return html;
}

function addEmail(form){
  var ss = SpreadsheetApp.openByUrl('<YourSpreadsheetUrlGoesHere>')
    .getSheets()[0];
  var range = ss.getRange(ss.getLastRow()+1, 1, 1, 2);
  var values = [[new Date(), form.email]];
  range.setValues(values);
  Logger.log(form.email);
  return 200;
}

Figure 4-5. Each entry is saved in the spreadsheet and timestamped.

We have covered all the basics of displaying web apps and interacting with the Google Apps Script server, not to mention jQuery, CSS, and Google Sites. Take a deep breath and pat yourself on the back, then go grab some coffee. I recommend a Starbucks.Grande().Black(). In the next section of the book we will start creating some pretty complex applications: good luck to you, new Google Apps Script Developer!