title: Dynamic Hyperlink based on cell contents in google sheets?
body: <p>There is a list of items in a google spreadsheet, that we need to link a bunch of files from another folder on the google drive.  The link is constructed with the function shown below:</p>
  
  <pre><code>function bscott () {
  
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var s=ss.getActiveSheet();
    var c=s.getActiveCell();
    var fldr=DriveApp.getFolderById("**FOLDER ID - not included for security**");
    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);
  }
  </code></pre>
  
  <p>The problem we have encountered is if the contents of the folder do not match the ordering of our list exactly, the ordering of files to line items gets out of sync. We want certain files to hyperlink to certain cells in our sheet if the contents of another cell in the same row match the contents in the file name. We would like to avoid manually creating links for each line item. Is this possible in google sheets? </p>