Insert image from Google Drive into Google Sheets

Question

How do I insert/link to images existing in a folder in Google Drive into a Google Sheets spreadsheet?

There is the image() function, I imagine using it something like this:

image("googledrive://somefodleringoogledrivewithmypics/pic_1.png")

How?

Update: Some people noted that by uploading pictures to Picasa, we can then get the URL of each picture and use that. It works. The downside of this is that we have to copy each and every URL of all images. I put that in a separate sheet, then use VLOOKUP to get each image URL per index. It works but the URL copying part is a hell of a job if you have lots of images of course. A better solution is needed.

Answer 1

Short answer

While it's possible to use files stored in Google Drive as website resources at this time it's not officially supported and should not be used for websites and spreadsheets that have a lifespan beyond August 31, 2016.

As was mentioned by AE I in his answer to a related question, the use of https://docs.google.com/uc?export=view&id=FILE_ID is not documented and could stop to work at any time without previous official notice but at this time (February, 2019) it still works.

Explanation

The instructions to publish Google Drive files as website content are in Publish Website Content - Google Drive REST API but according to Deprecating web hosting support in Google Drive - Google Apps Developer Blog

Beginning August 31st, 2015, web hosting in Google Drive for users and developers will be deprecated. You can continue to use this feature for a period of one year until August 31st, 2016, when we will discontinue serving content via googledrive.com/host/[doc id].

Answer 2

I made a two lines script to use the share link of an image in Google Drive.

1. Go to Tools > Script editor.
2. Copy, paste the following code

3. Click on run for permission

   function DRIVE_IMAGE(link){
       prefix_url = "https://docs.google.com/uc?export=download&";
       link.replace("open?", "uc?export=download&");
   }
   

Using the script :

1. Copy the share link of your image in Google Drive.
2. Go to a cell

3. Enter the formula

   =IMAGE(DRIVE_IMAGE("COPIED_LINK"))
   

Answer 3

1. You'll need to make a spreadsheet.
2. Then go to tools>script editor.
3. You can cut/paste the script below replacing all of the contents that were put in by default.
4. You'll need to add your folder ID where it says THIS_SHOULD_BE_YOUR_FOLDER_ID (leave the quotes).
5. Save it.
6. Hit the play/run button
7. You'll need to give it permission to run when asked.

That should do it. Working example of output here.

/* modified from @hubgit and http://stackoverflow.com/questions/30328636/google-apps-script-count-files-in-folder 
for this stackexchange question http://webapps.stackexchange.com/questions/86081/insert-image-from-google-drive-into-google-sheets by @twoodwar
*/
function listFilesInFolder(folderName) {

   var sheet = SpreadsheetApp.getActiveSheet();
   sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Image"]);


//change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
    var folder = DriveApp.getFolderById("THIS_SHOULD_BE_YOUR_FOLDER_ID");
    var contents = folder.getFiles();

    var cnt = 0;
    var file;

    while (contents.hasNext()) {
        var file = contents.next();
        cnt++;

           data = [
                file.getName(),
                file.getDateCreated(),
                file.getSize(),
                file.getUrl(),
                "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
                file.getDescription(),
                "=image(\"https://docs.google.com/uc?export=download&id=" + file.getId() +"\")",
            ];

            sheet.appendRow(data);



    };
};

Answer 4

The best and easy way to handle this is by using substitute formula and having two columns here is the explanation below:

Column A5: just put google drive image shareable link e.g.
"https://drive.google.com/open?id=1CfUnvGGdt96irA419HnU0BvRafD4F_os"

Column B5: use the combination of two formula image and substitute like this:
=image(substitute(A5,"open?id","uc?export=download&id"))

Explanation this will convert the google image indirect image file into direct link and give the image outcome as you want it.

Screenshot:

Answer 5

You can do this by linking to the URL of a Google Drawing, steps below:

1. Create a new drawing in Google Drive (right-click in any folder in Google Drive, "More", "Drawing")
2. paste your image in there
3. File -> Publish to web -> As Link, Start Publishing
4. Copy URL provided and go to Google Sheet cell you want to insert
5. =image("URL you copied")

It does duplicate the data, so you are not linking to the actual image but to a Google Drawing that has a copy of the image. Anyone could go to that link and download in any of the supported formats (JPEG, PNG, SVG, or PDF).

Answer 6

I took the above answer from (Zeeshan) and combined it into one cell.

Enter this into the cell:

=image(substitute("https://drive.google.com/open?id=1cmtBYSbobv3FYpKKBCPvS","open?id","uc?export=download&id"))

Replace "https://drive.google.com/open?id=1cmtBYSbobv3FYpKKBCPvS" with your "Shareable Link"