Reference file in Google drive in Google Spreadsheet

2

votes

I have created a folder in Google Drive, e.g. test, and put 3 files inside it

a.jpg, b.jpg, c.jpg

I want in my Google Spreadsheet under the same account, to access the files, e.g. generate the link to image file, is it possible?

e.g.

 ColA     ColB
 a.jpg    [generate link to a.jpg in drive]
 a.jpg    [generate  link to b.jpg in drive]
 a.jpg    [generate  link to c.jpg in drive]

google-sheetsgoogle-docs-apigoogle-spreadsheet-apigoogle-drive-api

Use: imgur.com/dropbox ? - I had a quick look and there does not look to be an option for what you want. – eddyparkinson

2

votes

Yes its possible. Google for 'host website on google drive' for detailed instructions. Basically first share the folder as public, copy the folder id from its url and make a new url googledrive.com/host/folderid/a.jpg

2

votes

With GScripts you can use the following functions to create/modify the files:

This one creates a new Spreadsheet with all Folder names and all corresponding URLS (no header)

function mapAllFiles() { 
  var values = [];
  var folders = DriveApp.getFoldersByName("FOLDER_NAME");
  while (folders.hasNext()) {
    var folder = folders.next();
    var files = folder.getFiles();
    // Looping over all files in all folders with the given name
    while (files.hasNext()) {
      var file = files.next();
      Logger.log(file.getName());
      var fileInfo = [file.getName(), file.getDownloadUrl()];
      Logger.log(fileInfo);
      values.push(fileInfo);
      // values.push(file.getDownloadUrl());
    }
  }
  // To use an exiting spreadsheet
  // var sFile = DriveApp.getFilesByName("YOUR_SPREADSHEET_NAME");
  // var as = SpreadsheetApp.openById(sFile.next().getId());
  // Create a new spreadsheet
  var as = SpreadsheetApp.create("YOUR_SPREADSHEET_NAME");
  var sheet = as.getActiveSheet();
  // Fill in all values
  var range = sheet.getRange(1, 1, values.length, 2).setValues(values);
}

This one list the URLs for the given fileNames in the spreadsheet which are present in the folder

function mapListedFiles() { 
  var values = [];
  var folders = DriveApp.getFoldersByName("FOLDER_NAME");

  if (folders.hasNext()) {
    var folder = folders.next();

    var sFile = DriveApp.getFilesByName("YOUR_SPREADSHEET_NAME");
    var as = SpreadsheetApp.openById(sFile.next().getId());
    // var as = SpreadsheetApp.create("YOUR_SPREADSHEET_NAME");
    var sheet = as.getActiveSheet();
    var range = sheet.getRange(2,1,sheet.getDataRange().getLastRow(), 1).getValues();
    for (var i = 0; i < range.length-1; i++) {
      var files = folder.getFilesByName(range[i]);
      Logger.log(files);
      if (files.hasNext()) {
         var file = files.next();
         Logger.log(file.getName());
         Logger.log(file.getDownloadUrl());
        values.push([file.getDownloadUrl()]);
      } else {
        values.push(["MISSING"]);
      }
    }
    var range = sheet.getRange(2, 2, values.length, 1).setValues(values);
  }
}

1

votes

Here is a principal way to do it (without programming). Later on you can automate this process in any way you wish.

Using Google Drive API: Go to Google Drive files API example (you may need to perform additional registration steps on Google Developers portal): https://developers.google.com/drive/v2/reference/files/list#try-it
Filtering all files within a folder: In "q" field put the string:

'0B6eKcFRCDRT2NjRGbDYxODlTMFE' in parents and trashed = False

where 0B6eKcFRCDRT2NjRGbDYxODlTMFE is document ID of the folder where your images are located (you can view this identifier in URL when navigating to that folder in Google Drive web UI).
Extracting fields you need: In "fields" field put the string:

items(alternateLink,title)
Authentication: Click "Authorize requests using OAuth 2.0" toggle in top-right, and allow access to your google drive
Making API Request: Click "Execute"
Getting JSON-formatted response: Scroll down, and copy the fragment of response following "items":, between square brackets (including them) for example:

[ { "alternateLink": "https://...", "title": "Links to files" }, { "alternateLink": "https://...", "title": "shanghai.jpg" }, { "alternateLink": "https://...", "title": "map.JPG" }, { "alternateLink": "https://...", "title": "London_2008.jpg" }, { "alternateLink": "https://...", "title": "graph.png" }, { "alternateLink": "https://...", "title": "chart_1.png" } ]
Converting JSON to CSV: Paste this fragment to following web page: http://konklone.io/json/ - it will generate a table:
Using CSV as you need: Copy this table and paste into your Google spreadsheet.

Reference file in Google drive in Google Spreadsheet

3 Answers