Fetch file names, Owner name, URL and last modified date of all files within a Master Google Drive f-CodePudding

I am trying to bring in the file details from a google drive folder. It has many subfolders within and I want the details of all those files as well. I have been playing with a code and it partially worked. I can see all the file names in "Execution" but not everything gets added to my Output in Google sheet.

Execution log (Here "Copy of" file names are the ones that are in subfolders)

Sheet Output (Have Date modified and Owner name to the left )

Not sure what's causing this issue. Below is the code that I use.

function getFileNames(folder) {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet2");

  
  var files = folder.getFiles();

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

    sheet.getRange(i 1, 1, 1, 4).setValues([[file.getLastUpdated(), file.getOwner().getName(), file.getName(), file.getUrl()]]);
    i  ;
  }
  
  var subFolders = folder.getFolders();
  
  
  while (subFolders.hasNext()) {
    var subFolder = subFolders.next();
    
    
    getFileNames(subFolder);
  }
}


var folder = DriveApp.getFolderById('FOLDER_ID');
getFileNames(folder);

CodePudding user response：

Modification points:

In your script, by var i = 1;, sheet.getRange(i 1, 1, 1, 4).setValues([[file.getLastUpdated(), file.getOwner().getName(), file.getName(), file.getUrl()]]); is always run from the 1st row. I thought that this might be the reason for your issue of I can see all the file names in "Execution" but not everything gets added to my Output in Google sheet..
When setValues is used in a loop, the process cost will become high. Ref

When these points are reflected in your script, how about the following modification?

Modified script:

In this case, please run main.

function getFileNames(folder, values = []) {
  if (folder) {
    var files = folder.getFiles();
    while (files.hasNext()) {
      var file = files.next();
      values.push([file.getLastUpdated(), file.getOwner().getName(), file.getName(), file.getUrl()])
    }
    var subFolders = folder.getFolders();
    while (subFolders.hasNext()) {
      var subFolder = subFolders.next();
      getFileNames(subFolder, values);
    }
  }
  return values;
}

function main() {
  var folder = DriveApp.getFolderById('FOLDER_ID');
  var values = getFileNames(folder);
  if (values.length > 0) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  } else {
    console.log("No values are returned.")
  }
}