I made a game. It’s in a Google Sheet:

really, really, I'm fine

There’s some stuff going on here! But in addition to aesthetics this game is “real time” - the spreadsheet tracks the time when you complete tasks and compares those times to where the animated blue progress bar is. Your final evaluation (in the lower right) tells you whether you completed your tasks early, on time, or late. This is all driven purely via Google Sheets formulas - no Apps Script or other trickery needed.

Let’s talk about how this works!

Playing the game

Before we get into it - here’s a link to the sheet if you want to play. To play the game, navigate to the “GAME” sheet, refresh your browser tab, and then check the box in the upper left when the first gif says “go.” And then try to complete the tasks in the sheet while animations are playing next to them.

Note that you’ll be prompted to copy an Apps Script as well. That script is just used to reset the game to its starting state so that you can replay it.

The basics

This game relies on 3 tricks to power its interactivity and make it work in real time:

Using circular cell references that allow us to record the first time an action is performed.
Embedding images in cells so that we can swap them out via formulas
Adding many seconds of “silence” to non-looping gifs to make unrelated gifs seem connected.

I you want to see all of the calculations in the sheet open up the “GAME” tab, scroll down, and click the checkbox to view the ‘calculation zone.’

Circular cell references tell us the time

Examples from this section are available in this tab. Mouse over and off of any gif to make it stop playing.

To understand whether an action (like checking a checkbox) is “early” or “late,” we need the time at which the action was performed. That’s a little hard to do. Here’s a naive attempt:

not particularly useful

A cell that depends on a NOW call updates its value whenever another cell in the sheet changes¹ (the “force recomputation” checkbox is just to make it clear when I am editing the editing the sheet and forcing an update). So instead of logging when our “BEGIN” box is checked, we’re just logging the current time! What we want is “the current time, only if we haven’t updated this cell yet.”

And it turns out that we can do exactly that, as long as we enable iterative calculation so that we can make recursive cells.

a little more useful

By making the formula in A10 depend on itself, we can create a cell that only updates once. The first time that BEGIN is checked, AND(A10=0,BEGIN) is true (because A10 has no value, and Google Sheets resolves circular references to empty cells to 0), and so we set A10 to the current time. From then on A10 is never 0, so we always return A10’s current value. So A10 can’t be changed and is frozen to the timestamp when I made this example! This is neat but not super useful yet - we want our game to be replayable, so we need some way to reset our stored value.

almost there

This works! Adding a second IF gives us a cell that updates the first time that BEGIN is checked, and then holds onto that value until BEGIN is unchecked. But in the game I didn’t want unchecking a cell to clear its timestamp since I figured it was easy to uncheck a cell on accident. So instead I use a separate RESET value for clearing state, meaning that BEGIN can be toggled off without clearing the timestamp:

that's it!

And that’s the primary technique that this game ends up using to log times. It’s useful for other purposes too - for example, I generate random numbers for the math problem by memoizing a call to RANDBETWEEN.

Embedding images in cells

I also make heavy use of the images embedded in cells. You can embed an image in a cell via the “insert” tab, or you can hotlink an image via the formula IMAGE("LINK"). This is fun on its own (it’s weird to see images embedded in cells!) but very powerful when referencing images via formulas, which allows us to dynamically swap images in and out.

I can't see without my 3d glasses

I use this for the “find my glasses” and “fridge magnet poetry” games. I’d like to say that I also use this technique to swap in gifs to dynamically trigger animations but unfortunately that doesn’t work! Gifs embedded in cells only display their first frame. This is a huge bummer because being able to swap out animated gifs on the fly would be super powerful. Instead, animated gifs need to be overlaid on top of cells and will automatically play when the sheet loads.

Chaining unrelated gifs

So all of our gifs play on sheet load and they need to all play in sequence. One approach I considered for this is making a single megagif that is overlaid over the whole sheet but that sounds pretty unwieldy (and I think the gif would get huge)². So instead I generated a series of gifs for the sheet and used convert (part of the ImageMagick suite of tools) to embed more and more “silence” (e.g. repetitions of the first frame) in each gif so that they’d play one after the other.

The code for that looks something like this:

#!/usr/bin/env bash

_empty="bar-empty.png"
empty_measure="$_empty $_empty $_empty $_empty"

main() {
    measures_of_silence="$1"; shift
    target_file="bar-${measures_of_silence}beats.gif"
    files=""

    while (( "$measures_of_silence" > 0 ))
    do
        files="$empty_measure $files"
        measures_of_silence=$((measures_of_silence-1))
    done

    files="$files bar1.png bar2.png bar3.png bar4.png"
    convert -dispose previous -delay 25 -loop 1 $files $target_file
}

main "$@"

Briefly: we take in the number of seconds that we want our animation to wait before playing, and embed that many seconds of just our first frame (delay is in centiseconds; we’re running at 4 frames a second). Then we create our gif, which is always 4 frames (animations always last one second). And we disable looping (by setting our loop count to 1) because it’d look weird if our starting animation played the whole time.

Putting it all together

To start the game I have players refresh the tab, which causes all the gifs to reload. Then they check the box in the upper left when “GO” is displayed - this gives me a start time from which I can derive when other animations on the sheet should play. I also pay attention to when some other early tasks are performed and use that to update my notion of when animations are playing (in case the player checked the first box early or late).

All the tasks in the sheet use the same trick to track when they’re completed - the game compares those times to a hardcoded “range at which this task can be performed” to decide whether you’re early, on time, or late. This, along with a giant table of calculations, drives the evaluation that you get.

calculations from the first two zones

Most of the rest of the sheet was about spending hours messing with formatting to make everything look right. A few other tricks or hard to google things that I can call out:

To make the “pops” section I hand-coded 15 conditional formatting rules to make one new cell show every time you check a cell.
To make the code easier to read I make heavy use of named ranges and named functions
Iterative calculation sometimes requires an extra page edit to force all values to update³. This is why there are 3 checkboxes to show the final “report” - only two are needed for the report to be displayed, but having 3 encourages folks to toggle all three of them on and off, and the 3rd toggle should always force a refresh to the “right” state.
The one thing I couldn’t figure out how to do without Apps Script was reset the state of the game! So I added a script to do that during testing and just kept it in for the final game. It makes the sheet a bit less pure but I think it’s worth it.

Acknowledgements

I got started with Google Sheets games during a gamejam at the lovely Wonderville bar in New York, where Blake Andrews runs a monthly 2 hour gamejam using unusual engines. For this jam Alexander King gave an excellent talk about how (and why) to build games in Google Sheets, which taught me some of the techniques that this game uses. Alexander was also nice enough to chat with me after the jam and point me in the right direction on using recursive references for impure memoization. Thank you both!

I built this game in my last week at Recurse Center, a place that functions like a writers retreat for programmers. Recurse has been an incredible environment to build and share fun and bizarre stuff like this game. If that sounds fun to you you should consider applying!.

Sheets can be configured to auto-update up to once a minute, but that’s infrequent enough that it doesn’t come into play in this article ↩
Although this honestly might have been less work! ↩
I don’t quite understand why but it seems like values that depend on a circular reference sometimes update before the circular reference resolves, and sheets doesn’t go back and update those references again until the next edit cycle. ↩

My Sheets Adventure