0

I have a gscript that will return all the files in a folder adn write to the open spreadsheet. I would like to add the files in all the subfolders as well but can't quite make it work. Here is the script for the files in the folder with the contents of the subfolders:

function getFiles() {

  // Get the active spreadsheet file and the active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssid = ss.getId();

  // Look in the current folder e.g. if this spreadsheet is in 'My Folder'
  // this routine will return all of the files in 'My Folder'.
  var ssparents = DriveApp.getFileById(ssid).getParents();
  var sheet = ss.getActiveSheet();

  // Clear the area, add the headers ready for results
  var headers = [["Full Folder Ref", "File Name", "Last Updated", "File Owner", "File URL", "In Folder"]];
  sheet.getRange("A1:F").clear();
  sheet.getRange("A1:F1").setValues(headers);

  // Loop through all the files, add the values to the spreadsheet.
  var folder = ssparents.next();
  var files = folder.getFiles();

  var i=1;
  while(files.hasNext()) {
    var file = files.next();

  // get the full folder ref
  var fileParents = file.getParents()
    folders = [];
    while (fileParents.hasNext()) {
      fileParents = fileParents.next();
      folders.push(fileParents.getName());
      fileParents = fileParents.getParents();
    }

  // create the row
    if(ss.getId() == file.getId()){ 
      continue; 
    }
    sheet.getRange(i+1, 1, 1, 6).setValues([[folders.reverse().join("/") , file.getName(), file.getLastUpdated(),file.getOwner().getName(), file.getUrl(), folder.getName()  ]]);
    i++;
  }
}

Can someone help me add the subfolder files please.

thank you

lizat
  • 3
  • 3
  • Take a look at this: https://stackoverflow.com/a/55248127/7215091 – Cooper Feb 10 '20 at 22:04
  • Does this answer your question? [In Google Apps Script, how would I get all subfolders and all subsubfolders and all subsubsub folders etc.?](https://stackoverflow.com/questions/55243153/in-google-apps-script-how-would-i-get-all-subfolders-and-all-subsubfolders-and) – alberto vielma Feb 11 '20 at 08:40
  • Thank you both, I think it might help but I have to think hard as my coding skills are rusty and I have to incorporate into the code above – lizat Feb 11 '20 at 18:06
  • I have tried to get this working using the examples but can't quite make it work. I know it's cheating but if someone has the time to edit my routine above and show me how it's done I'd be very greatful. Thank you – lizat Feb 11 '20 at 19:03

0 Answers0