Inserting images into a Sheet as BlobSource using Google Apps Script

I've been having issues with inserting images from google drive into a google sheet using google scripts. I can get it to work perfectly when loading from url, but not from the google drive file as a blob. The script verifies the size as 88KB, but then throws an error saying the file is >2MB when the insert function is run.

The 88KB file loads nicely from URL however.

Can it be that the blob adds over 2MB of data around an 88KB file? Maybe I'm not quite understanding how to use blobs?

function InsertImageTest() {

  // this is a file with a single sheet with a png image over the grid, starting at cell A1
  var ReportSpeadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/the-file-id/edit#gid=0")

  var ReportSheet = ReportSpeadsheet.getSheetByName("Sheet1");

  var File1 = DriveApp.getFileById("fileId1"); // this is a 763byte png image
  var File2 = DriveApp.getFileById("fileId2"); // this is a 88KB png image


  Logger.log("File1=" + File1.getName()); // returns correct name for file
  Logger.log("File2" + File2.getName()); // returns correct name for file
  Logger.log("File1=" + File1.getSize()); //File1=763
  Logger.log("File2=" + File2.getSize()); //File2=89678

  try { ReportSheet.insertImage(File1, 1, 1); } catch (err) {
    Logger.log("File1 failed  " + err); //Works
  } 

  try { ReportSheet.insertImage(File2, 1, 2); } catch (err) {
    Logger.log("File2 failed  " + err); //Exception: The blob was too large. The maximum blob size is 2MB. The maximum number of pixels is 1 million.
  } 
// Also tried File2.getAs('image/png') and File2.getBlob() as the blob argument, same error message.

// testing the 88KB file uploaded to a URL on my website.
  ReportSheet.insertImage("www.theURL.com/the88KBfile.png", 1, 3); // works fine


}

Modification points:

• I thought that in your situation, the image size might be due to the reason of your issue rather than the file size. The limitation of the image size for insertImage() can be seen at SpreadsheetApp.insertImage server error In the current stage, it seems that the maximum image size for insertImage() is 1,048,576 pixels^2.
• In order to avoid your issue, I would like to propose to resize the image.

When your script is modified, it becomes as follows.

Modified script:

This sample script uses Drive API for retrieving the image size. So before you use this script, please enable Drive API at Advanced Google services. And, please set your file IDs of image files.

function InsertImageTest() {
  var ReportSpeadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/the-file-id/edit#gid=0");
  var ReportSheet = ReportSpeadsheet.getSheetByName("Sheet1");

  // --- I modified below script.
  var fileIds = ["fileId1", "fileId2"];  // <--- Please set the file IDs of the image files.
  fileIds.forEach((id, i) => {
    var obj = Drive.Files.get(id);
    var image = obj.imageMediaMetadata && (obj.imageMediaMetadata.width * obj.imageMediaMetadata.height) > 1048576 ? obj.thumbnailLink.replace(/=s\d+/, "=s500") : DriveApp.getFileById(id).getBlob();
    ReportSheet.insertImage(image, 1, i + 1);
  });
}

• In this sample script, when the image size is over 1,048,576 pixels^2, the image is reduced and the URL of reduced image is returned. When the image size is less than 1,048,576 pixels^2, the blob of the image is returned.
• The resized image has 500 pixel in the width. When you want to change this, please modify =s500. But, when the image size is over 1,048,576 pixels^2, an error occurs. Please be careful this.

References:

• Related thread and gists.
  • SpreadsheetApp.insertImage server error
  • Limitations for Inserting Images to Google Docs
• insertImage(blobSource, column, row)
• insertImage(url, column, row)

Updated on November 6, 2024:

From Lle.4's comment, when I posted this answer, when Drive API was enabled at Advanced Google services, v2 was used because of no v3. In that case, all metadata are returned. By this, the above script worked.

But, in the current stage, when Drive API is enabled, v3 is automatically used. In this case, in order to retrieve the metadata imageMediaMetadata, it is required to use the fields property. Because Drive API v3 doesn't return all metadata as the default. When you use Drive API v3, please use the property of fields as follows.

var obj = Drive.Files.get(id, {fields: "imageMediaMetadata"});

or

var obj = Drive.Files.get(id, {fields: "*"});