How do you ask Google Sheets to tell a Dad Joke?
How do you ask Google Sheets to tell a Dad Joke?
"Everything Starts Out Looking Like a Toy" #112
Hi, I’m Greg 👋! I write essays on product development. Some key topics for me are system “handshakes”, the expectations for workflow, and the jobs we expect data to do. This all started when I tried to define What is Data Operations?
This week’s toy: ASCII Patrol, an all-ascii version of Moon Patrol. It goes to show that some of the classic games still feel fun and fresh to play (Platformers FTW). Edition 112 of this newsletter is here - it’s September 26, 2022.
The Big Idea
A short long-form essay about data things
⚙️ How do you ask Google Sheets to tell a Dad Joke?
Webster’s dictionary defines Dad jokes this way:
“a wholesome joke of the type said to be told by fathers with a punchline that is often an obvious or predictable pun or play on words and usually judged to be endearingly corny or unfunny.”
The fact that scholarly research is being done on this cultural phenomenon indicates that the Dad joke is here to stay, much to the chagrin of many people who don’t like this form of stupid humor.
When Google Sheets announced Named Functions to take a formula action and rename it as a custom function, I immediately thought about creating a simple example to demonstrate the power of these reusable no-code functions that anyone can build. Naturally, I chose the Dad Joke as the format for my demo.
My function DADJOKE takes a search term, calls a source for dad jokes, and then lets you select a joke from the returned list. When you’re done you can call =DADJOKE(“dog”,3) in a Google Sheet (copy the sheet at this link to try it yourself) to get the third dad joke returned from the list. If your search term doesn’t find any jokes, you’ll get a message that the search returned no jokes.
Let’s talk about the steps required to create this named function, how you might use it, and what benefits it opens for Google Sheets users, even if you don’t like Dad Jokes.
TL;DR about Named Functions
Named Functions let you build functionality from a formula that can be referenced using a new function signature and also imported into other Google Sheets spreadsheets.
In our example, we take the following formula, which returns a joke from the icanhazdadjoke site, and simplify it, turning:
=QUERY(IMPORTHTML("https://icanhazdadjoke.com/search?term=dog","table",1),"select Col1 limit 1 offset 3")
what do you call a dog that can do magic tricks? a labracadabrador
Into a much simpler function call:
=DADJOKE("dog",3)
what do you call a dog that can do magic tricks? a labracadabrador
Named Functions are built out from an extension to the existing Google Sheets Named Ranges functionality, which have been around for quite a while. Named Ranges were (and are) a great way to simplify references within a spreadsheet, but couldn’t be shared easily with users of other spreadsheets, making it more challenging to share a solution.
Named Ranges let you identify an area of the spreadsheet with a range, let’s say cell A2 below to mark the taxRate. Enter (almost) any value you want here, including a static value like the one in this example, or a reference to another field, or a formula. Google Sheets will happily calculate the result to this cell and make it available to you using the name of the named range: taxRate.
After you set the named range, it’s available to you elsewhere in the spreadsheet. For example, when you need to refer to the taxRate in a formula, you can now use that value as you would a variable in a typical formula, evaluating to the value you input in the A2 cell.
Named ranges have an issue - when you try to use them from another spreadsheet, they don’t work. Even if you copy them to the same spreadsheet, you need to re-establish the range in each copy to get them to work. So Named functions are a much better solution here because they are a better way of packaging up this user-defined function for use.
Arranging our Dad Joke Data
Now that you get the idea of a named range and the difference between that and a Named function, let’s review how we built this formula to retrieve a Dad Joke. At the beginning of the process, we knew we would want to send a term (“dog”) to the search page and retrieve some results.
Ideally, we would send an API call to the search and retrieve a list of results, as in the example below:
Google Sheets supports this approach using gScript, but this is not natively available within a formula. However, the excellent function IMPORTHTML has an option to pull data from within an HTML table, so here we go!
The search at the icanhazdadjoke site supports entering a term and displaying the results as an HTML table, so this is a good source for our formula.
We’ll use this formula to retrieve information from this HTML table:
IMPORTHTML("https://icanhazdadjoke.com/search?term=dog","table",1)
IMPORTHTML does the following:
points at a URL - in this case, “https://icanhazdadjoke.com/search?term=dog”
queries either a LIST or a TABLE depending upon the structure you want to import, and builds it into an internal array in Google Sheets
selects an Index, starting at 1, to indicate which structure to import if there are more than one LIST or TABLE element to import from the page
This means our formula will retrieve all of these answers in an array. Now we need to determine how to return a single joke, how to pass a specific term to the page that returns the jokes, and to skip the initial row of the table. While we’re at it, we should probably build in an error check to return a message when there are no jokes found for the term we searched.
Prototyping a solution
Now, we need to take the information we get from the IMPORTHTML function and select only a single row from that table we’ve retrieved. To do this, I like to use QUERY, which lets you run a SQL-like search over a series of data in an array. Here we use QUERY to wrap the response from the IMPORTHTML function, and then use the syntax of limit to return a single row and offset to select which row of the table to return.
=QUERY(IMPORTHTML("https://icanhazdadjoke.com/search?term=dog","table",1),"select Col1 limit 1 offset 3")
This returns a single joke to our formula. Finally, we can wrap this function with an IFERROR function to return a message if there is no joke returned.
Packaging this formula as a named function
Now that we have a way to request a list of jokes and filter that list to a single joke, we’re ready to package this formula into a Named function in Google Sheets.
To do this, use the Data > Named functions to create a new Named function using the formula we just created.
This panel creates a function you can call simply, as follows:
=DADJOKE("dog",3)
what do you call a dog that can do magic tricks? a labracadabrador
Because this is a Named function, another sheet can now import this function for use.
What can you do with Named functions?
Named functions let you abstract the logic of a spreadsheet from multiple formulas into a single function easily used to provide a result.
Things you might do here:
Complete a multi-step normalization of an email address, including trimming, checking for format, and identifying from a list of known bad addresses
Chain a number of named functions to do a complex thing (import them both!)
To learn more, check out this article from Ben Collins on using Named Functions.
What you can’t do:
overload existing functions by using the same name
What’s the takeaway? Named functions are a powerful way to abstract logic in Google Sheets and make it easier for other users to implement this logic in their Sheets without having to learn complex formulas. Instead of needing to explain your logic, give them a function signature that’s easily defined. If you want to try out the DadJoke example, copy this Google Sheet.
Links for Reading and Sharing
These are links that caught my 👀
1/ the default option is powerful - we all know that people tend to pick default options, but do you know the magnitude of the effect? This essay shows the impact on organ donation in countries that opt in vs countries that opt out.
The next time you build a default option, spend a bit more time thinking about what’s there and the impact of choosing it.
2/ Hurricanes influence baby names - when storms roll in, people tend to name their kids that name less often. This is not really surprising when you consider it, and interesting that it has a measurable effect.
3/ How to get stuff done - Do the work that no one else wants to do. When you complete dirty work like QA, customer success, or rewriting documentation, you are finding the basic problems with products, process, or technology. Learning how to take these insights and distill these items into improvements is a fundamental product skill.
What to do next
Hit reply if you’ve got links to share, data stories, or want to say hello.
Want more essays? Read on Data Operations or other writings at gregmeyer.com.
The next big thing always starts out being dismissed as a “toy.” - Chris Dixon
Hilarious. This is so you!!! love it!