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
}
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.When your script is modified, it becomes as follows.
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);
});
}
=s500
. But, when the image size is over 1,048,576 pixels^2, an error occurs. Please be careful this.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: "*"});