assign hyperlink to cell in google sheets to google drive files using google app script

Question

Below is a GAS that extracts file names and google drive hyperlinks from a folder and pastes them into google sheets.

Now I want to create google drive hyperlinks to a selected range in google sheets based on the file names(file names are accurately extracted using a different GAS) with its cell position maintained. Can someone supplement here or guide me to reference?

Requirement:

• selecting a range of cells and searching for "matching" file names recursively in the folders
• I have folder and subfolders so the script would have to navigate/search multi-level folders.
• The depth of the folders are about 3-4 levels

Below is the current working GAS

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var c=s.getActiveCell();
  var fldr=DriveApp.getFolderById("<id>");
  var files=fldr.getFiles();
  var names=[],f,str;
  while (files.hasNext()) {
    f=files.next();
    str='=hyperlink("' + f.getUrl() + '","' + f.getName() + '")';
    names.push([str]);
  }
  s.getRange(c.getRow(),c.getColumn(),names.length).setFormulas(names);
}

Spreadsheet sample:

Answer 1

1. There are the folder names and filenames on a sheet in the Spreadsheet.
   • These folders and files are in the specific folder.
   • Each name is the unique name in all values.
2. You select the ranges. For example, it's "A1:D12" in the Spreadsheet of your image.
3. You want to retrieve all file names and search the files using the filenames.
4. You want to replace the retrieved values to the formulas of hyperlinks of the files.

From your replying and additional information, I could understand like above. If my understanding is correct, how about this sample script? Please think of this as just one of several possible answers.

Modified script:

After you set the folder ID, please select the range (for example, "A1:D12") and run the function of myFunction(). By this, the filenames are replaced to the formulas of hyperlinks.

function myFunction() {
  var folderId = "###"; // Please set the folder ID.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var c = s.getActiveRange()
  var fldr = DriveApp.getFolderById(folderId);
  var values = c.getValues();
  var getFiles = function getFiles(folder) {
    var folders = folder.getFolders();
    while(folders.hasNext()) {
      var fol = folders.next();
//      for (var i = 0; i < values.length; i++) {
//        for (var j = 0; j < values[i].length; j++) {
//          values[i][j] = values[i][j] == fol.getName() ? ('=hyperlink("' + fol.getUrl() + '","' + fol.getName() + '")') : values[i][j];
//        }
//      }
      getFiles(fol);
    }
    var files = folder.getFiles();
    while (files.hasNext()) {
      var f = files.next();
      for (var i = 0; i < values.length; i++) {
        for (var j = 0; j < values[i].length; j++) {
          values[i][j] = values[i][j] == f.getName() ? ('=hyperlink("' + f.getUrl() + '","' + f.getName() + '")') : values[i][j];
        }
      }
    }
  }(fldr);
  if (values.length > 0) {
    s.getRange(c.getRow(), c.getColumn(), values.length, values[0].length).setValues(values);
  }
}

Note:

• When the following lines, which are commented above, are used, the hyperlinks of folders are also put.

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      values[i][j] = values[i][j] == fol.getName() ? ('=hyperlink("' + fol.getUrl() + '","' + fol.getName() + '")') : values[i][j];
    }
  }
  

• If the same filenames and folder names are existing in the cell values and in subfolders, the logic for parsing the folders and files from the cell values is required. In your additional image, I cannot understand about the logic. This is due to my poor skill. I apologize for this.

Answer 2

Recursing for hyperlinks in folders and subfolders

function makeHyperlinks() {
  var ss=SpreadsheetApp.getActive();
  var folder=DriveApp.getFolderById('Your Starting Folder Id');//The main folder to start in  
  var obj={id:ss.getId(),sheetname:'hyperlinks'};//spreadsheet info to pass to recursive function
  getFnF(folder,obj);
}

This function recurses through the folders and subfolders appending all of the hyperlinks into the spreadsheet as it goes.

function getFnF(folder,obj) {
  var ss=SpreadsheetApp.openById(obj.id);
  var sh=ss.getSheetByName(obj.sheetname);
  var files=folder.getFiles();
  while(files.hasNext()) {
    var file=files.next();
    sh.appendRow(['=hyperlink("' + file.getUrl() + '","' + file.getName() + '")']);
  }
  var subfolders=folder.getFolders() 
  while(subfolders.hasNext()) {
    var subfolder=subfolders.next();
    getFnF(subfolder,obj);
  }
}

Recursion