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.
