Embedding Animation GIF in A Cell on Spreadsheet

submit.md

Embedding Animation GIF in A Cell on Spreadsheet

This sample script is for embedding animation GIF in a cell using custom function on Spreadsheet.

I think that this method is one of various ideas.

Problem

There are some limitations.

1. Images of jpeg and png can be embedded in a cell using =IMAGE(). But when animation GIF is embedded using it, GIF is not played.
2. insertImage() can insert the animation GIF to sheet. But it is not imported to one cell. It floats on several cells.
3. In order to float the animation GIF on one cell, the size of GIF has to be retrieved. But the size of image cannot be retrieved at spreadsheet APIs.
4. =IMAGE() and insertImage() cannot be used by custom functions.

Solution

I thought a method to floating an animation GIF on one cell using insertImage(). By this, I thought that it will be easy to use as a sheet with GIF images.

In order to retrieve the size of image, it uses Google Document APIs. Class InlineImage of Document APIs has getHeight() and getWidth(). By this, the image size can be retrieved, and the size of cell can be changed using this. Although I have tried to resize image using setHeight() and setWidth(), when the image resized using them is retrieved, the size was original one.

insertImage() can be used by Web Apps Web Apps. From previous research, it has been found that Web Apps can avoid various limitations. Also in the case of this situation, Web Apps could avoid the above limitations.

To use this sample script, please deploy Web Apps as follows.

On the Script Editor,

• File
  • -> Manage Versions
  • -> Save New Version
• Publish
  • -> Deploy as Web App
  • -> At Execute the app as, select "your account"
  • -> At Who has access to the app, select "Anyone, even anonymous"
  • -> Click "Deploy"
  • -> Copy "Current web app URL"
  • -> Click "OK"

When it deploys Web Apps, the approval required authorization can be done, simultaneously.

Sample Script :

Please copy and paste this script to a bound script of spreadsheet.

function gif(filename) {
  var ac = SpreadsheetApp.getActiveSheet().getActiveCell();
  var q1 = "?file=" + filename;
  var q2 = "&row=" + ac.getRow();
  var q3 = "&col=" + ac.getColumn();
  var url = ScriptApp.getService().getUrl() + q1 + q2 + q3;
  UrlFetchApp.fetch(url);
}

function doGet(e) {
  var srcfile = DriveApp.getFilesByName(e.parameters.file).next();
  srcfile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  var fileid = Drive.Files.insert({
      title: "temp",
      mimeType: "application/vnd.google-apps.document",
      parents:  [{"id": srcfile.getParents().next().getId()}]
  }).getId();
  var img = DocumentApp.openById(fileid)
            .insertImage(
              0,
              UrlFetchApp.fetch(Drive.Files.get(srcfile.getId())
              .webContentLink)
              .getBlob()
            );
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.setRowHeight(e.parameters.row, img.getHeight() + 4);
  sheet.setColumnWidth(e.parameters.col, img.getWidth() + 3);
  sheet.getRange(e.parameters.row, e.parameters.col).setFormula("");
  sheet.insertImage(Drive.Files.get(srcfile.getId()).webContentLink, e.parameters.row, e.parameters.col);
  Utilities.sleep(1000);
  Drive.Files.remove(fileid);
}

Flow of Script :

gif()

1. Input =gif("sample.gif") in cell B2. "sample.gif" is an animation GIF.
2. Using fetch(), sends data of "sample.gif" and the inputted coordinate to doGet().

doGet()

1. Using doGet(), get the data.
2. Update the permission of GIF.
3. Create Document file as new file.
4. Import GIF to the Document and retrieve the image size of GIF.
5. Using the size, change the size of cell for importing GIF.
6. Import GIF to the cell.
7. Delete Document file.

Result :

By inputting =gif("sample.png") in cell B2 as a custom function, following result can be obtained.

Although this demo movie seems short loading time, this loading time is edited. Actual loading time is about 40 seconds.

Note :

1. When the custom function gif() is used, loading time is about 40 seconds. (I don't know whether this occurs only my environment.)
2. Permissions of GIF are ANYONE_WITH_LINK, VIEW.

Related Tips

• OCR using Custom Function on Spreadsheet
• Embedding a Chart to a Cell using Custom Function on Spreadsheet
• Embedding a Map to a Cell using Custom Function on Spreadsheet

hi Tanaikech, thanks for the code!
I'm trying to employ it but no results :( The gifs are still static.
Am I missing a step or sth else? Is there a step that we should do that maybe you don't mention in your guide?