Reputation: 2574
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
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