CODEWITHSUNDEEP
google-sheetsgoogle-sheets-api
Jake Zeitz
Jake Zeitz

Reputation: 2574

How to hyperlink a cell in google spreadsheets to an image in a folder on google drive?

I have a google spreadsheet with a column named photo. In that column are image names (ex: "123") which match up to images in a separate google drive folder (ex: "IMG_123.jpg"). Currently, in order to view an image I have to open up the google drive folder and search for the image manually.

I would like to do something like this, if A1 is the cell with contents "123":

"=HYPERLINK('https://drive.google.com/drive/foldername/IMG_"&A1&".jpg',"&A1&");"

If this is even possible, what should the url be to reference a specific image?

UPDATE

Now I've created a google script function that works! But it only seems to work on the first active cell in a range:

function getImageLinks() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  var folder = DriveApp.getFolderById("my_folder_id_here");
  var contents = folder.getFiles();

  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();

  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var imgName = range.getCell(i,j).getValue();
      while (contents.hasNext()) {
        var file = contents.next();
        var fileName = file.getName();

        if(fileName.search(imgName) >= 0){
          range.getCell(i,j).setFormula("=HYPERLINK(\""+file.getUrl()+"\",\""+imgName+"\")");
          break;
        }
      }
    }
  }
}

Can anyone point out what I'm doing wrong here?

Upvotes: 0

Views: 1952

Answers (1)

Tom Woodward
Tom Woodward

Reputation: 1713

I don't think it'll quite work that way. You'll notice if you go to that particular folder that the URL does not reference the folder name. It references a particular ID string. The same goes for files.

There are ways to do that with Google Script. 

function getImageLinks() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var folder = DriveApp.getFolderById("YOUR_FOLDERID");
  var contents = folder.getFiles();

  var numRows = sheet.getLastRow();
  Logger.log('number of rows = ' + numRows);

  for (var i = 1; i <= numRows; i++) {

      var imgName = sheet.getRange(i, 1).getValue(); //adjust number to reflect column of value
      while (contents.hasNext()) {
        var file = contents.next();
        var fileName = file.getName();

        if(fileName.search(imgName) >= 0){
          sheet.getRange(i,1).setFormula("=HYPERLINK(\""+file.getUrl()+"\",\""+imgName+"\")"); //adjust number for getRange to reflect column
          break;

      }
    }
  }
}

Upvotes: 3

Related Questions