List all files in a folder, including files in sub folders

Question

I'm trying to create a Google Apps Script that will allow user to get all files inside a folder including files inside a sub folder connected to the main folder. (Please take note that In my code, I'm the one who is giving the folderid and this this will be the main folder)

I've managed to get all files from a folder, but I also want the files inside the sub folder to be extracted.

See below code that I have made so far.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
  //ss.addMenu("Search Document List", searchMenuEntries);
  ss.addMenu("Import Data from Google  Drive",searchMenuEntries);
}

function search()  {
   // Prompt the user for a search term
  var searchTerm = Browser.inputBox("Enter the folder ID of the files that you want to import:");
  //var folder = DocsList.getFolder(searchTerm);
  var folder = DocsList.getFolderById(searchTerm)
  var contents = folder.getFiles();

  var file;
  var data;

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();

  sheet.appendRow(["FileName","File name (with link)", "Revision Date", "File Type", "DocumentID", "Meta Data"]);

  sheet.appendRow(["StringFilter - Hidden", "StringFilter", "DateFilter", "StringFilter - Hidden","StringFilter - Hidden", "csvFilter - Hidden"]);

  for (var i = 0; i < contents.length; i++) {
    file = contents[i];

    if (file.getFileType() == "SPREADSHEET") {
      continue;
    }

    data = [ 
     file.getName(),
      "<a href= " + file.getUrl() + " target= '_blank'>" + file.getName() + "</a>",
      file.getLastUpdated(),
      file.getFileType(),
      file.getId(),
      file.getDescription()
    ];

    sheet.appendRow(data);
  }
};

Now, with this code result will be like this.

But it will only show files that is under the main folder, I also want to loop inside and check if there is a sub-folder hence check whether there are files inside such and get it.

Further, I want to add a column name "Folder/Sub-folder" wherein I want to display the folder name of that file and the main folder of that file (if applicable) see below example that I've done manually. (e.g. Main Folder = Folder1 and Sub-Folder = SubFolder1)

Please advise.

score 3 · Answer 1 · edited May 23 '17 at 12:19

The easiest solution (IMHO) is to use a recursive function that search folder and subfolders. Like this:

function search() {
  // Prompt the user for a search term
  var searchTerm = Browser.inputBox("Enter the folder ID of the files that you want to import:");
  var folder = DocsList.getFolderById(searchTerm);

  var data = [["FileName","File name (with link)", "Revision Date", "File Type", "DocumentID", "Meta Data", "Folder/Sub-folder"],
              ["StringFilter - Hidden", "StringFilter", "DateFilter", "StringFilter - Hidden","StringFilter - Hidden", "csvFilter - Hidden",""]
             ];
  searchFolder(folder, data, "");

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear().getRange(1,1,data.length,data[0].length).setValues(data);
}

function searchFolder(folder, matrix, path) {
  path += folder.getName();
  addFiles(folder.getFiles(), matrix, path);
  path += ',';
  var folders = folder.getFolders();
  for( var i = 0; i < folders.length; ++i )
    searchFolder(folders[i], matrix, path);
}

function addFiles(files, matrix, path) {
  for (var i = 0; i < files.length; i++) {
    var file = files[i];

    if (file.getFileType() == "SPREADSHEET")
      continue;

    matrix.push([ 
     file.getName(),
      "<a href= " + file.getUrl() + " target= '_blank'>" + file.getName() + "</a>",
      file.getLastUpdated(),
      file.getFileType(),
      file.getId(),
      file.getDescription(),
      path
    ]);
  }
}

Obviously, this function assumes you don't have more files that can be handled in a single run of the script (you know about the 6 minutes maximum execution time, right?). If you need a script that can be resumed later (automatically or manually), you'll have to use DriveApp instead of DocsList. Take a look at this other answer where I show how to do it.

List all files in a folder, including files in sub folders

1 Answers1

Linked