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:See the original image hereEach 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.
Hop hop hop…Simple is betterAs 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.
Keep on hopping…Real world implicationsAs 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.
????.