How to retrieve a Google Drive file link in Google Sheets based on a filename in a specific folder using Apps Script

I’m working in Google Sheets, and I have the following setup:

• Cell A2 contains a filename, for example: "Giraffe"
• I have a known Google Drive folder (I know the folder ID)
• That folder contains a file named "Giraffe" (same as A2)

I want to automatically:

1. Read the filename from cell A2
2. Search the specific Google Drive folder for a file with that name
3. Retrieve the file’s URL
4. Place the link into cell B2

Bonus:
If the file is not found, I’d like to return "File not found" in B2 instead.

What is the best way to do this using Google Apps Script?

From your following reply,

I cant thank you enough for doing this for me Maybe its me who is making a mistake I am Going to post links of the google sheet and drive below I am getting some unknown error Google Sheet Link- docs.google.com/spreadsheets/d/… Google Drive Folder Link - drive.google.com/drive/folders/…

When I saw your folder, I noticed that in your situation, no files are existing, only the folders are existing. In this case, please test the following script. In this script, both folders and the files are retrieved. When you use this script, please install the OnEdit triggert to installedOnEdit and put the folder name to column "A". By this, the folder link is put into column "B".

Sample script:

function installedOnEdit(e) {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const folderId = "###"; // Please set your folder ID you want to search the files.

  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart != 1 || range.rowStart == 1) return;

  const getFiles = (folder, obj = {}) => {
    if (folder) {
      const folderName = folder.getName();
      const folderLink = folder.getUrl();
      obj[folderName] = obj[folderName] ? [...obj[folderName], folderLink] : [folderLink];
      const files = folder.getFiles();
      while (files.hasNext()) {
        const f = files.next();
        const filename = f.getName();
        const link = f.getUrl();
        obj[filename] = obj[filename] ? [...obj[filename], link] : [link];
      }
      const subFolders = folder.getFolders();
      while (subFolders.hasNext()) {
        getFiles(subFolders.next(), obj);
      }
    }
    return obj;
  }

  const folder = DriveApp.getFolderById(folderId);
  const obj = getFiles(folder);
  const filename = range.getDisplayValue().trim();
  if (obj[filename]) {
    range.offset(0, 1).setValue(obj[filename]);
  }
}

If you want to retrieve only folders, please remove the following script from the above script.

const files = folder.getFiles();
while (files.hasNext()) {
  const f = files.next();
  const filename = f.getName();
  const link = f.getUrl();
  obj[filename] = obj[filename] ? [...obj[filename], link] : [link];
}