1

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)

1

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

| improve this answer | |
  • Oh wow, that's awesome. I actually ended up figuring out how to do roughly the same thing (but more inefficiently), so being able to do it just running one piece of code is a true timesaver. Thank you so much! – KLonge Jul 2 at 14:40
  • Glad it worked out for you. If the answer was useful, you might consider "accepting" it. – Tedinoz Jul 3 at 0:18
  • How do I "accept" it? – KLonge Jul 3 at 6:15
  • It's explained here - What should I do when someone answers my question?. – Tedinoz Jul 3 at 12:59
  • Thanks a bunch. Just accepted it. – KLonge Jul 3 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.