0

I'm trying to get a Google Sheet list of file links in folders and subfolders which I'm keeping in Google Drive. It should look something like this:

one main folder with a couple of subfolders inside

I was googling and reading forums for a quite a time now but I managed to find only a few script examples and not all of them are working. I found one script which is not too bad as it brings a list of files with links in one folder. but I would like to get a list of files with links in subfolders. Ideally all folder links and item names would be listed in one cell with a separator "|". one of the best script examples I found so far is:

function listFilesInFolder(folderName) {

var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(["Name", "File-Id"]);


//change the folder ID below to reflect your folder's ID (look in the            URL when you're in your folder)
var folder = DriveApp.getFolderById("0ByQ9Zp2ge-pzSjhKdHVWWk0wNnc");
var contents = folder.getFiles();

var cnt = 0;
var file;

while (contents.hasNext()) {
    var file = contents.next();
    cnt++;

       data = [
            file.getName(),
            file.getId(),
        ];

        sheet.appendRow(data);
    };
};

and it brings results in a sheet

enter image description here

It would be much appreciated if anyone could help to get a better version of this code.

p.s.There's also a few bigger scripts I will need in a future which I'm willing to pay for. please message me if you're interested.

0
// replace Guitar_Score below with the folder for which you want a listing with all subfolders
function listFolderContents() {
  var foldername = 'Guitar_Score';
  // file with this name will be saved in your goolge files
  var folderlisting = 'listing of folder ' + foldername;  
  var ss = SpreadsheetApp.create(folderlisting);
  var sheet = ss.getActiveSheet();
  var folders = DriveApp.getFoldersByName(foldername)
  var folder = folders.next();                  
  //listFilesInFolder(folder, sheet);
  traverseFolder(folder, sheet);
};

function traverseFolder(folder, sheet) { 
  listFilesInFolder(folder, sheet);  
  var subFolders = folder.getFolders();
  while (subFolders.hasNext()) {
    traverseFolder(subFolders.next(), sheet);
  }
}

function listFilesInFolder(folder, sheet) {
  var foldername = folder.getName();
  var contents = folder.getFiles();  
  sheet.appendRow( ['...', foldername] );    
  while(contents.hasNext()) {
    var file = contents.next();
    var name = file.getName();
    var link = file.getUrl();
    sheet.appendRow( [name, link] );     
  } 
}
0

Thanks a lot for this code. It worked precisely the way I wanted it - iterate through a folder and then its subfolders to get the files names and other details and put in on a Spreadsheet. However, can you add a bit of explanation on how this works and role of each individual function in the overall process.

New contributor
ARINDAM MOITRA is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

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.