Mass assigning hyperlinks to Google Sheets / Excel cells

user_13737726 0
Viewed 1182

I've got a 3 column excel file (name / time / link). Name is the name of the college, time is the document upload date, and the link is a hyperlinked "test.pdf", for example. Now the 3rd column contains 6000 cells of text that are supposed to be clickable, for example, I'll upload all of my docs to AWS S3 or Google Drive, and then just put the links in the cells. Cell value and the file name are the same. (test.pdf cell = test.pdf downloadable file).

What I want to know is how do I assign all those links to those cells without manually copy/pasting the uploaded file link to the cell? There's 6000+ records of it, and it would take an insane amount of time. Of course, it doesn't matter if the files are on S3 or GD, I can upload them to any service that makes this possible, I guess.

TLDR I want to hyperlink all my 6000 cells to documents people can download, but I couldn't find any automated solution besides "do it manually".

PS: Folder structure goes like this

College 1 folder (10 pdf documents)

College 2 folder (10 pdf documents)

...

College 600 folder (10 pdf documents)

if the college name is Oxford for example, the first document in that folder would be P1OU (period 1 oxford university, all the way up to 10 documents)

This is the spreadsheet:

https://docs.google.com/spreadsheets/d/1kOWFQSMTYh0RJveDMqTXx_oeL_ueXkeKCBnHmTWk5ZA/edit?usp=sharing

google-sheets hyperlink excel automation
1 Answers

Assumptions

The solution that follows assumes a few things:

  • That you have your files already uploaded to Drive.
  • Tthat the file names in the spreadsheet correspond exactly with the file names in Drive.
  • That within the whole of your drive there are no other files with the same name.

A possible solution with Apps Script

  1. Open your spreadsheet.

  2. Navigate to the menu, "Tools" > "Script Editor".

  3. In the script editor window replace the function with:

    function getLinks() {

  var rangeNames = "C2:C10";
  var rangeLinks = "D2:D10";

  var fileNames = SpreadsheetApp.getActiveSheet().getRange(rangeNames).getValues();

  fileNames.forEach(function(row, i) {
    if (row[0] != "") {
      try {
        var fileFound = DriveApp.getFilesByName(row[0]).next();
        fileNames[i]=[fileFound.getDownloadUrl()];
      } catch(e){
        fileNames[i] = ["file not found"];
      };
    };
  });

  SpreadsheetApp.getActiveSheet().getRange(rangeLinks).setValues(fileNames);
}

  4. Replace the variables rangeNames and rangeLinks with the ranges you want to take for the name and the range you want to see the hyperlinks in. For example, based on your spreadsheet:

    var rangeNames = "C2:C50";
var rangeLinks = "D2:D50";

Warning For 6000 lines this will probably take a long time and may time-out (6 min max) or you may run into a quota issue (though I think for 6000 requests it should be fine). You will likely have to break it up into batches. See more about that here

  1. Run the script.

  2. If you want it to be a file with a different name which has a hyperlink. I would recommend using the =HYPERLINK function within sheets itself once you have all your links.

Explanation

The script first takes the range of the file names and puts them into an array. Then it runs a for loop that will search drive for the file name, and return its download URL. It will not search drive if the value is blank. If it can't find a value it will only return a "file not found" in the column.

References

DriveApp.getFilesByName()

file-iterator - what is returned from the getFilesByName

getDownloadUrl()

user_104450170
Related