How to list also files inside subfolders in google drive

Question

I found this google script that can list files inside a specific folder. When i tried it, it would only list files on the first level folder and ignore files in subfolders.

How do i make it so it can list also everything inside all subfolders.

This is the script

function ListNamedFilesandFolders() {
/* Adapted from Code written by @hubgit https://gist.github.com/hubgit/3755293
Updated since DocsList is deprecated  https://ctrlq.org/code/19854-list-files-in-google-drive-folder
*/


  // List all files and sub-folders in a single folder on Google Drive
  // declare the folder name
  var foldername = 'FolderName';

  // declare this sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // clear any existing contents
  sheet.clear();
  // append a header row
  sheet.appendRow(["Folder","Name", "Date Last Updated", "Size", "URL", "ID", "Description", "Type"]);


  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  // folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
  var folders = DriveApp.getFoldersByName(foldername);
  var foldersnext = folders.next();
  // Logger.log("THE FOLDER IS "+foldersnext);// DEBUG

  // declare an array to push data into the spreadsheet
  var data = [];

  // list files in this folder
  // myfiles is a File Iterator
  var myfiles = foldersnext.getFiles();

  // Logger.log("FILES IN THIS FOLDER"); DEBUG

  // loop through files in this folder
  while (myfiles.hasNext()) {
    var myfile = myfiles.next();
    var fname = myfile.getName();
    var fdate = myfile.getLastUpdated(); 
    var fsize = myfile.getSize();
    var furl = myfile.getUrl();
    var fid = myfile.getId();
    var fdesc = myfile.getDescription();
    var ftype = myfile.getMimeType();
    //Logger.log("File Name is "+myfile.getName()); //Logger.log("Date is "+myfile.getLastUpdated()); //Logger.log("Size is "+myfile.getSize());
    //Logger.log("URL is "+myfile.getUrl()); //Logger.log("ID is "+myfile.getId()); //Logger.log("Description is "+myfile.getDescription());
    //Logger.log("File Type is "+myfile.getMimeType());

    // Populate the array for this file
    data = [ 
      foldersnext,
      fname,
      fdate,
      fsize,
      furl,
      fid,
      fdesc,
      ftype
    ];
    //Logger.log("data = "+data); //DEBUG
    sheet.appendRow(data);
  } // Completes listing of the files in the named folder

  // Now get the subfolder
  // subfolders is a Folder Iterator
  var subfolders = foldersnext.getFolders();
  //Logger.log("THE SUBFOLDER(S) ARE"); DEBUG HEADING

  // now start a loop on the SubFolder list
  while (subfolders.hasNext()) {
    var subfolderdata = [];
    var mysubfolders = subfolders.next();
    var mysubfolder = mysubfolders.getName();  
    //Logger.log("Subfolder name:"+mysubfolder); //DEBUG

    // Get the files
    // mysubfiles is a File Iterator
    var mysubfiles = mysubfolders.getFiles();
    //Logger.log("FILES IN THIS FOLDER"); //DEBUG HEADING

    // now start a loop on the files in the subfolder
    while (mysubfiles.hasNext()) {
      var smyfile = mysubfiles.next();
      var sfname =  smyfile.getName();
      var sfdate =  smyfile.getLastUpdated(); 
      var sfsize =  smyfile.getSize();
      var sfurl =  smyfile.getUrl();
      var sfid =  smyfile.getId();
      var sfdesc =  smyfile.getDescription();
      var sftype =  smyfile.getMimeType();
      //Logger.log("Subfolder is "+foldersnext+"/"+mysubfolder); 
      //Logger.log("File Name is "+ smyfile.getName()); Logger.log("Date is "+ smyfile.getLastUpdated()); Logger.log("Size is "+ smyfile.getSize());
      //Logger.log("URL is "+ smyfile.getUrl()); Logger.log("ID is "+ smyfile.getId()); Logger.log("Description is "+ smyfile.getDescription());Logger.log("File Type is "+ smyfile.getMimeType());
      subfolderdata = [ 
        (foldersnext+"/"+mysubfolder),
        sfname,
        sfdate,
        sfsize,
        sfurl,
        sfid,
        sfdesc,
        sftype
      ];
      //Logger.log("subfolderdata = "+subfolderdata);
      sheet.appendRow(subfolderdata);
    }
  }
}

My post wouldn't go trough because my post is mostly code and i need to post "more details". But there is no more details to add really.

So instead of drawing unicorns all over the post i'm gonna do this.

