Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 1 down vote accepted

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=download&id=FILE_ID is not documented and could stop to work at any time without previous official notice.

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

share|improve this answer
  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);



    };
};
share|improve this answer
1  
I may have over answered :) - URL would be https://docs.google.com/uc?export=download&id=FILE_ID – Tom Woodward Oct 30 '15 at 16:40
    
See the answer of @AEI -> webapps.stackexchange.com/a/89112/88163 – Rubén Jan 25 at 18:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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