Google Sheets – How to Link Multiple Files in Google Drive from Google Sheet

google sheetsgoogle-drivelinks

I have a bunch of PDF files in a single folder (say "myPDFs") in my Google Drive.

I have a Google Sheet stored in the same Google Drive. One column of this Sheet has the filenames of the PDF files. (eg: "apple.pdf", "banana.pdf", etc.) I would like to transform each of these into a link to the corresponding file.

I know I can use the hyperlink function to create a link from a URL and link text, but the URL for the files (what Drive calls the "shareable link") is not easily computable from the filename. eg: myPDFs/apple.pdf might have a URL like https://drive.google.com/open?id=Ia18d1uTYY5jfdyrhodAJ7Xofk4g0bLm5. I do not want to have to manually retrieve the "shareable link" from the Drive UI, as there are several hundred files involved.

I tried making a custom function that uses DriveApp.getFilesByName to find the URL for each filename, but apparently that function cannot be called from a custom function.

How can I easily link to these files from my sheet?

Best Answer

2nd EDIT

(following OP's request) "I need a solution that gets me all the way from having a list of filenames (what I have now) to having a list of links I can click on and get to the corresponding file. Incomplete answers like "write a custom menu that uses DriveApp.getFilesByName" won't be accepted"

1. On the spreadsheet's menu find Tools and click on Script editor.

2. Delete all content there and replace it with the following script.

function ListFldPdfs() {
  // replace FOLDER-ID with your folder's ID
  // replace SHEET-NAME with the name of the sheet that will hold the list
  var myPDFfolder = DriveApp.getFolderById('FOLDER-ID'); // replace FOLDER-ID with your folder's ID
  var thesheet = 'SHEET-NAME' // give a name to YOUR list sheet

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newPDFsheet = ss.getSheetByName(thesheet); 
     if (!newPDFsheet) { 
       ss.insertSheet(thesheet, 0); 
       var newPDFsheet = ss.getSheetByName(thesheet) ;
  Logger.log(SpreadsheetApp.getActiveSheet().getName());
     }
  // clear all existing content
  newPDFsheet.clear();
  // append the header row
  newPDFsheet.appendRow([ "Name", "VIEW", "HYPERLINK"]);
  var results = [];
  // list all pdf files in the folder
  var mypdfs = myPDFfolder.getFilesByType(MimeType.PDF);
  // loop through found files in the folder
  while (mypdfs.hasNext()) { 
    var myfile = mypdfs.next(); 
    var fname = myfile.getName();
    var furl = myfile.getUrl();
    results = [ fname, furl, ];
    //Logger.log("results = "+results); // for de-bugging
    newPDFsheet.appendRow(results); 
       } 
     var fcell = ss.getSheetByName(thesheet).getRange("C2"); 
               fcell.setFormula('=arrayformula(if(A2:A10="",,hyperlink(B2:B10,""&A2:A10&"")))'); 
}

3. Replace the FOLDER-ID with the ID of the folder to be searched and the SHEET-NAME with a name of your choice.

4. Save and authorize the code by clicking the Advanced button.

5. Run the script.

What the script does
When run for the first time, it creates a new sheet ('SHEET-NAME') placing it in front of the rest of the sheets, where you get the name of the file (Name) in the specified folder('FOLDER-ID'), the view URL of the file (VIEW) as well as the hyperlink to it (HYPERLINK).
After that and whenever you choose to re-run it (because you have maybe made some changes to the folder like added more files), it deletes just the content of the sheet ('SHEET-NAME') and repeats the process on the same sheet. (We avoid using an onOpen or onEdit trigger for the script because it would be an overstrain)

Initial answer

(Hoping I have not misunderstood your question)

One of the functions in Google Sheets is HYPERLINK. It creates a hyperlink inside a cell. It's syntax is:

HYPERLINK(url, [link_label])

In your case it would be: 

=HYPERLINK("https://drive.google.com/file/d/XXXXXXX/", "banana.pdf")

Place the formula in a cell and when clicking on it, it will take you (since it is a .pdf) to the preview (since it is a .pdf) of the file. Repeat for as many files as you have.
You can read more about the HYPERLINK function here.

TIP: Because it is the shareable link, you have to use the full URL and not just the file ID.

EDIT

(following OP explanation)

As a workaround, you can use the following script: 


// replace FOLDER-NAME below with the folder for which you want a listing
function listFolderContents() {
  var foldername = 'FOLDER-NAME';
  var folderlisting = 'listing of folder ' + foldername;



var folders = DriveApp.getFoldersByName(foldername)
  var folder = folders.next();
  var contents = folder.getFiles();



var ss = SpreadsheetApp.create(folderlisting);
  var sheet = ss.getActiveSheet();
  sheet.appendRow( ['name', 'link'] );



var file;
  var name;
  var link;
  var row;
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow( [name, link] );

  }

};

It will create a new spreadsheet in the root directory (My Drive) with the folder's name (listing of folder FOLDER-NAME) where you will find all file names from the folder in question along with their URL.
You can then use a simple formula (=QUERY(A:B, "select * where A matches '.*pdf.*'",1)) to filter out just the files you need.

Related Topic