Ignore below

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Vel quam elementum pulvinar etiam non quam. Eu mi bibendum neque egestas. Tempor nec feugiat nisl pretium. Vulputate enim nulla aliquet porttitor. In dictum non consectetur a erat nam at lectus urna. Suspendisse sed nisi lacus sed viverra tellus in hac. Ipsum consequat nisl vel pretium lectus quam id. Sit amet tellus cras adipiscing enim eu. Sapien et ligula ullamcorper malesuada. Quis vel eros donec ac. Aenean sed adipiscing diam donec adipiscing tristique risus nec. Quis eleifend quam adipiscing vitae proin sagittis nisl. Nunc eget lorem dolor sed viverra ipsum nunc aliquet.

Eget felis eget nunc lobortis mattis aliquam faucibus. Gravida in fermentum et sollicitudin. Nisl purus in mollis nunc sed. Diam volutpat commodo sed egestas egestas fringilla phasellus. Nunc sed velit dignissim sodales. Egestas integer eget aliquet nibh praesent tristique. Sit amet cursus sit amet dictum sit. Et ligula ullamcorper malesuada proin libero nunc. Suspendisse potenti nullam ac tortor vitae purus faucibus ornare. Condimentum lacinia quis vel eros donec ac odio tempor orci. Aliquam eleifend mi in nulla posuere sollicitudin. Dictum at tempor commodo ullamcorper a lacus vestibulum. Placerat orci nulla pellentesque dignissim. Montes nascetur ridiculus mus mauris vitae. Lacus suspendisse faucibus interdum posuere lorem ipsum dolor. Maecenas pharetra convallis posuere morbi leo urna molestie at elementum. Massa ultricies mi quis hendrerit dolor magna. Et sollicitudin ac orci phasellus. Ut placerat orci nulla pellentesque dignissim enim sit amet venenatis.

Answer 1

Just use link this:

var tingkatanSubfolder = 0;

function Sementara() {
folderUtama = DriveApp.getFolderById(""PUT HERE THE ID OF MAIN FOLDER"");
MendaftarSubfolder(folderUtama);
}

function MendaftarSubfolder(foldernya) {
  var folders = foldernya.getFolders();
  while (folders.hasNext()) {
    var folder = folders.next();
    Logger.log(" ".repeat(tingkatanSubfolder)+"|"+"_".repeat(tingkatanSubfolder)+folder.getName()+": folder");
    if(folder.getFolders().hasNext()) {
      tingkatanSubfolder +=1;
      MendaftarSubfolder(folder);
    }
  }
  if(tingkatanSubfolder>0){tingkatanSubfolder -= 1}
}

Answer 2

New Version with improvements:

The previous version requires 510 seconds to list the same 1500 files, this version only needs 170 seconds to do it. List of improvements:

1. This version sorts the files by name so that they are arranged in the list (inside each folder).

2. Reports the number of files found, next to the folder name (it is used to verify that all the files appear)

3. The lines are recorded in blocks of configurable quantity. So as not to waste time recording one by one.

4. We force the refreshment on the screen so that the lines are seen as they are recorded in the form, otherwise we have to wait until the process ends.

---

function ListarTodo() {
  /* Adapted from Code written by @Andres Duarte in this link:
    https://stackoverflow.com/questions/59045664/how-to-list-also-files-inside-subfolders-in-google-drive/63182864#63182864
  */

  // Lista todos los archivos de una carpeta y de sus sub carpetas, y toma el nombre de la carpeta a analizar del nombre de la hoja activa.
  // List all files and sub-folders in a single folder on Google Drive, and get the name of the activesheet to know the folder desired.
  var foldername = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

  // Declaramos la hoja // declare this sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // Borramos los datos de la hoja // clear any existing contents
  sheet.clear();
  // Agregamos una linea con los titulos // append a header row
  sheet.appendRow(["Carpeta","Nombre Archivo", "Fecha ultima modificacion", "Tamaño MB", "URL", "ID", "Descripción", "Tipo archivo"]);

  // getFoldersByName = obtener una coleccion de todas las carpetas en la unidad Drive que tienen el nombre buscado "foldername".
  // folders es un "iterador de carpetas" pero hay solo una carpeta para llamar por el nombre, por eso tiene un solo valor (next)
  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  // folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
  var folders = DriveApp.getFoldersByName(foldername);
  var foldersnext = folders.next();
  var lintotal = 2;

  //Iniciamos la funcion recursiva // Initiate recursive function
  lintotal = SubCarpetas(foldersnext, foldername, lintotal);  
}

function SubCarpetas(folder, path, cantlineas) {
  cantlineas = ListarArchivos(folder, path, cantlineas);
  var subfolders = folder.getFolders();

  while (subfolders.hasNext()) {
    var mysubfolders = subfolders.next();
    var mysubfolderName = mysubfolders.getName(); 
    var newpath = "";
    newpath = path + "/" + mysubfolderName;
    cantlineas = SubCarpetas(mysubfolders, newpath, cantlineas);
  }
  return(cantlineas) 
}

