Insert Google Drive image into Google Sheets

👤 Diwas Poudel 🕒 13 Sep 2023 📁 TECH

There are times when you need to link to list all of the files on Google Drive in Google Sheets. During this process, you may encounter an image in the middle of the process, and you would like to display the image from Google Drive on Google Sheets. If this is the case and you are unsure how to proceed, then our tech room will walk you through the steps.

As a solution, Google Sheets comes equipped with an Image() function that enables users to insert an image file directly into a cell. The bare minimum requirement is that you must supply a URL for the source of the image file.

Image() function syntax:

=IMAGE(url,[mode],[height],[width])

Where,
Url = Image location on the web
mode = There are four modes in total.
1 - Resize the image to fit the cell. When you want to keep the same aspect ratio so that the image does not distort, this is a good option.
2 - Enlarge the image to fill the entire cell.
3 - Retains original size even when cropped.
4 - Custom

If you want the height and width then you have to specify 4 in mode.

The most important thing is those photos should have been shared with anyone link before proceeding further. Then you can get the link and share it.

Steps for using a Photo from Google Drive to Google Sheets.

1 Right Right-click on the image you want to share.

2 Click on Get Link

Get Image Link Google Drive — **fig. Get Link Image**

3 Then under the General Access dropdown select Anyone with the link

4 Click on Copy Link button. Now the link gets copied to the clipboard. When you paste somewhere else you will get the link to the image.

Copy Link of Image Google Drive — **fig. Copy Link of Image**

In my case, the link looks like this: https://drive.google.com/file/d/1i0TtJuhBHGToJnBxtOBfNWmpzFhIj6fc/view?usp=sharing

Here you only have to focus on ID ie 1i0TtJuhBHGToJnBxtOBfNWmpzFhIj6fc

5 Then Come to the Google Sheet and in a Google Sheet cell use it like below:

=Image("https://docs.google.com/uc?id=ImageID")

in my case it ImageId is equal to 1i0TtJuhBHGToJnBxtOBfNWmpzFhIj6fc so it looks like this:

=Image("https://docs.google.com/uc?id=1i0TtJuhBHGToJnBxtOBfNWmpzFhIj6fc")

To reflect images in Google sheet take a few seconds.

Paste Image function with image link in Google Sheet — fig. Final result for Single Image in Google Sheet

How to insert multiple images from Google Drive to Google Sheets?

You will need to make use of Google Script in order to insert several images from Google Drive into Google Sheets.

In my case, I have a folder with many images, and I want to insert those images into the Google Sheets, so we can do the following.

1 Right-click on the folder you want to share.

2 Click on Get Link

3 Then under the General Access dropdown select Anyone with the link

4 Click on Copy Link button. Now the link gets copied to the clipboard. When you paste somewhere else then you will get the link to the folder.

So my folder link looks like this:

5 Now come to Google Sheets and Click on Extensions > App Script.

Now you will get App Script Editor on the next screen.

6 Just paste the below code:

function onOpen() {
    var SS = SpreadsheetApp.getActiveSpreadsheet();
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Generate Images')
      .addItem('Preview Images', 'previewAllImages')
      .addToUi();
  };
  
  function previewAllImages(){
    var folderId = Browser.inputBox('Enter folder ID', Browser.Buttons.OK_CANCEL);
    if (folderId === "") {
      Browser.msgBox('Folder ID is invalid');
      return;
    }
    getFolderTree(folderId); 
  };
  
  
  function getFolderTree(folderId) {
      var sheet = SpreadsheetApp.getActiveSheet();
      sheet.appendRow(["Name", "Created Date","Updated Date", "Size", "URL", "Download", "Description", "Image"]);
   
       var folder = DriveApp.getFolderById(folderId);
       var filecontents = folder.getFiles();
   
       var cnt = 0;
       var file;
   
       while (filecontents.hasNext()) {
           var file = filecontents.next();
           cnt++;
   
              data = [
                   file.getName(),
                   file.getDateCreated(),
                   file.getLastUpdated(),
                   file.getSize(),
                   file.getUrl(),
                   "https://docs.google.com/uc?export=download&id=" + file.getId(),
                   file.getDescription(),
                   "=image(\"https://docs.google.com/uc?id=" + file.getId() +"\")",
               ];
               sheet.appendRow(data);
       };
  
  }

What this script is doing?

Initially, the onOpen() the function is called, which results in the creation of the Generate Images menu. Subsequently, the previewAllImages() function is called, which prompts the user to enter the FolderId. Finally, the getFolderTree() function is called, which conducts a search for all of the files that are contained within the folder before displaying each one. Here the main things which help in displaying an image are highlighted in yellow lines, which helps in generating an image from the imageurl.

7 Click on the Save icon.

Paste script and click on Save icon — **fig. Paste the script and click on the Save icon**

8 In the next screen, you may get a popup Authorization Required title. Just click on Review permissions. Then select your account which was connected to your Google Sheets. And then click on Allow on the next screen.

9 Now click on Run.

Now you get a message like "Execution Started and Execution Completed" in the same App script window.

Click Run in App Script