Inserting Animated GIFs over Cells on Google Sheets using Google Apps Script

submit.md

Inserting Animated GIFs over Cells on Google Sheets using Google Apps Script

Overview

This script demonstrates how to insert an animated GIF over cells in a Google Sheet using Google Apps Script.

Description

I recently received a request to create a Google Apps Script for inserting animated GIFs into cells on a Google Sheet. I previously published a sample script on my blog on June 6, 2017. Ref In that script, the animation GIF was inserted using a public link. This new script leverages data URLs, which simplifies the process for using GIFs stored in Google Drive. Since this approach might be helpful to others, I'm sharing it here.

Usage

1. Prepare animation GIF

Please prepare your sample animation GIF on your Google Drive, and confirm the file ID of the GIF file.

2. Prepare Google Spreadsheet

Please create a new Google Spreadsheet and open the script editor.

3. Sample script

function myFunction() {
  const fileId = "###"; // Please set the file ID of the GIF image file on your Google Drive.

  // Prepare image.
  const file = DriveApp.getFileById(fileId);
  const blob = file.getBlob();
  const dataUrl = `data:${file.getMimeType()};base64,${Utilities.base64Encode(
    blob.getBytes()
  )}`;

  // Insert image over cells.
  SpreadsheetApp.getActiveSheet().insertImage(dataUrl, 2, 2);

  // SpreadsheetApp.getActiveSheet().insertImage(blob, 2, 2); // In this case, an error like "Exception: The blob format is unsupported." occurs.
}

When this script is run, the following result is obtained. You can see that an animation GIF is inserted as the anchor of the cell "B2" of the active sheet.

IMPORTANT

When the script is modified to use blob instead of dataUrl for inserting images as from SpreadsheetApp.getActiveSheet().insertImage(dataUrl, 2, 2); to SpreadsheetApp.getActiveSheet().insertImage(blob, 2, 2);, an error like "Exception: The blob format is unsupported." occurs. While PNG and JPEG images inserted with Blob data work fine, this suggests that animation GIFs cannot be directly inserted into Google Spreadsheets using blobs. In this case, using a public link or data URL to insert the animation GIF seems to work.

Additional information: Inserting an animation GIF into a cell results in a static image; the animation doesn't play. This situation has not still changed since 2017.