How do you create a google script in google sheets that will create hyperlinks to video files in google drive

Question

I have a google sheet with about 20 columns of data, all related to a video file. The video files are of plays from a football game. The video files are in google drive and named, m0001.mp4 to m0124.mp4 in order. Assuming for that game I have 124 video files (plays) for that game. For some games it might be m00356.mp4 to m00495.mp4 etc.

I did this before in excel using macros and gave in the name the first file and number of files and it would auto-popular with hyperlinks to the video files.

How can I do the same thing using scripts?

This was my macro from excel:

Sub nbsl()
Dim FldrLoc As String:  FldrLoc = "m:\2011 season\nbsl\"
    Dim rngAnchor As Range
    Dim CurrentFile As String:  CurrentFile = Dir(FldrLoc)
    Do While CurrentFile <> vbNullString
        Set rngAnchor = ActiveSheet.Range("V" & Rows.Count).End(xlUp).Offset(1, 0)
        ActiveSheet.Hyperlinks.Add Anchor:=rngAnchor, _
                            Address:=FldrLoc & CurrentFile, _
                            TextToDisplay:=CurrentFile
        CurrentFile = Dir()
    Loop
End Sub

Answer 1

Thanks to @Tednioz & @Niek Waarbroek I was able to figure most of this out.

Below is my code.

function getFiles() {

  var dApp = DriveApp;
  var vfolder = dApp.getFoldersByName("video").next();
  var yearfolder = vfolder.getFoldersByName("2018").next();
  var gamefolder = yearfolder.getFoldersByName("game1").next();
  var folderez = gamefolder.getFoldersByName("ez").next();
  var foldersl = gamefolder.getFoldersByName("sl").next();
  var ezIter = folderez.getFiles();
  var slIter = foldersl.getFiles();
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(yearfolder+gamefolder);

  var i = 2;

  while (ezIter.hasNext()) {
    var file = ezIter.next();
    var name = file.getName();
    var url = file.getUrl();
    range = 'AC'+(i);
    ss.getRange(range).setValue(url);
    i++
}

  i = 2;
  while (slIter.hasNext()) {
    var file = slIter.next();
    var name = file.getName();
    var url = file.getUrl();
    range = 'AD'+(i);
    ss.getRange(range).setValue(url);
    i++
}
}

There are a few things that I have not been able to figure out 1) Is there a way use a files full path if you know it? Example: "Driveroot:\video\2018\game1\sl"

2) Is there a way to have the links show up as just the name to a file and not the full URL?

3) Is there a way to have the video automatically play and keep looking when you click on the hyperlink?

4) The files will be names dms0001, dms0002, ..... to say dms0189. That is not that order they are being loaded. Is there a way to do this?

Answer 2

SORTING OF OUTPUT

This code is offered as a snippet to show how the results might be sorted in name order. The code, as written is in two parts; the first part sorts the results in column AC, before commencing the output for Column AD. This isn't strictly necessary and all the sorting could be combined

---

Sorting Column AC

  i++
  }
  // end of the first WHILE loop.

  // START OF THE SORTING CODE-Column AC
  // get the number of rows of content in Column AC
  var ACvals = ss.getRange("AC2:AC").getValues();
  var AClast = ACvals.filter(String).length;
  // Logger.log("DEBUG: AClast = "+AClast);  //DEBUG

  //  create a range
  var range = sheet.getRange(2, 29, AClast);
  // Logger.log("DEBUG: range = "+range.getA1Notation());//DEBUG

  // sort the range by file name
  range.sort({
    column: 29,
    ascending: true
  });

// END OF THE SORTING CODE-Column AC
i = 2;
// start of the second WHILE loop

---

Sorting Column AD

  i++
  }
  // end of the second WHILE loop. 
  // START OF THE SORTING CODE-Column AD
  // get the number of rows of content in Column AD
  var ADvals = ss.getRange("AD2:AD").getValues();
  var ADlast = ADvals.filter(String).length;
  // Logger.log("DEBUG: ADlast = "+ADlast);  //DEBUG

  //  create a range
  var range = sheet.getRange(2, 30, ADlast);
  // Logger.log("DEBUG: range = "+range.getA1Notation());//DEBUG

  // sort the range by file name
  range.sort({
    column: 30,
    ascending: true
  });
  // END OF THE SORTING CODE-Column AD
  }

Answer 3

I have two solutions that worked. I will post both below:

Option 1) Allows you to name the links something other than the file name:

function getFiles() {

  var dApp = DriveApp;
  var vfolder = dApp.getFoldersByName("video").next();
  var yearfolder = vfolder.getFoldersByName("2018").next();
  var gamefolder = yearfolder.getFoldersByName("game2").next();
  var folderez = gamefolder.getFoldersByName("ez").next();
  var foldersl = gamefolder.getFoldersByName("sl").next();
  var ezIter = folderez.getFiles();
  var slIter = foldersl.getFiles();
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(yearfolder+gamefolder);
  var i = 2;
  var files = [];

  while (ezIter.hasNext()) {
    files.push(ezIter.next());
} 

// sorts the files array by file names alphabetically
files = files.sort(function(a, b){
    var aName = a.getName().toUpperCase();
    var bName = b.getName().toUpperCase();

    return aName.localeCompare(bName);
});

files.forEach(function(file){
    var name = file.getName();
    var url = file.getUrl();
    range = 'AC'+(i);
    var filename = 'ezvideo'+(i-1);
    var semi = ";";  
    var hyp = "=hyperlink(\"";
    var quot = "\"";
    var clos = ")";
    var title = hyp  + url + quot + semi + quot + filename + quot + clos;  // str='=hyperlink("' + f.getUrl() + '","' + f.getName() + '")';
    ss.getRange(range).setValue(title);   
    i++;
});

  var i = 2;
  var files = [];
while (slIter.hasNext()) {
    files.push(slIter.next());
}

// sorts the files array by file names alphabetically
files = files.sort(function(a, b){
    var aName = a.getName().toUpperCase();
    var bName = b.getName().toUpperCase();

    return aName.localeCompare(bName);
});

files.forEach(function(file){
    var name = file.getName();
    var url = file.getUrl();
    range = 'AD'+(i);
    var filename = 'slvideo'+(i-1);
    var semi = ";";  
    var hyp = "=hyperlink(\"";
    var quot = "\"";
    var clos = ")";
    var title = hyp  + url + quot + semi + quot + filename + quot + clos;  // str='=hyperlink("' + f.getUrl() + '","' + f.getName() + '")';
    ss.getRange(range).setValue(title);   
    i++;
});
}

Or Option 2; which requires you to use the filenames to sort:

function getFiles() {

  var dApp = DriveApp;
  var vfolder = dApp.getFoldersByName("video").next();
  var yearfolder = vfolder.getFoldersByName("2018").next();
  var gamefolder = yearfolder.getFoldersByName("game1").next();
  var folderez = gamefolder.getFoldersByName("ez").next();
  var foldersl = gamefolder.getFoldersByName("sl").next();
  var ezIter = folderez.getFiles();
  var slIter = foldersl.getFiles();
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(yearfolder+gamefolder);

  var i = 2;

  while (ezIter.hasNext()) {
    var file = ezIter.next();
    var name = file.getName(); // use this or the created filename below
    var url = file.getUrl();
    range = 'AC'+(i);
    var filename = 'ezvideo'+(i-1);
    var semi = ";";  
    var hyp = "=hyperlink(\"";
    var quot = "\"";
    var clos = ")";
    var title = hyp  + url + quot + semi + quot + name + quot + clos;  // str='=hyperlink("' + f.getUrl() + '","' + f.getName() + '")';
    ss.getRange(range).setValue(title);   
    i++;
}

  // START OF THE SORTING CODE-Column AC
  // get the number of rows of content in Column AC
  var ACvals = ss.getRange("AC2:AC").getValues();
  var AClast = ACvals.filter(String).length;
  // Logger.log("DEBUG: AClast = "+AClast);  //DEBUG
  //  create a range
  var range = ss.getRange(2, 29, AClast);
  // Logger.log("DEBUG: range = "+range.getA1Notation());//DEBUG
  // sort the range by file name
  range.sort({
    column: 29,
    ascending: true
  });

// END OF THE SORTING CODE-Column AC  


  i = 2;
  while (slIter.hasNext()) {
    var file = slIter.next();
    var name = file.getName();
    var url = file.getUrl();
    range = 'AD'+(i);
    var filename = 'slvideo'+(i-1);
    var title = hyp  + url + quot + semi + quot + file + quot + clos;
    ss.getRange(range).setValue(title);  
    i++;
}

    // end of the second WHILE loop. 
  // START OF THE SORTING CODE-Column AD
  // get the number of rows of content in Column AD
  var ADvals = ss.getRange("AD2:AD").getValues();
  var ADlast = ADvals.filter(String).length;
  // Logger.log("DEBUG: ADlast = "+ADlast);  //DEBUG

  //  create a range
  var range = ss.getRange(2, 30, ADlast);
  // Logger.log("DEBUG: range = "+range.getA1Notation());//DEBUG

  // sort the range by file name
  range.sort({
    column: 30,
    ascending: true
  });
  // END OF THE SORTING CODE-Column AD
}

Thank you everyone for all of the help!!