Last week, I wrote a post about how to perform a simple mail merge in Google Docs. In the post, I mentioned that I'd eventually talk about how to modify the publicly available mail merge program to send more advanced or tailored emails. Before we can do that, however, I wanted to give a very basic overview of how scripting works in Google Docs. In addition to being useful for thinking about the mail merge, hopefully this introduction will give you a sense of how you can use scripting in Google Docs to extend it's functionality in any number of ways. In addition, if you've never programmed before, this is a great way to get started.
Scripts add functionality to Google Docs
If you've spent any time within a spreadsheet application, you've probably made use of any number of built in functions. Whereas most entries in a spreadsheet are static values (like a number or text), functions are ways to process values and return a result. Probably the most commonly used function is the summation, used by typing "=SUM(A1:A5)" into a shell. Most programs, such as Excel and Google Docs, have a bunch of built in functions that you can use. They also, however, let you define your own functions to augment the abilities of your spreadsheets. For the purposes of this introduction, scripting in Google Docs is the process of creating these new functions. Before going further, I should point out that Google Docs scripting lets you do a lot more than just define new spreadsheet functions, but for now, it's a reasonable place to start.
Hello World!
Whenever the summer Olympics roll around and gymnastics occupy every prime time hour of NBC's schedule, I always find myself thinking "how did gymnasts ever learn to do any of this?" The maneuvers are so complicated that it's hard to imagine how someone would even get good enough to start practicing them. If you've never programmed before, you might have a similar perspective on writing programs (or scripts). As a way of demystifying the process, most programming introductions start with writing something called a "Hello World" program. This program merely prints the phrase "Hello World!" to the screen in some way. While that in and of itself is obviously not very useful, it can serve as a handle to help understanding about writing programs for a particular platform.
So, let's get started by writing a "Hello World" program in Google Docs. Start by creating a new spreadsheet in Google Docs. Next, we need to open the Script Editor through the Tools -> Scripts -> Script editor menu. Upon opening the Script Editor, you should have a fairly empty window with the following text in it:
function myFunction() {
}
As mentioned above, functions are pieces of code that take in some information, perform some processing of that information, and return a result in some way. This empty function shows the syntax for defining a new function in Google Docs. All functions start with the word "function," followed by the name that will be used to run the function (in this case "myFunction"). The first set of parentheses will define the inputs that the function accepts (the information that goes in). Everything inside the curly brackets describes how to process that information. So, we want to create a new function that will return the value "Hello World!" To do this, replace everything in the script editor with the following:
function helloWorld() {
var message = "Hello World!";
return message;
}
So we now have a new function that we've named "helloWorld." This particular function doesn't need to take in any inputs, because the result will always be the same message. As such, we've left the parentheses empty. In the second line, we've introduced a new concept called a "variable," which is an entity that stores information. In this case, we've used the equals operator to assign a value of "Hello World!" to the variable called "message." The preceding "var" serves to tell the program that "message" will be used as a variable, and it is only needed the first time we use a particular variable. From now on, any time we type "message" it will be replaced by it's value ("Hello World!"). The last line is how a function returns it's result, using the command "return" followed by the information to output as the result. In this case, we are returning the contents of the message variable. Once you've copied the function in, click save, and choose a name to save the script.
You've now written your first function for Google Docs, so let's try using it. Switch over to the main spreadsheet, and type into a cell "=helloWorld()" (don't include the quotes). After you hit enter, the cell where you entered the new function will change briefly to say "Thinking . . ." and then be replaced by the message from your function "Hello World!" If you go back to your script editor and change the message to something else and then save the script, you'll see that the spreadsheet automatically updates to the new message. Feel free to mess around with the function a little bit to return different things and see how the changes show up in the spreadsheet.
Hello
This post is already getting a bit long, so I just want to show one more variation on the script in which we create a function that accepts input data. Copy the following into your script editor and save the script (you can just place this code on a new line after the end of the helloWorld function):
function helloName(name) {
var message = "Hello " + name + "!";
return message;
}
This new function, helloName, works very much like helloWorld, but it has a single input represented by the variable "name" in between the first set of parentheses. Just like the variable "message," "name" will be replaced by it's value whenever it is used, but unlike "message," "name" is defined outside of the function and then passed as an input. The message in this function is a little more complicated that in helloWorld. In this case, we use the + operator to join multiple strings together. The message will combine "Hello " with the inputted name and the exclamation point to produce a single message.
Head back over to the spreadsheet, and type your name into an empty cell. Then move to a new cell and type "=helloName(A1)" where A1 is the cell where you typed your name (or you can instead choose the cell with your mouse after typing the open parenthesis). After hitting enter, the formula should update to say hello.
Plenty more to come
Alright, this post is already getting pretty long, so I think I'll pick things up next week. We haven't done anything useful yet, but hopefully you at least have an idea about how functions are defined in Google Docs, and how that might be useful. It may not seem like it, but we're not too far away from being able to understand and change an existing Google Docs script, such as the one that enables mail merges.
Sign up to receive updates in your inbox