google-apps-scriptgoogle-sheetsgoogle-drive-api

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


}

Solution

  • Modification points:

    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);
      });
    }
    

    References:

    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: "*"});