Like many of you, I love Google Sheets for collaborating with co-workerson project plans, inventory data, configuration information, and group-solving of puzzle-hunt-style posers.
Sadly, Google’s many spreadsheet functions don’t include ready tools for that last group. It’s hard to imagine we’re such a small population; let’s chalk it up to gross oversight.
Happily, extending Sheets is easy. I had known this for a while but hadn’t really looked at the capability until my puzzle-solving group brainstormed about how we could make better collaborative tools.
Now that I’ve dug in, I’m impressed. If you’ve ever thought Google Sheets fell short of your needs, chances are they didn’t have to.
Here’s how it works. Go to sheets.google.com and create a new spreadsheet using that big plus sign in the lower right. You will, obviously, need a Google account (gmail, for instance) to access this.
Now go to the Tools menu and select Script Editor … It will ask you what kind of script you want to create; you want “Custom Functions in Sheets.” You’ll end up in a new window or tab with a browser-based editor full of sample functions.
Now what? If you’re a JavaScript programmer, you’ve probably looked at the pre-generated samples, sussed out what’s going on, and have stopped reading because you’ve figured it out. Be sure to talk about your cool work in the comments!
For the rest of us, save the document (via the Save command in the sheet’s File menu or your operating system’s normal keyboard shortcut). The sheet will ask you for a project name: “Custom Function Fun,” or anything you like, will suffice. Go back to the spreadsheet you created above.
In cell A1, type “Derrick.” In cell A2, type =SAY_HELLO(A1). You should see the text Loading … and then “Hello Derrick” (Hi!) Woo hoo! SAY_HELLO is one of the custom functions now available to you.
Big deal, you might say. You can do the same thing with =CONCAT("Hello ", A1). That’s true. Though the SAY_HELLO version has the advantage of describing what the value means instead of how you derived it. That’s a powerful documentation technique.
But let’s look at one of the first functions I wrote for my solving group. In these types of puzzles, you often find a solution that has lots of numbers from 1 to 26. You’re usually supposed to convert these numbers into a letter of the alphabet. Google has functions for converting numbers into letters, but they assume Unicode encoding, which means 65, not 1, is “A.”
If I wanted to do this solely with Google Sheets functions, I could do something like this (I use 64 because I want “1” to be equal to “A,” which is 65 in Google Sheets land):
=(CHAR(64 + D1))
But what if someone types “175?” Or “-3?” It’s a bit safer to do this, which will print letters for safe numbers and a blank string for everything else:
=IF(AND(D1 >= 1,D1 <= 26), CHAR(64 + D1), "")
That’s not too horrible, though it’s becoming a bit unwieldy to read. Hopefully you labeled your column with some meaningful name! Now, using nothing but Google’s built-in functions, how would you look up Morse code sequences — another puzzle hunt staple — in one cell and set a new cell to the appropriate letter? Let us know how that works out.
Let’s replace our index lookup with a custom function. Go back to your Script Editor and, after the “*/” on line three, type Return a few times and then type the following:
/**
* Given a number from 1 to 26, return the appropriate alphabetic character.
*
* @customfunction
*/
function INDEX_INTO_ALPHABET(index) {
if (index == null || index < 1 || index > 26) {
return "";
} else {
return String.fromCharCode(64 + index);
}
}
Save, and go back to the spreadsheet. In A1, type “1.” In A2, type =INDEX_INTO_ALPHABET(A1) into the formula editor. Note that because you put @customfunction in the comments, it will show up in your autocomplete menu. A2 should get set to “A.”
If you’re not really familiar with JavaScript, there are lots of guides that will help you get something working. Or you can browse the premade options in the Add-on Store for Sheets.
But if you are comfortable with JavaScript or other mainstream languages, you have a lot of the capabilities you’d expect. My actual INDEX_INTO_ALPHABET function, which — like all my group’s functions — uses a private helper function to handle space-delimited text, looks something like this:
function INDEX_INTO_ALPHABET(input) {
return _forEachWord( input,
function(token) {
var parsedInt = parseInt(token);
return parsedInt < ALPHABET_START || parsedInt > ALPHABET_COUNT ?
UNKNOWN_INPUT :
String.fromCharCode((ASCII_MIN - 1) + parsedInt);
}
);
}
Don’t worry if this doesn’t make sense: The point is that programmers can take advantage of all their normal conceptual tools. Your scripts don’t have to be fancy if they get your job done.
Custom functions are a quick, powerful way to extend spreadsheets for your purposes. But they’re just the beginning.
How about adding your own menus? We did just that, adding a suite of tools that lets us quickly reshape blocks of cells into common puzzle shapes. It couldn’t be easier. Inside your script file, add code that looks like this:
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('My Menu')
.addItem('Handy Tool', 'doHandyToolStuff')
.addToUi();
}
function doHandyToolStuff() {
var ui = SpreadsheetApp.getUi();
var result = ui.alert(
'Please confirm',
'Are you sure you want to continue?',
ui.ButtonSet.YES_NO
);
}
Close and re-open the spreadsheet, and you should see your new My Menu on the far right. When you select the Handy Tool option, it will call the function named doHandyToolStuff(). Showing a dialog isn’t very exciting, but it gives you a sense of how to expand your spreadsheet. My puzzle group’s custom menu includes tools to lay out a grid of squares, create symmetrical layouts for filling in crosswords, and we’re adding more all the time.
Dig a little bit deeper, and “spreadsheet” begins to seem like an outdated term for what’s in front of you. Think of it instead as an application development platform. Even without being a serious coder, you can quickly build a sophisticated tool designed for your needs. If I were still tracking my daughter’s sleep, I’d do it in Sheets. You can create useful UIs in custom sidebars. You can call out to Google services or even random third-party APIs. Even a week and a half into building my puzzle group’s tool, I’m bursting with ideas as I read more of the Sheets documentation.
Google Sheets has always been useful. But now I view it as a powerful tool I can use to do all manner of tasks.
Have you done cool customizations of Google Sheets? Let us know in the comments!
