Inserting Image Using Google Script

I am trying to insert an image that is stored on my google drive that I want to insert into my google sheet, using google script Bearing in mind that i have a a whole load of imag

Solution 1:

How about this sample? In this sample, an image is inserted to the active sheet using the filename of image file. You can see the detail information of insertImage() at https://developers.google.com/apps-script/reference/spreadsheet/sheet#insertImage(Blob,Integer,Integer).

Sample script 1

SpreadsheetApp.getActiveSheet().insertImage(
  DriveApp.getFilesByName(filename).next().getBlob(),
  1,
  1
);

Sample script 2

If you want to insert all files in own Google Drive, you can use following script. But if image files are in large number, errors may occur.

var ss = SpreadsheetApp.getActiveSheet();
var files = DriveApp.getFiles();
var ar = [];
var i = 1;
while (files.hasNext()) {
  var file = files.next();
  if (~file.getMimeType().indexOf("image")) {
    ss.insertImage(DriveApp.getFileById(file.getId()).getBlob(), i++, 1);
  }
}

Sample script 3

insertImage() can use the mimyTypes of png, bmp, gif and jpeg. I think that this is corresponding to getAs(). Images with the mimeType except for them cannot be used directly. So such images have to convert the mimeTypes. But since Google Apps Script doesn't have the prepared converter for it, outer API has to be used for this situation before. Recently, I noticed that thumbnailLink of Drive API can be used for this situation by changing the query parameter. You can see the detail information here.

About the sample script, I used this. This script inserts images in the specific folder. You can use the mimeTypes of png, bmp, gif and jpeg and also other mimeTypes. Although I thought high efficiency by separating "png, bmp, gif and jpeg" and images except for them, when the number of files is large, errors may occur.

varfolderId="### folder ID ###";
varss= SpreadsheetApp.getActiveSheet();
varfiles= DriveApp.getFolderById(folderId).getFiles();
varar= [];
vari=1;
while (files.hasNext()) {
  varfile= files.next();
  if (file.getMimeType() == "image/png" || file.getMimeType() == "image/bmp" || file.getMimeType() == "image/gif" || file.getMimeType() == "image/jpeg" || file.getMimeType() == "image/jpg") {
    ss.insertImage(DriveApp.getFileById(file.getId()).getBlob(), i++, 1);
  } elseif (~file.getMimeType().indexOf("image")) {
    varres= JSON.parse(UrlFetchApp.fetch("https://www.googleapis.com/drive/v3/files/" + file.getId() + "?fields=thumbnailLink", {
      method: "GET",
      headers: {
        "Authorization": "Bearer " + ScriptApp.getOAuthToken()
      },
      muteHttpExceptions: true
    }).getContentText()).thumbnailLink;
    varr= res.split("=");
    varurl= res.replace(r[r.length - 1], "s5000");
    ss.insertImage(UrlFetchApp.fetch(url, {
      headers: {
        Authorization: "Bearer " + ScriptApp.getOAuthToken()
      }
    }).getBlob(), i++, 1);
  }
}

If I misunderstand your question, I'm sorry.