function ListarArchivos(mifoldersnext, mipath, milintotal) {
  var datos = []; //array temporal que vamos a usar para grabar en la hoja
  var files = []; //array con todos los archivos que encontramos en la carpeta que estamos evaluando
  var file = []; //array que usamos para volcar los datos de cada archivo antes de guardarlo
  var total = 0;
  var sheet = SpreadsheetApp.getActiveSheet();
  var myfiles = mifoldersnext.getFiles();

// Creamos un array con los datos de cada archivo y guardamos el total de archivos
while (myfiles.hasNext()) {
    files.push(myfiles.next());
    total++;
}
//ordenamos el array por nombre de archivo alfabeticamente  //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);
});

////
var vuelta = 0;
var bulk = 10; //Definimos la cantidad de lineas a grabar cada vez, en la hoja de la planilla GoogleDoc
var linea = milintotal; //definimos en que linea vamos a grabar en la planilla
for (var i = 0; i < files.length; i++) { //recorremos el array de archivos y formateamos la informacion que necesitamos para nuestra planilla
    file = files[i];
    var fname = file.getName(); //nombre del archivo
    var fdate = file.getLastUpdated(); //fecha y hora ultima modificacion
    var fsize = file.getSize()/1024/1024; //tamaño del archivo, lo pasamos de byte a Kbyte y luego a Mb
    fsize = +fsize.toFixed(2); //lo formateamos a dos decimales
    var furl = file.getUrl(); //url del archivo
    var fid = file.getId(); //id del archivo
    var fdesc = file.getDescription(); //descripcion
    var ftype = file.getMimeType(); //tipo de archivo
    datos[vuelta] = [mipath+" ("+total+")", fname, fdate, fsize, furl, fid, fdesc, ftype]; //ponemos todo dentro de un array temporal
    vuelta++;
    if (vuelta == bulk) {//cuando alcanza la cantidad definida, guarda este array con 10 lineas y lo vacía
      linea = milintotal;
//      Logger.log("linea = "+linea); //DEBUG
//      Logger.log("vuelta = "+vuelta); //DEBUG
//      Logger.log("total = "+total); //DEBUG
//      Logger.log("lintotal = "+milintotal); //DEBUG
//      Logger.log("registros en datos = "+datos.length); //DEBUG
//      Logger.log("data = "+datos); //DEBUG
      sheet.getRange(linea, 1, bulk,8).setValues(datos); //guardamos los datos del array temporal en la hoja
      SpreadsheetApp.flush(); //forzamos que aparezcan los datos en la hoja - sin esto los datos no aparecen hasta terminar (genera mucha impaciencia)
      milintotal = milintotal + vuelta;
      datos = []; //vaciamos el array temporal
      vuelta = 0;
      }
    }

if (datos.length>0) {//Al salir del bucle grabamos lo que haya quedado en el array datos
      linea = milintotal;
//      Logger.log("linea = "+linea); //DEBUG
//      Logger.log("vuelta = "+vuelta); //DEBUG
//      Logger.log("total = "+total); //DEBUG
//      Logger.log("lintotal = "+milintotal); //DEBUG
//      Logger.log("registros en datos = "+datos.length); //DEBUG
//      Logger.log("data = "+datos); //DEBUG
      sheet.getRange(linea, 1, datos.length,8).setValues(datos);
      SpreadsheetApp.flush(); //ansiolítico
      milintotal = milintotal + datos.length;
      datos = [];
      vuelta = 0;
    }
return (milintotal)
}

Answer 3

This script, is based on post of Andres Duarte. have solutioned the correct path of sub-folders.

This script to know which folder you need analyze, reads the name of the ActiveSheet

function ListNamedFilesandFolders() {
  /* Adapted from Code written by @hubgit https://gist.github.com/hubgit/3755293
  Updated since DocsList is deprecated  https://ctrlq.org/code/19854-list-files-in-google-drive-folder
  */

  // List all files and sub-folders in a single folder on Google Drive
  // declare the folder name
  //var foldername = 'FolderName';
  var foldername = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

  // declare this sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // clear any existing contents
  sheet.clear();
  // append a header row
  sheet.appendRow(["Folder","Name", "Date Last Updated", "Size MB", "URL", "ID", "Description", "Type"]);

  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  // folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
  var folders = DriveApp.getFoldersByName(foldername);
  var foldersnext = folders.next();
  // Logger.log("THE FOLDER IS "+foldersnext);// DEBUG

  //Initiate recursive function
  listFilesAndSubfolders(foldersnext, foldername);  
}

