0

I need to get a list of all the files in my Drive with all of the nested folder names where the files reside.

I found this very helpful answer which is almost exactly what I need: Get a list of Drive files into a spreadsheet

However, the code only gathers the files one subfolder level down. I know I need to add an additional loop but my coding skills are very rusty.

The code from the linked answer I referenced is below for convenience.

If someone could point me in the right direction it would be greatly appreciated! I just need to understand where the loop (i'm assuming a while loop is best?) should go. Also, what terminator should I use to end the new loop? I see that both the subFolders and subFiles loops use .hasNext, is that the best for this additional loop?

Sorry in advance, I know this is probably a pretty simple thing I am asking here but it has been a looooong time since I have done any coding!

Code:

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 = 'Customer';

  // 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);
    }
  }
}
2
  • Is this thread useful for your situation? stackoverflow.com/q/54242466 – Tanaike Jul 30 '19 at 2:22
  • I did this on Windows by syncing my files to my computer with Backup and Sync, then opening the command prompt changing the directory with the command cd, and typing tree /a > tree.txt, where "tree.txt" is the file that shows my folder hierarchy. I know you're looking for the cloud solution, but I'm just mentioning this for the record. – ahorn Aug 2 '19 at 15:56
0

I found a script that worked great for this here:

https://gist.github.com/mesgarpour/07317e81e9ee2b3f1699

(in case anyone else needs something similar)

0

This code does exactly that: brings the list of all Gdrive files and folders (starting from a folder to specify at the top of the code) into a Google sheet (same original code as yours):

https://github.com/bluexm/snipets/blob/master/list%20Gdrive%20files%20and%20folders

/* 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

Bluexm: added recursion on subfolders */

// List all files and sub-folders in a folder and subfolders Google Drive
// declare the starting folder name
var foldername = '20200424';


// entry function 
function ListNamedFilesandFolders(){
  ListNamedFilesandFolders1F(foldername,'')
}


function ListNamedFilesandFolders1F(fn,rfn) {

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


  // declare this sheet
  var sheet = SpreadsheetApp.getActiveSheet();

  if (foldername==fn){
    // clear any existing content
    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(fn);
  var foldersnext = folders.next();
  Logger.log("THE FOLDER IS "+foldersnext);// DEBUG

  // 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 = [ 
      rfn+'/'+ 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

    ListNamedFilesandFolders1F(mysubfolder,rfn +'/'+ fn)

  }
}
1
  • That's great. But it wouldn't work if the folder is shared and requires sign in. You need to share it as view then you can get the IDs – Hassan Almaateeq Jan 27 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.