Inserting Image using Google Script

Question

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 images stored in my drive that i don't really want to publish to the web, but do want an automated way of doing it.

I know this question was asked about 2/3 years ago and google didn't have a method of doing it, there were workarounds like export links which have now been detracted.

insertimage() comes to mind but it doesn't seem to work for me.

This is the code I am using to get PDF and Tiff in, it is basically the code by Tainik

function insertDrawing(folderID, file_name) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var file = DriveApp.getFolderById(folderID).searchFiles('title contains "' + file_name + '"').next();
if (!~file.getMimeType().indexOf("pdf") || !~file.getMimeType().indexOf("tiff") || file == 'undefined') {
    ss.toast('No image found');
    return
} 
var res = (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);
var r = res.split("=");
Logger.log(res)
var url = res.replace(r[r.length - 1], "s5000");
Logger.log([res, url])
return UrlFetchApp.fetch(url, {
    headers: {
        Authorization: "Bearer " + ScriptApp.getOAuthToken()
    }
}).getBlob()
}

I don't know whether I understand what you want to do. But in the case of if (!~file.getMimeType().indexOf("pdf") || !~file.getMimeType().indexOf("tiff") || file == 'undefined') {, ss.toast() in if is run for all files included pdf and tiff. When you want to use only pdf and tiff, please modify from if (!~file.getMimeType().indexOf("pdf") || !~file.getMimeType().indexOf("tiff") || file == 'undefined') { to if (!~file.getMimeType().indexOf("pdf") && !~file.getMimeType().indexOf("tiff") || file == 'undefined') {. — Tanaike
And when PDF is used, please set the query parameter to less than s1100. When s is large, an error occurs. — Tanaike
When you want to add and modify your question, please update your original question. — Tanaike
Thanks for your comments. Sorry my mistake I was adding it as an answer. I think the syntax is correct there I only want it to put the image in if the file is either a PDF or TIFF and if it isn't to come up with file not found. May i ask where you are getting these resolution limits from. I cannot seem to find documentation that even mentions what "s5000" "s1200" means — mfaiz
The resolution using query s is different between mimeType. I have reported about it at github.com/tanaikech/ImgApp#doresize_principle Please check it. — Tanaike

Tanaike Tanaike · Accepted Answer · 2017-07-14T08:04:21

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.

var folderId = "### folder ID ###";
var ss = SpreadsheetApp.getActiveSheet();
var files = DriveApp.getFolderById(folderId).getFiles();
var ar = [];
var i = 1;
while (files.hasNext()) {
  var file = 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);
  } else if (~file.getMimeType().indexOf("image")) {
    var res = 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;
    var r = res.split("=");
    var url = 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.

Inserting Image using Google Script

1 Answers

Sample script 1

Sample script 2

Sample script 3