Google Sheets Animation

Kieran Dixon

Updated: June 30, 2021

Can you use Google Sheets to create an animation?

Yes:

shows a working nyan cat animation created and playing in google sheets

Here's how:

Create a sheet for every frame of your animation.

Think of each cell in a sheet as a pixel in an image and change the background color of the cells to represent your frames:

shows how many sheets function as frames to create an animation in google sheets

Make sure all of your frame sheets are the same size and are in the order of the animation from left to right.

Create a main sheet called Animation that's the same size as the frame sheets.

Copy this code into the Script Editor and refresh your sheet:

Dark theme

Copy code

function onOpen() {

  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Animation')
    .addItem('Start', 'animate')
    .addToUi();

}

function animate() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const animationSheet = ss.getSheetByName('Animation');
  const sheets = ss.getSheets();
  const frames = [];

  sheets.forEach(sheet => {

    if (sheet.getName() === 'Animation') return;

    const frame = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).getBackgrounds();
    frames.push(frame);

  });

  for (let i = 0; i < 2; i++) {

    frames.forEach(frame => {

      animationSheet
        .getRange(1, 1, frame.length, frame[0].length)
        .setBackgrounds(frame);

      SpreadsheetApp.flush();

    });

  }

}

This code creates a custom Animation main menu item that you can use to trigger your Animation sheet to… be animated.

The animate() function:

Collects the background color data from the frame sheets and stores it
Loops over the frame data setting each frame as the background of the Animation sheet before SpreadsheetApp.flush(); forces the sheet to display the new background to the user.
This frame data loop happens twice so the animation lasts a little longer than one run through.

Without SpreadsheetApp.flush(); the user would only see the final frame because Apps Script would ignore the interim changes to make the script more efficient.

But efficiency isn't what we're after here.

This is a short animation and it runs quite well (although a little slow).

Making longer animations that require more color data will probably cause things to slow down even more and eventually not work.

With a really long animation you might run into the script runtime limit of 6 minutes / execution.

If your animation lasts for one or five minutes you could set up a time-based trigger to make it execute over and over making your animation loop.

However, the triggers are imprecise, would fail occasionally, and there's also a triggers total runtime limit of 90 minutes / day (for consumer accounts e.g. gmail.com) and 6 hours / day (for Google Workspace accounts).

If you're interested, here are all of the Google Workspace quotas.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.