How to waste time and abuse Google Sheets for personal amusement
Lately I’ve been doing some spring cleaning of my computer and uncovered a few gems that were sitting around for a long time. A demo I wrote for the Sheets API a few years back was one of my favorite little hacks that was never released as an API sample. I thought it was time to revisit it.
This original demo was simple — it treated a sheet as a canvas for drawing images, with each cell as a pixel. While not so useful in the real world, it produced some cute results and was useful for testing out the API. It just so happened that drawing images with the API hit on a lot of features — bulk setting cell values, formatting, resizing sheets, etc. But as fun as it was to draw static images, it wasn’t enough.
I wanted movies. Well, at least some animated GIFs!
So after dusting things off, I took a stab at animated gifs and learned a few things along the way.
I =CHOOSE poorly
When I first thought about how to represent animations in cells, I was (overly, in hindsight) concerned with API quota and how much data I was sending. I wondered if there was a way to encode all the frames of the GIF in the sheet to minimize updates during animation.
The =CHOOSE function was just what I was looking for. The function yields a cell value based on a lookup list. In this case, the idea was to store the frame index in 1 cell, then encode the colors for each frame of each pixel in the formula.
Taking a step back, you can play around with how this works with a simplified version:
=CHOOSE($A$1, “|”,”\”, “-”, “/”)If you put that formula in cell A2, then set cell A1 to 1, 2, or 3 you’ll see the value update. Change that value fast enough, and it looks like it’s animated. A small script attached to the sheet helps drive the animation.
function animate() {
var ss = SpreadsheetApp.getActiveSheet();
var range = ss.getRange(1, 1);
var maxFrame = 4;
for (var i = 0; i < 100; ++i) {
var frame = (i % maxFrame) + 1;
range.setValue(frame);
SpreadsheetApp.flush();
Utilities.sleep(100);
}
}Run the script and voila! Not an animated GIF, but a proof of concept.
For images we need colors, not text. Enter conditional formatting. You can try it for yourself by setting up some conditional formatting rules for the 4 values.
Run the script again and now the cell color changes for each frame.
Now we just need to go from 1 pixel to lots of pixels!
Encoding an image like this by hand would be tedious, but with the Sheets API, it’s not so bad. There’s a small Node.js script I created at https://github.com/sqrrrl/animated-sheet/formula.js that does this work for us. It reads a GIF and for each pixel, encodes the colors for each frame. It also does some resizing of the sheet and cells to make things look a little nicer.
The resulting sheet looks like:
Each pixel the decimal value of the cell color (in hex, 0xRRGGBB) for the corresponding frame.
=CHOOSE($A$1, 16380939, 16380939, 16378891, 16313354, 11859516, 11722536, 11722536, 7863319, 3669396, 6091876, 8977160, 8977160, 10025223)It also creates a conditional formatting rule for each possible color in the image that matches on the color value.
Here’s where things started heading the wrong way. Each update to the frame index took about 15 seconds to recalculate and render the sheet. While it technically worked, it was absurdly slow and not the result I was looking for.
As it turns out, too many conditional formatting rules make things slow.
Very slow.
The particular GIF I used had over 500 colors across all the frames, resulting in over 500 conditional formatting rules. Just a wee bit too many for this to work.
Quantizing the image to reduce the color palette helped. After a little experimentation, 16 colors turned out to be a good balance between aesthetics and performance. Still janky as hell, but as close to good as I was going to get with this approach.
Simple is better
As fun as it was getting the GIF to work, I certainly wasn’t ready to get back to my “real” work. So I decided that v2 should follow an approach I originally thought about and discarded — let’s just rewrite the image in full for each frame via the API.
You can find this version in https://github.com/sqrrrl/animated-sheet/frames.js. It’s mostly the same as previous version, but instead of using Apps Script to drive the animation, it’s handled completely in the script and the image is updated via the API for each frame.
This turns out to be much simpler, doesn’t require quantizing, and animates much more smoothly. It’s still janky, but a notable improvement over v1. And certainly good enough to call it quits on this little venture and get back to more serious (ha!) pursuits.
Real world implications
As effective as this experiment was at wasting time, it wasn’t without value. As mentioned earlier, the original version was a useful exercise for learning the Sheets API and trying out different features.
Attempting to do animations revealed the performance implications of conditional formatting rules. It turns out that approaching just a dozen or so rules can dramatically slow down recalculations on a moderately complex sheet. Hopefully the Sheets team will continue to make improvements here. (Don’t worry — I already filed the bug.)
More so, there are countless real world cases that require pushing data to sheets on a frequent basis. What were pixels for me may be sales data, sensor readings, or whatever data you need to keep up-to-date in your sheets.
And lastly, it was a much needed reminder of the KISS principle. 😙
