Insert Google Drive image into Google Sheets


👤 Diwas Poudel    🕒 Oct 16 2022    📁 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 - Enlarges 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 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 then 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 makes take 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

Extension > App Script
fig. Extension > 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

10 Now come to Google sheet and then click on Generate Images > Preview Images.

click_on_generate_button_preview_images

11 In the popup paste the FolderId which we have generated in step 4.

Paste FolderId AppScript
fig. Paste FolderId generate in Step 4

The result of the above script looks like this:

Multiple Images in Google Sheet generate via App Script
fig. Multiple Images in Google Sheets generate via App Script

Here we can see all the images located inside that particular folder. Note that it may take some seconds to load all the images.

Conclusion:

In this way, we have successfully Inserted image from Google Drive into Google Sheets

 

 

 

 

👊 MORE FROM OURTECHROOM 👊



Ourtechroom Main Logo
Hello and thank you for visiting Ourtechroom.com! We'll talk about everything from how to troubleshoot tech issues, to emerging technology, games, gadgets, and fun facts about technology.
Contact : ourtechroom777@gmail.com