2

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


}
Tanaike
  • 181,128
  • 11
  • 97
  • 165
RowanD
  • 109
  • 1
  • 7

2 Answers2

2

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • This seems to work perfectly, thank you very much. I had come up with a hack solution, which I'll post below, but this is much neater. How do I mark this as the solution? – RowanD Mar 26 '21 at 08:53
  • Marked as solution. – RowanD Mar 26 '21 at 09:07
  • @RowanD Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. – Tanaike Mar 26 '21 at 09:09
0

Having established that the url method worked, I came up with this workaround to my own problem, it's not as neat as Tanaike's solution though.

You can access the file by url using "https://docs.google.com/uc?id=" + fileId2

But the file has to viewable to 'anyone with link' for the url method to work on a Google Drive file, so I temporarily changed the file permissions:

File.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
ReportSheet.insertImage("https://docs.google.com/uc?id=" + fileId2, 1, 4);
File.setSharing(DriveApp.Access.DOMAIN_WITH_LINK, DriveApp.Permission.VIEW);

I'll be adopting Tanaike's solution.

RowanD
  • 109
  • 1
  • 7