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

Answer 3

As @nguyen-hieu suggested, for what you want to do in the spreadsheet you are showing, his/her answer is a good solution

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

---

More Advanced Option

When you want to query an image based on a constantly changing cell content, like a data validation dropdown menu list, use this code

=IMAGE( CONCATENATE("https://drive.google.com/uc?export=download&id=", QUERY(Images, "SELECT B WHERE A = '" & Item & "'", 0)) , 1)

Dissection of the Formulas

Sheet: Images

In this sheet you want to keep all your images (best if you only use the URL ID)

|  A     |   B   |
--------------
|Item    |ImageID|
--------------
|Hammers |XYZABC |
--------------
|Chairs  |ABCXYZ |
--------------
|Wood    |ABXXYA |

---

Main Sheet

Let's say you have a main sheet (ej. Summary) with a drop down menu (data validation list) in $A$2 of the full list of items from the sheet =Images!$A:$A

1. To make coding much easier, create the following Named Ranges:

• Item: =Summary!$A$2
• Items: (=Images!$A:$B)

2. On the cell you to display to display the image use this code

=IMAGE( CONCAT("https://drive.google.com/uc?export=download&id=", QUERY(Images, "SELECT B WHERE A = '" & Item & "'", 0)) , 1)

=Image

• The formula Image needs a URL and the format needs to be "https://drive.google.com/uc?export=download&id=GoogleDriveImageID

• The number 1 specifies the image to auto-resize to take the whole width of the cell. Keeping the aspect ratio. If you want to use the height of the row instead, replace it for the number 2

=CONCAT

• To give the formula =IMAGE a URL that it can use, CONCAT puts together the string "https://drive.google.com/uc?export=download&id= and the image ID from the sheet Images.

As an alrernative, you can add this CONCAT formula in your Images sheet, on Column C, CONCAT( "https://drive.google.com/uc?export=download&id=", $B:$RowNumber ) for every entry (replace RowNumber for the actual row number of the entry) and then update your IMAGE formula to this:

=IMAGE( QUERY(Images, "SELECT C WHERE A = '" & Item & "'", 0)) , 1)

Removing CONCAT and replacing the column B for the column C in the SELECT statement. ALSO update the named range Images to Images!A:C

=QUERY

This function is the "workhorse of the show". QUERY will get the image ID based on the value of the cell (in this example, Summary!$A:$2 or Image named range).

QUERY(Images, "SELECT B WHERE A = '" & Item & "'", 0)

• The named range Images replaces Images!$A:$B, and gives you the data you need based on the query "SELECT B WHERE A = 'Item'" The Item portion of the WHERE query is the named range Item (Summary!$A:$2).

• Finally, the number 0 after the comma, at the end of the select query statement QUERY(Images, "SELECT B WHERE A = '" & Item & "'", 0) is required to remove the headers from the first row (Images!$A$1; |Item |ImageID|)

Answer 4

First and foremost, make sure the image's "General Access" is set to "Anyone with the link." The =IMAGE("my-gdrive-url") function won't work without the correct sharing permissions.

If you want to bulk import, here is a script that will add images from a GDrive folder. Navigate to "Extensions" > "Apps Script" on your google sheet to run this.

function addImagesFromFolder() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const folderId =  "Pull this off the end of your GDrive folder URL";   
  const folder = DriveApp.getFolderById(folderId);
  const contents = folder.getFiles();

  while (contents.hasNext()) {
    const i = sheet.getLastRow() + 1;
    const file = contents.next();
    Logger.log(`${i} - ${file}`);

    const data = [
        `=IMAGE("${file.getDownloadUrl()}")`,
        file.getName(),
    ];

    sheet.appendRow(data);
    sheet.setRowHeight(i, 128);
  };
};