CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Mohamed H
Mohamed H

Reputation: 258

How display images from Google Drive on Gsheet cell?

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)

SCreenShot of Gsheet

Upvotes: 9

Views: 35668

Answers (8)

Илья Хоришко
Илья Хоришко

Reputation: 1205

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)

Upvotes: -1

BlackOctane
BlackOctane

Reputation: 1

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

Upvotes: -1

nkink
nkink

Reputation: 128

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.

Upvotes: -1

user23058132
user23058132

Reputation: 11

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

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

enter image description here

Upvotes: 1

Tanaike
Tanaike

Reputation: 201643

  • 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:

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:

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:

Upvotes: 14

Ty Sabs
Ty Sabs

Reputation: 229

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

Upvotes: 2

Omar Juvera
Omar Juvera

Reputation: 12307

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

Upvotes: 1

Nguyen Hieu
Nguyen Hieu

Reputation: 29

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

This works for me

Upvotes: 2

Related Questions