Mapping google sheets cell to google drive and retreiving the link

Question

I have a name in cell A2 eg; A2="Giraffe"

i have a specific folder in which i have a file named "Giraffe"

Can google sheets find the file and get the link in the output cell B2

Answer 1

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];
}