I first noticed them about two weeks ago: the squares. Grids of black, orange, and green square emoji flooding my Twitter feed. The people posting them seemed to be happy about them. It turned out they came from a game: Wordle, a daily word-guessing game by artist/programmer Josh Wardle.
It’s simple enough: there’s a new word each day, and you have six chances to guess it. Get it right, and you get a cute little emoji grid to show off how you did.
With the rise of Wordle came the imitators, and while some have chosen to copy Wordle’s entire gimmick (the worst offenders were removed from the iOS App Store), the really fun ones put their own spin on it—there’s a bunch over on Glitch, like Horsle (spoiler: the word is always “horse”). As a part-time spreadsheet artist, I wanted to see if Wordle could be adapted to my medium of choice: Google Sheets.
And here’s the result: SHEETLE!
It’s a self-contained, mostly complete copy of Wordle, with daily words and six guesses and everything—all running in Google Sheets. Once you’ve made a new copy of Sheetle, you can look behind the curtain by right-clicking on row 12, and selecting Unhide rows. That’ll reveal rows 1-11, where all the magic happens.
It works using three lesser-known Google Sheets features, which can pull off some really interesting things when combined. Put super briefly, here’s what’s happening behind the scenes.
1. Start with good data
When starting a new spreadsheet, one of the more daunting tasks is always data entry. Luckily, with projects like this, it’s highly likely that the work can be done automatically (hi, Zapier!) or that someone has already done that work for you.
In this case, I went to GitHub and searched for “five-letter words,” which brought me to Chaz Reid’s aptly named “five-letter-words” project, which does what it says on the tin: creates a big list of five-letter words. Thanks to Chaz using the MIT license, I can safely use that data in my own project, and not spend days manually entering every five-letter word I can think of.
Google Sheets has several functions for importing data, but since the words I was using each had their own row, I could use IMPORTDATA. Usually, that would create a new row for each line in the document, but since I only wanted one word at a time, I used INDEX to tell the sheet which specific row to stick to, and UPPER to make it uppercase.
2. Recalculation and iterative calculation
No wait, come back! Google Sheets make these features sound way scarier than they actually are, and enabling them can take your sheets to a whole new level. You can find them in your spreadsheet under File > Settings > Calculation.
Recalculation changes how often your formulas recheck their math, and Iterative calculation changes how many times they perform that check. In this case, I have Sheetle check its formulas any time something changes and every hour, and when it does that, it rechecks everything one extra time.
The added perk of this is now your cells can have formulas that reference themselves (yes, your sheets are now self-aware!), allowing you to turn checkboxes into buttons, build whole games from scratch, or (with a little extra math) start making 3D graphics, all right there in your spreadsheet. For Sheetle, they help grab the player’s guess each time they hit the Submit button, and add it to the list of guesses.
3. Hidden ranges
When you select multiple cells in Google Sheets, you’ll often see text like “A1:B3” appear. That’s called a range, and it’s how Google Sheets knows that there are actually several items that it should be paying attention to in the formula.
Normally, Sheets will want to look at all of those items at once, but by using functions like ARRAYFORMULA, you can tell it to do something with each value dynamically. For example, to create the little emoji grid, I wanted to compare each letter of each guess to each letter of the final answer, so I split them up and used ARRAYFORMULA to compare each one.
If left alone, ranges can take up quite a bit of space, which isn’t always great. To condense the results into one cell, I’ve dropped in TEXTJOIN and REGEXREPLACE functions, which bring the results back into one place and add line returns where needed.
Put together, I split up the answer and guesses to create two new ranges, using ARRAYFORMULA to compare them, then TEXTJOIN and REGEXREPLACE to make them disappear again. It’s like the ranges were never there!
A new kind of art project
This is the secret fourth trick—a special treat for making it this far: just keep trying stuff. There’s no right way to make Wordle in a spreadsheet! And the only “wrong” way to make something is to not make it.
The only reason I learned all of what you just read is because I asked, “I wonder if that could work” and kept trying stuff until it did. Yes, it’s a spreadsheet, but if you approach it like an art project, and—dare I say—try to have fun with it, you’ll stumble into solutions that you wouldn’t have considered otherwise. Or, if spreadsheets aren’t your thing, maybe take up crochet, or scrapbooking. What I’m trying to say is that times are hard, and you deserve to be happy.
If you get stuck, Google’s documentation is a lifesaver, or you can check out my portfolio (yes, another spreadsheet) for more examples, or join my tiny, spreadsheet-focused Discord server, Sheet Street. Happy sheeting! ????
This article originally appeared on Zapier’s blog and is reprinted with permission.
Recognize your brand’s excellence by applying to this year’s Brands That Matter Awards before the early-rate deadline, May 3.
