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)

Novelize

Is that a naked link our a hyperlink (=hyperlink( , ))? – Solve

@James D the URL link are from pictures taken by smartphone, and stored in Gdriver via with a Gform I have created – Novelize

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

Updated: September 30, 2023

As the current method, I think that the following patterns might also be able to be used.

Script 1:

In this script, the file content retrieved from Google Drive is used as an object of SpreadsheetApp.CellImage. In this case, when the image size is large, an error might occur. Please be careful about this.

const fileId = "###fileId###": // Please set the file ID of the image file on Google Drive.

const file = DriveApp.getFileById(fileId);
const dataUrl = `data:${file.getMimeType()};base64,${Utilities.base64Encode(file.getBlob().getBytes())}`;
const img = SpreadsheetApp.newCellImage().setSourceUrl(dataUrl).build();
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A1").setValue(img);

When this script is run, an image is put into a cell "A1".

Script 2:

In this script, the thumbnail of file content retrieved from Google Drive is used as an object of SpreadsheetApp.CellImage. In this case, even when the image size is large, this script can be used, because the image size is resized.

const fileId = "###fileId###": // Please set the file ID of the image file on Google Drive.

const imageUrl = `https://drive.google.com/thumbnail?sz=w1000&id=${fileId}`; // Ref: https://stackoverflow.com/a/31504086
const bytes = UrlFetchApp.fetch(imageUrl, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getContent();
const dataUrl = `data:${MimeType.PNG};base64,${Utilities.base64Encode(bytes)}`;
const img = SpreadsheetApp.newCellImage().setSourceUrl(dataUrl).build();
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A1").setValue(img);

// DriveApp.getFiles(); // This comment line is used for automatically detecting a scope of https://www.googleapis.com/auth/drive.readonly This is used for ScriptApp.getOAuthToken()

When this script is run, an image is put into a cell "A1".

References:

• newCellImage()
• Class CellImageBuilder

Malediction

I have followed your recommendation (Pattern 2) and I have added your script sample in my project, and I have met an error during running of script on the following line: var image = sheet.insertImage(blobSource, 1, 1); I have tried several solution from stackoverflow as link1 and link2 and I have not succeeded – Novelize

Thank you for your feedback I have tested the first pattern and the solution is OK only if the file is shared with Public access as you mention previously. However, I cannot shared publicly the picture due to company compliance rules. I will check the second solution and will give you my feedback – Novelize

@Mohamed H Thank you for replying. I apologize for the inconvenience. About the pattern 1, I could understand about your actual situation. About the pattern 2, about I have met an error during running of script on the following line: var image = sheet.insertImage(blobSource, 1, 1);, unfortunately, I couldn't image the detail error message from it. This is due to my poor skill. I deeply apologize for this. Can you provide the error message? By the way, the owner of file of the URL is you? – Malediction

the error message is in french Erreur liée à un service : Feuilles de calcul (ligne 6, fichier "InsertImageInCell") about the line in script var image = sheet.insertImage(blobSource, 1, 1);. Yes I am the owner of Ghseet. – Novelize

@Mohamed H Thank you for replying. Unfortunately, I cannot understand about french. This is due to my poor skill. I deeply apologize for this. Can I ask you about the meaning of the error message? And in your script, can I ask you about the value of fileId of DriveApp.getFileById(fileId).getBlob(); you tested? Because if you are using URL for it, an error occurs. But if I misunderstood your situation, I apologize. – Malediction

@Mohamed H By the way, can I ask you about the image size of the file you want to put? There is the limitation for the image which can be put to Spreadsheet. About this, you can see the information at gist.github.com/tanaikech/9414d22de2ff30216269ca7be4bce462 Also please be careful this. – Malediction

the image size are about Dimensions ‪4 000 pix x 3 000‬ pix. The image are photos from smartphone. – Novelize

@ Tanaike about error message, Gtranslate : Exception: Service-related error: Worksheets (line 6, "InsertImageInCell" file) . The ID value is set as var fileId = '1K4A2DBAVVJwhECsTMrlAKlwo_nuE94mZ' from the URL image drive.google.com/open?id=1Rs36UEEE4R3ClVVXAMuZtSfBcBzTK9pp I would like to know how to test this line in script. Thank you – Novelize

@Mohamed H Thank you for replying. From your replying, I could confirm the reason of your issue. The issue was the size of the image. There is the limitation area for putting the image to Spreadsheet. The limitation area is 1,048,576 pixels^2. In your case, 4 000 pix x 3 000‬ pix = 12,000,000 is over than that. By this, such error occurs. So can you test the script by reducing the size of image? For example, when you want to resize the image using script, you can also use the script for resizing image. https://mcmap.net/q/1172633/-google-script-how-to-resize-image-in-blob – Malediction

@Mohamed H I proposed a sample script for putting the image by resizing. Could you please confirm it? If that was not useful, I apologize again. – Malediction

Answer 2

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

This works for me

Telegu

This is explained here https://mcmap.net/q/1145401/-how-display-images-from-google-drive-on-gsheet-cell – Blithering

@SurajRao it does not work when the files is private, you have to remind that the files must be shared in public mode. – Novelize

Answer 3

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);
  };
};

Pine

Answer 4

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|)

Chiffchaff

Answer 5

I found the solution this video: https://www.youtube.com/watch?v=h3Okv1fnMWM

=IMAGE(SUBSTITUTE(TRIM($F7),"open?id","uc?export=download&id"))

Mittel

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Pixilated

Answer 6

The URL structure has changed as now of December 2023. The original URL format is "https://drive.google.com/file/d/FILEID/view?usp=drive_link" and download URL format is "https://drive.usercontent.google.com/u/0/uc?export=download&id=FILEID"

So, you should get only FILEID from google drive file link and make the download link with it.

Alterant

Answer 7

Solution:

=arrayformula(LET(
  id, IF(REGEXMATCH(C2:C, "https://drive.google.com/"), REGEXEXTRACT(C2:C, "id=([^&]+)"), C2:C),
  url, IF(REGEXMATCH(id, "^http"), C2:C, "https://drive.google.com/uc?id=" & id),
  HYPERLINK(url, IMAGE(url))
))

Untenable

On Stack Overflow, the how is important, but much of the site's quality level comes from people going out of their way to explain why. While a correct code-only answer get the person who asked the question past whatever hurdle they might be facing, it doesn't do them or future visitors much good in the long run. See Is there any benefit in code-only answers? – Clydesdale

Answer 8

My solution

=IMAGE(ПОДСТАВИТЬ(J7; "open?id=";"uc?export=download&id="))

OR

=IMAGE(=SUBSTITUTE(J7, "open?id=", "uc?export=download&id="))

---

Docs: (https://support.google.com/docs/answer/3094215?hl=ru&sjid=4569402517862628001-EU)

Remittance