11

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.

5

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].

4

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"))
    
  • This script does not work - error is "TypeError: Cannot call method "replace" of undefined. (line 3, file "Code")" (Sorry, scripting is kind of magic to me, so I can't offer much more at this time) – cduston May 26 '18 at 15:13
  • @cduston Have you followed the steps? – Rubén Jan 20 at 15:37
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);



    };
};
2

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:

enter image description here

enter image description here

1

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

  • A life saver, thank you! – post_ahead Mar 9 at 13:07
0

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"

protected by Community Jul 17 at 21:24

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.