How display images from Google Drive on Gsheet cell?

Question

I would like to display easily an image on Gsheet cell based from URL of image stored in Gdrive.

I have tried with the Gsheet function =IMAGE("URL") and it does not work.

The objective is to display an image picture as the example below (example of picture in 5th row was done manually)

Answer 1

• You want to put the image in your Google Drive to your Google Spreadsheet.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Pattern 1:

In this pattern, the image is put using =IMAGE("URL").

When =IMAGE("URL") is used, the image is required to publicly shared. So please share the images with publicly shared as On - Anyone with the link.

And also, please modify the endpoint as follows.

From:

https://drive.google.com/open?id=###

to:

https://drive.google.com/uc?export=download&id=###

• In this case, you can put the image with =IMAGE("https://drive.google.com/uc?export=download&id=###") after the image is shared publicly.

Pattern 2:

If you don't want to share publicly the images, how about this pattern? In this pattern, the image is put as the blob without sharing publicly.

Here, please check the following sample script.

Sample script:

var fileId = "###";  // Please set the file ID of the image.

var sheet = SpreadsheetApp.getActiveSheet();
var blobSource = DriveApp.getFileById(fileId).getBlob();
var image = sheet.insertImage(blobSource, 1, 1);
image.setWidth(100).setHeight(100);
sheet.setColumnWidth(1, 100).setRowHeight(1, 100);

• When you run the script, the image is put to the cell "A1". And the image size is resized to 100 x 100 pixels. And then, the row and column size is changed for the image size.
• This is a simple sample script. So please modify this for your actual situation.

References:

• insertImage()
• Class OverGridImage
• setColumnWidth()
• setRowHeight()

If I misunderstood your question and this was not the direction you want, I apologize.

Added:

From your replying, it was found that the image size is over than the limitation size (1,048,576 pixels^2) Ref The reason of your current is is this.

In this case, in order to put the image, it is required to resize the image. The following sample script puts the image by resizing image size. For this, I used a Google Apps Script library. So please install it to the script editor.

Sample script:

var fileId = "###";  // Please set the file ID of the image.

var sheet = SpreadsheetApp.getActiveSheet();
var blobSource = DriveApp.getFileById(fileId).getBlob();
var obj = ImgApp.getSize(blobSource);
var height = obj.height;
var width = obj.width;
if (height * width > 1048576) {
  var r = ImgApp.doResize(fileId, 512);
  blobSource = r.blob;
}
var image = sheet.insertImage(blobSource, 1, 1);
image.setWidth(100).setHeight(100);
sheet.setColumnWidth(1, 100).setRowHeight(1, 100);

• In this sample script, when the image size is over than 1,048,576 pixels^2, the image is resized and put to the Spreadsheet.
• This is a simple sample script. So please modify it for your actual situation.

References:

• Limitations for Inserting Images to Google Docs
• ImgApp

Answer 2

=image("https://drive.google.com/uc?export=download&id="&right(A2;33);1). 

This works for me