function listFilesAndSubfolders(folder, path) {
  listFiles(folder, path);
  var subfolders = folder.getFolders();

  while (subfolders.hasNext()) {
    var mysubfolders = subfolders.next();
    var mysubfolderName = mysubfolders.getName(); 
    var newpath = "";
    newpath = path + "/" + mysubfolderName;
    listFilesAndSubfolders(mysubfolders, newpath);
  }
}

function listFiles(foldersnext, path) {
  var data = [];
  var sheet = SpreadsheetApp.getActiveSheet();

  // list files in this folder
  // myfiles is a File Iterator
  var myfiles = foldersnext.getFiles();
  // Logger.log("FILES IN THIS FOLDER"); DEBUG

  // loop through files in this folder
  while (myfiles.hasNext()) {
    var myfile = myfiles.next();
    var fname = myfile.getName();
    var fdate = myfile.getLastUpdated(); 
    var fsize = myfile.getSize()/1024/1024;
    fsize = +fsize.toFixed(2);
    var furl = myfile.getUrl();
    var fid = myfile.getId();
    var fdesc = myfile.getDescription();
    var ftype = myfile.getMimeType();
    //Logger.log("File Name is "+myfile.getName()); //Logger.log("Date is "+myfile.getLastUpdated()); //Logger.log("Size is "+myfile.getSize());
    //Logger.log("URL is "+myfile.getUrl()); //Logger.log("ID is "+myfile.getId()); //Logger.log("Description is "+myfile.getDescription());
    //Logger.log("File Type is "+myfile.getMimeType());

    // Populate the array for this file
    data = [ 
      path,
      fname,
      fdate,
      fsize,
      furl,
      fid,
      fdesc,
      ftype
    ];
    //Logger.log("data = "+data); //DEBUG
    sheet.appendRow(data);
  } // Completes listing of the files in the named folder

}

Answer 4

You need to call a function recursively. I tweak your code into 3 functions ListNamedFilesandFolders() to initiate the recursively function which is listFilesAndSubfolders() which calls listFiles() for every folder to list the files. I just needed to use getFolders() [1] function:

function ListNamedFilesandFolders() {
  /* Adapted from Code written by @hubgit https://gist.github.com/hubgit/3755293
  Updated since DocsList is deprecated  https://ctrlq.org/code/19854-list-files-in-google-drive-folder
  */

  // List all files and sub-folders in a single folder on Google Drive
  // declare the folder name
  var foldername = 'FolderName';

  // declare this sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // clear any existing contents
  sheet.clear();
  // append a header row
  sheet.appendRow(["Folder","Name", "Date Last Updated", "Size", "URL", "ID", "Description", "Type"]);

  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  // folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
  var folders = DriveApp.getFoldersByName(foldername);
  var foldersnext = folders.next();
  // Logger.log("THE FOLDER IS "+foldersnext);// DEBUG

  //Initiate recursive function
  listFilesAndSubfolders(foldersnext, foldername);  
}

function listFilesAndSubfolders(folder, path) {
  listFiles(folder, path);
  var subfolders = folder.getFolders();

  while (subfolders.hasNext()) {
    var mysubfolders = subfolders.next();
    var mysubfolderName = mysubfolders.getName(); 
    path = path + "/" + mysubfolderName;
    listFilesAndSubfolders(mysubfolders, path);    
  }
}

function listFiles(foldersnext, path) {
  var data = [];
  var sheet = SpreadsheetApp.getActiveSheet();

  // list files in this folder
  // myfiles is a File Iterator
  var myfiles = foldersnext.getFiles();
  // Logger.log("FILES IN THIS FOLDER"); DEBUG

  // loop through files in this folder
  while (myfiles.hasNext()) {
    var myfile = myfiles.next();
    var fname = myfile.getName();
    var fdate = myfile.getLastUpdated(); 
    var fsize = myfile.getSize();
    var furl = myfile.getUrl();
    var fid = myfile.getId();
    var fdesc = myfile.getDescription();
    var ftype = myfile.getMimeType();
    //Logger.log("File Name is "+myfile.getName()); //Logger.log("Date is "+myfile.getLastUpdated()); //Logger.log("Size is "+myfile.getSize());
    //Logger.log("URL is "+myfile.getUrl()); //Logger.log("ID is "+myfile.getId()); //Logger.log("Description is "+myfile.getDescription());
    //Logger.log("File Type is "+myfile.getMimeType());

    // Populate the array for this file
    data = [ 
      path,
      fname,
      fdate,
      fsize,
      furl,
      fid,
      fdesc,
      ftype
    ];
    //Logger.log("data = "+data); //DEBUG
    sheet.appendRow(data);
  } // Completes listing of the files in the named folder

}

I wasn't able to find a way to get the correct path for each file when there are more than one sub-folder.

[1] https://developers.google.com/apps-script/reference/drive/drive-app#getfolders