Google Sheets – Creating Hyperlinks of Google Drive Items in Alphabetic Order

google sheetsgoogle-codelinksmicrosoft excelsorting

The original post that I got the google script editor code from was here:
Get share link of multiple files in Google Drive to put in spreadsheet
It works perfectly fine, but what I want to do is show the actual google drive link in the excel file (not the name of the file that has a hyperlink attached to it. I managed to do this by deleting #"' + f.getName() + '"# from the code, but the problem is, because they are no longer listed by file name (e.g. LG1, LG11, LG5), but by drive links (e.g.http….), the links can't be sorted with the sort feature on sheets. Does anyone know how I can deal with this?

I will post the code below so

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() + '")';
    names.push([str]);
  }
  s.getRange(c.getRow(),c.getColumn(),names.length).setFormulas(names);
}

PS: The reason I am trying to do this is because VLOOKUP doesn't seem to work properly in excel when you try lookup cells with hyperlinks attached as it just takes the placeholder string (e.g. LG1) and gives you that without the hyperlink (e.g. http…) that was attached to it.
Another potential solution would be knowing how to sort the initial folder in the first place to my liking (haven't managed to get that to work though)

Best Answer

An option is to output the file name as well as the HYPERLINK() function. This should allow you to:

  • sort the output,
  • lookup the file name and return hyperlink (or vice versa, according to your preference)

In this snippet, str is the hyperlink and str2 is the name of the file.

The final line of code is simply setValues rather than setFormulas

while (files.hasNext()) {
    f=files.next();
    str='=hyperlink("' + f.getUrl() + '")';
    str2 = f.getName();
    names.push([str2,str]);
  }

  s.getRange(c.getRow(),c.getColumn(),names.length,2).setValues(names);
        
}

Example output

Screenshot

Related Topic