1

So I have the script below which allows me to automatically create google drive hyperlinks of files within the same directory:

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var c=s.getActiveCell();
  var fldr=DriveApp.getFolderById(*[insert id]*);
  var files=fldr.getFiles();
  var names=[],f,str;
  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);
       
}

I am very novice, however, so I can't really figure out how I would get this to search through each of the subfolders as well to get their hyperlinks too. I feel as if this is probably relatively simple, but I am not well versed enough in google scripts to do this (if it was python, I could do this easily). Any help would be greatly appreciated.

KLonge
  • 55
  • 7

1 Answers1

0

I believe your goal as follows.

  • You want to retrieve all files under the specific folder, and put the values of URL and filename to the Spreadsheet.
  • The specific folder has the subfolders.
  • You want to achieve this using Google Apps Script.

Modification points:

  • In your script, the files are retrieved from just under the specific folder.
  • In order to retrieve all files under the specific folder including the subfolders, it is required to traverse all subfolders.

When above points are reflected to your script, it becomes as follows. In this modification, Drive service is used.

Modified script:

function myFunction() {
  // Method for retrieving the file list from the specific folder including the subfolders.
  const getAllFiles = (id, list = []) => {
    const fols = DriveApp.getFolderById(id).getFolders();
    let temp = [];
    while (fols.hasNext()) {
      const fol = fols.next();
      temp.push(fol.getId());
      const files = fol.getFiles();
      while (files.hasNext()) {
        const file = files.next();
        list.push([`=hyperlink("${file.getUrl()}")`, file.getName()]);
      }
    }
    temp.forEach(id => getAllFiles(id, list));
    return list;
  }

  const folderId = "###"; // Please set the folder ID.
  const names = getAllFiles(folderId);

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var c = s.getActiveCell();
  s.getRange(c.getRow(), c.getColumn(), names.length, 2).setValues(names);
}

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165