Report: Management of Images on Google Spreadsheet using Google Apps Script

report / GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a report for management of images on Google Spreadsheet using Google Apps Script.

At October 30, 2018, Cass OverGridImage and the method of inserImage have been added to Spreadsheet Service. Ref At January 19, 2022, Class CellImageBuilder and Class CellImage have been added to Spreadsheet Service. Ref By these Classes and methods, the images got to be able to be managed on Google Spreadsheet. But, when the image is used to the various situations, there are the cases that it is required to ingenuity to manage the images. So, in this report, I would like to introduce the management of images on Google Spreadsheet using the sample scripts of Google Apps Script.

In this report, I would like to introduce the follwoing 4 sample situations.

1. Put an image retrieved from Google Drive over the cells.

In this sample, an image on Google Drive is put over the cells.

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

  // Prepare image.
  const blob = DriveApp.getFileById(imageFileId).getBlob();

  // Put image over cells.
  SpreadsheetApp.getActiveSheet().insertImage(blob, 2, 2);
}

When this script is run, an image of Google Drive is put over the cells (anchour cell is “B2”) of the active sheet as follows.

Note

  • Of course, when you have already had the direct link of the image, you can directly use the URL with SpreadsheetApp.getActiveSheet().insertImage(url, 2, 2);. This sample script is for using an image on Google Drive.

References

2. Put an image retrieved from Google Drive into a cell.

In this sample, an image on Google Drive is put into a cell. In this case, Drive API is used for retrieving the image URL. So, please enable Drive API at Advanced Google services. Ref

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

  // Prepare image.
  const url = Drive.Files.get(imageFileId).thumbnailLink.replace(/\=s.+/, "=s512"); // When =s512 is changed to =s1000, the width of the image becomes 1000 pixels.
  const image = SpreadsheetApp.newCellImage().setSourceUrl(url).build();

  // Put image over cells.
  SpreadsheetApp.getActiveSheet().getRange("B2").setValue(image);
}

When this script is run, an image of Google Drive is put into a cell “B2” of the active sheet as follows.

Updated: November 8, 2023

Recently, it was confirmed that the thumbnail link retrieved by Drive API cannot be directly used as the source URL of CellImageBuilder. By this, the above script cannot be used. I’m not sure whether this is the temporal situation. But, I found a question on Stackoverflow related to this. Ref So, as another approach instead of the use of the direct link of the image, I added the sample scripts.

In this case, the image is put into a cell using the data URL instead of the public link.

This script puts directly the image blob from imageFileId.

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

  // Prepare image.
  const file = DriveApp.getFileById(imageFileId);
  const base64 = Utilities.base64Encode(file.getBlob().getBytes());
  const image = SpreadsheetApp.newCellImage().setSourceUrl(`data:${file.getMimeType()};base64,${base64}`).build();

  // Put image over cells.
  SpreadsheetApp.getActiveSheet().getRange("B2").setValue(image);
}

This script puts the image blob from the thumbnail link from imageFileId. If your image size is large, this pattern might be useful.

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

  // Prepare image.
  const url = `https://drive.google.com/thumbnail?sz=w512&id=${imageFileId}`; // Ref: https://stackoverflow.com/a/31504086
  const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
  const base64 = Utilities.base64Encode(blob.getBytes());
  const image = SpreadsheetApp.newCellImage().setSourceUrl(`data:image/png;base64,${base64}`).build();

  // Put image over cells.
  SpreadsheetApp.getActiveSheet().getRange("B2").setValue(image);

  // DriveApp.getFiles(); // This is used for automatically detecting a scope of "https://www.googleapis.com/auth/drive.readonly" for the access token.
}

When this script is run, an image of Google Drive is put into a cell “B2” of the active sheet as follows.

Note

  • Of course, when you have already had the direct link of the image, you can directly use the URL with const image = SpreadsheetApp.newCellImage().setSourceUrl(url).build();. This sample script is for using an image on Google Drive.

References

3. Get an image of over the cells as a blob.

In this sample, it supposes that an sample image is put over the cells. Please prepare it, before you run this script.

In this sample, an image put over the cells is retrieved as a blob. Unfortunately, in the current stage, there are no methods for retrieving the image put over the cells using the built-in methods of Google Apps Script. So in this sample script, a Google Apps Script library of DocsServiceApp is used. I created this library to compensate for the processes that Google services cannot achieve.

First, please intstall a Google Apps Script library of DocsServiceApp to the script editor. You can see how to install it at here. And, please enable Drive API at Advanced Google services. Ref

function myFunction() {
  // Retrieve image position as a1Notation.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const images = sheet.getImages();
  if (images.length == 0) {
    throw new Error("Please put an image over the cells.");
  }
  const cell = images[0].getAnchorCell().getA1Notation(); // Anchour cell of an image put over the cell.

  // Retrieve image.
  const res = DocsServiceApp.openBySpreadsheetId(ss.getId())
    .getSheetByName(sheet.getSheetName())
    .getImages();
  const obj = res.find(
    ({ range: { a1Notation }, image: { innerCell } }) =>
      a1Notation == cell && !innerCell
  );
  if (!obj) {
    throw new Error("Image was not found.");
  }

  // Create the retrieved blob as a image file on the root folder.
  DriveApp.createFile(obj.image.blob);
}

When this script is run, an image is retrieved from the active sheet, and it is created as a PNG image file on the root folder.

References

4. Get an image in a cell as a blob.

In this sample, it supposes that an sample image is put into a cell “B2”. Please prepare it, before you run this script.

In this sample, an image put in a cell is retrieved as a blob. Unfortunately, in the current stage, there are no methods for retrieving the image put in a cell using the built-in methods of Google Apps Script. So in this sample script, a Google Apps Script library of DocsServiceApp is used. I created this library to compensate for the processes that Google services cannot achieve.

First, please intstall a Google Apps Script library of DocsServiceApp to the script editor. You can see how to install it at here. And, please enable Drive API at Advanced Google services. Ref

function myFunction() {
  // Retrieve image position as a1Notation.
  const cell = "B2"; // In this sample, it supposes that an image is put into a cell "B2".

  // Retrieve image.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const res = DocsServiceApp.openBySpreadsheetId(ss.getId())
    .getSheetByName(sheet.getSheetName())
    .getImages();
  const obj = res.find(
    ({ range: { a1Notation }, image: { innerCell } }) =>
      a1Notation == cell && innerCell
  );
  if (!obj) {
    throw new Error("Image was not found.");
  }

  // Create the retrieved blob as a image file on the root folder.
  DriveApp.createFile(obj.image.blob);
}

When this script is run, an image is retrieved from a cell “B2” of the active sheet, and it is created as a PNG image file on the root folder.

References

 Share!