Insert an image into Google Spresheet Cell from Drive using Google Apps Script

Question

I've made my way through all threads about this topic. From blob to URLs and more, to insert an image into a cell via apps-script but nothing seems to work. And most of the posts haven't gotten the solution laid out well enough to traceback the errors. So I'm trying to get an answer to my problem here. Below my code. I would also love to discuss the advantages of different methods. Blob seems to be one of the easiest when working with images already existing in G-Drive as far as I see.

function getGDriveFilesIntoCell() {

  var dApp = DriveApp;  // Store GDrive App in a function
  var folderIter = dApp.getFoldersByName("Training_folder");  // Get folder by name
  var foundfolder = folderIter.next();  // next allows to Iterate through the found folders
  Logger.log(foundfolder);  // Print into the logs the found folder of foundfolder

  var ss = SpreadsheetApp.getActiveSpreadsheet(); //.getActiveSheet();  // Calls the Spreadsheet App and the current active Spreadsheet
  var sheet = ss.getSheets()[0];  // Get first sheet

  var filesIter = foundfolder.getFiles();  // Get files in found folder

  var i = 1;  // Define a var with value 1

  while(filesIter.hasNext()){  // While loop for all files in folder found by name
    var file = filesIter.next();
    var filename = file.getName();  // Get Name
    var filesize = file.getSize() / 1024;  // Get size in kb
    var file_id = file.getId();  // Get ID
    var file_url = file.getUrl();  // Get Url to file
    sheet.insertImage(file.getBlob(), i, 1);  // Insert Image
    Logger.log(filename + filesize + file_id);
    Logger.log(filesize);
    Logger.log(file_id);
    i++;  // increment i by one

    // file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW)  // Set file permission

    // var file_blob = file.getBlob();  // Get blob

    // Insert Image via custom bblob
    // var file_mime_type = file.getMimeType();  // Get Mime Type
    // var response = UrlFetchApp.fetch(file_url);
    // var binaryData = response.getContent();
    // var blob = Utilities.newBlob(binaryData, file_mime_type, filename);
    // sheet.insertImage(blob, i, 1);

    // Inser image via Link
    // ss.getRange(i, 1).setValue("=image(\"https://drive.google.com/uc?export=view&id=" + file.getId() +"\")");  // Adds an Image via Link
    // var img = Drive.Files.get(file.getId()).webContentLink;
  }

}

Answer 1

I believe your goal as follows.

• You want to put the images to Google Spreadsheet using insertImage with the blob.
• You want to retrieve the images from the specific folder.

For this, how about this answer?

Modification points:

• When the image is put to the Spreadsheet, please be careful the following points.
  1. In your script, when the files except for the image are included in the folder of Training_folder, an error occurs.
  2. When the size of images are over the maximum limitation, an error occurs. Ref

I thought that these might be the reason of your issue. So I would like to propose the following modification.

Modified script:

When your script is modified, please modify as follows. In this modification, "ImgApp" which is Google Apps Script library is used. So before you run the script, please install the GAS library. You can see how to install it at here.

From:

while(filesIter.hasNext()){  // While loop for all files in folder found by name
  var file = filesIter.next();
  var filename = file.getName();  // Get Name
  var filesize = file.getSize() / 1024;  // Get size in kb
  var file_id = file.getId();  // Get ID
  var file_url = file.getUrl();  // Get Url to file
  sheet.insertImage(file.getBlob(), i, 1);  // Insert Image
  Logger.log(filename + filesize + file_id);
  Logger.log(filesize);
  Logger.log(file_id);
  i++;  // increment i by one
}

To:

while(filesIter.hasNext()){
  var file = filesIter.next();
  if (file.getMimeType().indexOf("image") != -1) {
    var blob = file.getBlob();
    var size = ImgApp.getSize(blob);
    if (size.width * size.height > 1048576) {
      var resized = ImgApp.doResize(file.getId(), 512);
      blob = resized.blob;
    }
    sheet.insertImage(blob, i, 1);  // Insert Image
    i++;  // increment i by one
  }
}

• In this modified script, the image files are retrieved. And, when the image size is over the maximum size for Spreadsheet, the image is resized.

Note:

• From your script, you might use the script without V8. So I modified for it.

References:

• SpreadsheetApp.insertImage server error
  • In this thread, the limitation of image for putting to Spreadsheet is discussed.
• Limitations for Inserting Images to Google Docs
• ImgApp
  • This GAS library can be retrieve from the blob of image, and the image can be resized.