Javascript – Mapping google sheets cell to google drive and retreiving the link

In your situation, how about the following sample script?

Sample script 1:

Before you use this script, please set sheetName and folderId. When this script is run, a filename is retrieved from "A2" and when the file of filename exists, the link of the file is put into "B2".

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

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("A2");
  const filename = range.getDisplayValue();
  const files = DriveApp.getFolderById(folderId).getFilesByName(filename);
  if (files.hasNext()) {
    range.offset(0, 1).setValue(files.next().getUrl());
  } else {
    console.log(`File of ${filename} was not found.`);
  }
}

Sample script 2:

Before you use this script, please set sheetName and folderId. When this script is run, the filenames are retrieved from "A2:A" and when the files of filenames exist, the links of the files are put into "B2:B".

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

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("A2:A" + sheet.getLastRow());
  const filenames = range.getDisplayValues();
  const values = filenames.map(([filename]) => {
    const files = DriveApp.getFolderById(folderId).getFilesByName(filename);
    return [files.hasNext() ? files.next().getUrl() : null];
  });
  range.offset(0, 1).setValues(values);
}

Added:

Although, unfortunately, I cannot know your actual situation, about i have a specific folder in which i have a file named "Giraffe", I’m worried that the files might be put into the subfolders under a specific folder. If my understanding is correct, please test the following sample script. The following script retrieves the files under the folder including the subfolders. But, this is just my guess.

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

  const getFiles = (folder, obj = {}) => {
    if (folder) {
      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 sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("A2:A" + sheet.getLastRow());
  const filenames = range.getDisplayValues();
  const values = filenames.map(([filename]) => [obj[filename] ? obj[filename].join(",") : null]);
  range.offset(0, 1).setValues(values);
}

Another approach:

If no links of the files are put to column "B" even when the above script, it is considered that the files of the filenames of column "A" do not exist in the folder. At that time, please test the following script. When the following script is run, all files in the folder are retrieved. And, the filenames and the file links are put into columns "B2:C".

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

  const getFiles = (folder, obj = {}) => {
    if (folder) {
      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 values = Object.entries(obj);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange(2, 2, values.length, values[0].length).setValues(values);
}

• If you want to put the values to "A2:B", please modify sheet.getRange(2, 2, values.length, values[0].length).setValues(values); to sheet.getRange(2, 1, values.length, values[0].length).setValues(values);.