A couple of folks have asked for an example of what Google Apps Script is useful for. The things I've done so far are pretty specific applications, so not useful to the general public. I decided to put together a simple script which may (or may not) be more generally useful, but still shows off some of the coolness of Google Apps Script.
If you're a tactile learner like I am, I encourage you to actually type in the samples rather than copy/pasting them. I don't learn anything by using copy/paste.
I generally work from home, so when I do go into the office, there are usually a number of coworkers there who also generally work from home. We try to schedule a lunch during the day, but when the time comes to get into cars, it's a long and painful process of trying to decide where to eat. I guess none of us has an alpha personality, so we each are trying to please the others. The goal of this script is to narrow down the selection process using a more alpha personality - a PRNG.
The workflow works something like this:
- Create a Google Form where you'll enter the name, address, and a description of a restaurant. The very first location saved needs to be the "home" location - in my case, the office. This process can be done at whatever interval - just putting together a list of candidates.
- As entries are saved, a trigger will call a Google Apps Script function which will use the Maps API to calculate the driving distance to the location and store that alongside the remainder of the entry.
- When it's time to select a restaurant, open the spreadsheet of responses. A sidebar will appear
- Using the sidebar, select a maximum driving distance and click Pick. Another script will run which finds all the entries within the specified driving distance and display the name of the PRNG-selected restaurant.
There's very little error checking in this so far, so you've been warned.
Use Google Drive to create a new Form. This form needs to collect the name, address, and description of the restaurant. (Right now, the description isn't used, so feel free to mess with the form as you wish). The way the scripts are written, it's not using column headers to determine where to get which pieces of data, so it's important that if you're not comfortable manipulating the column indexes in the script, that you have it collect Name, Address, and Description - in that order.
You need to fill out the form one time with the address of the "home" location - for example, the office where you work. This first row is used as the home for calculating the driving distances to other locations.
TODO: Can you think of a more elegant way of selecting the home location? Maybe at runtime, having the user enter their location - and store previously used locations so that they can pick a previous one from a list.
With Google Apps Script, you can specify Triggers which execute at various lifecycle stages of the spreadsheet, or even at various times. In this case, we'll have a trigger run each time a new form submission is posted which will calculate driving distances. This uses the Maps API to figure the driving distances.
On the spreadsheet with the form submission results, click on Tools/Script Editor. In the Code.gs file, add the following function:
/**
* Set this to execugte as a trigger whenever a form is submitted.
*/
function updateDistances() {
var sheet = SpreadsheetApp.getActiveSheet();
/* The home address is assumed to be the first entry, and the address is assumed to be in the
third column */
var homeAddress = sheet.getRange(2, 3).getValue();
// For each of the remaining rows
for (var i = 3; i <= sheet.getLastRow(); i++) {
// Use the Maps API
var maps = Maps.newDirectionFinder();
// Set the origin to the home address, and the destination to the third column of this row
var result = maps.setOrigin(homeAddress)
.setDestination(sheet.getRange(i, 3).getValue())
.getDirections();
var miles = 0;
// The result from getting directions has a list of possible routes, and if you were
// to add multiple waypoints, there would be multiple legs. Each leg has a number of
// steps with its distance. The total driving distance is the sum of all of those
// step lengths.
for (var x = 0; x < result.routes[0].legs[0].steps.length; x++) {
miles += result.routes[0].legs[0].steps[x].distance.value;
sheet.getRange(i, 5).setValue(miles / 1609);
}
}
}Now we need to add the trigger to call this function when a new form entry is submitted.
- Click Resources/Current Project's Triggers
- If this is the first time you've added a trigger, you'll be prompted to give this project a name
- Click Add a Trigger
- Select updateDistances from Spreadsheet and On Form Submit in the dropdowns
Submit another form entry and then look at the spreadsheet to see if a new column with the distance (in miles) from the home base.
It might get a little confusing which script executes where in a Google Apps Script. After all, it's all Javascript, so it's all running on the client, right? Not exactly. All of the script we wrote so far (the Google Apps Script part) runs on the server. While it's Javascript, there are a handful of cases where the fact that parts run on the server and parts run on the client might get confusing, and can make debugging a challenge.
For Google Apps Script, when a script runs, it logs all of its own events with some timing instrumentation, which is handy. (Word for the wise: accessing individual cells in a spreadsheet is very slow, so whenever possible, use Range.getValues() to get out all of the values within a range in one shot, then deal with the result as a two-dimensional array.)
Now we're going to start writing some script which executes on the client side.
Within the Code Editor, select File/New and then pick HTML File as the file type. This adds another file within your exsitng code project added to the editor. Call this file ui. On the left side of the code editor, you can pick which file within the project you're editing.
Enter the following code in the code editor:
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<div>
<p>Select the maximum distance you're willing to travel for lunch, then click
<em>Go</em> and we'll pick a place for you.</p>
<div class="block form-group">
<label for="maxDistance">Maximum Distance</label><br />
0.5mi <input type="range" name="maxDistance" id="maxDistance" min="0.5" max="10"> 10mi
</div>
<div class="block form-group">
<button class="blue" id="choose">Choose!</button>
</div>
<div id="restaurant"></div>
</div>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script>
/* From within the sidebar, we can't communicate DIRECTLY with the spreadsheet, so we need
to use google.script.run to execute a script within the spreadsheet. */
$(function() {
$('#choose').click(function() {
google.script.run.withSuccessHandler(function(choice) {
$('#restaurant').text(choice.name);
}).pickRestaurant(Number($('#maxDistance').val()));
});
});
</script>Here are some things to note about this bit of script:
- It's using some styles defined in the Google Drive Add-Ons CSS Package.
- It's using jQuery for the client-side scripting. Since it's client-side, you could use whatever other scripting you like (I think).
- This client-side script actually doesn't have access to the spreadsheet that it's running on. So you'll see a call to
google.script.run.pickRestaurant(). This uses the Google Script Runner to call a server-side function (which does have access to the spreadsheet) which we'll define later. google.script.runallows you to specify a callback that is called when the script executes successfully, and this handler is passed thereturnvalue from the server-side script (if there is one). Notice that it can also pass arguments to that server-side script. Pretty slick!
So this just makes a slider to pick your distance, and a button which calls some client side script to initiate the server script. When the server script returns its selection, we just update another div with the name from that selection.
TODO Add a UI element to display the current value of the slider. Update the callback to include the address, distance, and description of the selected restaurant.
BONUS Use the Maps API to render a static map for the selection.
Back to the server side. We wrote some nifty client-side script, but we have no way to render it, and the server-side function which selects a restaurant hasn't been written.
Creating the sidebar is pretty easy. Add the follwing function back in Code.gs:
/**
* Runs automatically when the spreadsheet is open. Open the sidebar.
*/
function onOpen() {
var app = SpreadsheetApp.getUi();
var html = HtmlService.createHtmlOutputFromFile("ui").setTitle("Pick a Place");
app.showSidebar(html);
}We don't have to add a trigger for this - the spreadsheet will automagically run the onOpen() function when the spreadsheet is opened. It just uses the HTML service to load up some HTML from the ui file we created, then shows that in the sidebar.
The last little bit here is to write the server-side function that reads all of the spreadsheet rows and uses Math.random() to randomly select one of the rows whose value is less than or equal to the selected range. Notice that this script can take parameters (in this case, distance is the maximum distance selected from the UI we worked on earlier) and it's able to return a value (which is passed as an argument to the callback passed to withSuccessHandler in the client script).
As I mentioned before, getting a Range is pretty expensive, so we want to get the entire range at once and turn that into a javascript table we can iterate over.
/**
* Selects a random location within the specified range.
*
* @param {Number} distance The maximum distance to search.
* @return {Object} The selected restaurant.
*/
function pickRestaurant(distance) {
var sheet = SpreadsheetApp.getActiveSheet();
var pool = [];
Logger.log("Max distance is %s", distance);
var data = sheet.getDataRange().getValues();
for (var i = 2; i < data.length; i++) {
if (! data[i][0]) {
continue;
}
Logger.log("Distance is %s", data[i][4]);
if (data[i][4] <= distance) {
pool.push({"name": data[i][1], "address": data[i][2], "description": data[i][3], "distance": data[i][4]});
}
}
Logger.log("Pool size is %s", pool.length);
selection = pool[Math.floor(Math.random() * pool.length)];
Logger.log("Sending restaurant %s", selection.name);
return selection;
}As I mentioned, it gets a little ugly trying to debug this stuff sometimes. The script-side code is a little limited in what you get back. Sometimes a little bit of information shows up in the browser console, but generally you'll have to go back to the script editor and click on View/Log to see your own logging statements, or View/Execution Transcript to see the information that GAS is logging by default.
The client-side code will show error messages in the browser console, but the code you write that's client-side isn't the final script that gets to the browser - so the script it's in has a randomly-generated name, and there's no map of that back to the original script. So use logging.