Google sheets lookup file by filename in google drive

Question

is there a way to link to a file on Google drive by the filename?

I am listing data on a google sheet, and in the same folder, I have multiple pdf files corresponding to the data on the sheet. Say, id: 1234 has a corresponding pdf file 1234.pdf.

is there a way to quickly link to the file based on the filename?

Answer 1

Explanation:

I believe the only way to achieve your goal is to use Google Apps Script and in particular the DriveApp class.

• The following script will get a column of names as input and it will return back to the sheet the urls for each name within a specified folder.

• It assumes that the name of the files are unique within this folder.

Solution:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1'); // put the name of your sheet
  const folder_id = "fid"; // put the folder id
  const folder = DriveApp.getFolderById(folder_id);
  const names = sh.getRange('A2:A'+sh.getLastRow()).getValues();
  const urls = names.map(name=>[folder.getFilesByName(name+".pdf").next().getUrl()]);
  sh.getRange(2,2,urls.length,1).setValues(urls);   
}

From the script above adjust the following four things:

• Choose the name of your sheet; replace Sheet1 to the name of your sheet.
• Put the folder id of your folder in fid.
• The file names are copied from column A and from the range A2 until the last row with content. Choose the column where you have the name of the files.
• Adjust the starting cell you want to paste the columns. In my case sh.getRange(2,2,..) means start pasting from the cell B2.

Sample sheet used for the script: