How to Make a list on Google SpreadSheet of all files in a Google Drive folder

Question

I have a Folder With more than 2000 Files.. And i Need to make a List of all these files on google spreadsheet.. I Found online some Scripts.. but they're not completely working. When i hit "RUN" i just get a list of 250 Files.

Reading on Google Developers page i found some things about enabling Google Drive advanced services (and i did it).. And i think i could solve this problem using something named "tokens"??

I don't know.. i'm not a programmer, and i barely know english.. i Tried editing this script making a fusion of what i found online.. But anything works.. i just get errors that i can't even understand..

So.. is there someone able to fix it?

function listFilesInFolder() {
  var folder = DocsList.getFolder("Film");
  var contents = folder.getFiles();
  var file;
  var data;

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();

  sheet.appendRow(["Nome", "Data", "Dimensione"]);

  for (var i = 0; i < contents.length; i++) {
    file = contents[i];

    if (file.getFileType() == "SPREADSHEET") {
      continue;
    }

    data = [ 
      file.getName(),
      file.getDateCreated(),
      file.getSize(),
    ];

    sheet.appendRow(data);
  }
};

Solution 1

This Script works for at least 2200 Files :)

function listFilesInFolder(id) {
  var folder = DriveApp.getFolderById('MyFolderID');
  var contents = folder.getFiles();
  var file;
  var name;
  var sheet = SpreadsheetApp.getActiveSheet();
  var date;
  var size;

  sheet.clear();
  sheet.appendRow(["Nome", "Data", "Dimensione"]);


  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    date = file.getDateCreated()
    size = file.getSize()

    data = [name, date, size]
    sheet.appendRow(data);
  }
};

Solution 2

The answer above appends a row in every iteration wich is particularly slow and there is a chance you will exceed the maximum execution time (see best practices)) so here is a version that uses an array to collect data and writes the array using a single setValues() .

The other issue is that it gets all the files in your drive, not in the folder you chose...

so below is a version that gets all files that are not Google documents, ie it counts only files that take space (images, pdf...) with a maximum of 4000 files.

full code below :

function listFilesInFolder() {
  var folder = DocsList.getFolderById('0B3qSFd3iikE3MS0yMzU4YjQ4NC04NjQxLTQyYmEtYTExNC1lMWVhNTZiMjlhMmI');
  var file;
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  var data = [];
  data.push(["Name", "Data", "Size", "url"]);
  var filesresult = folder.getFilesByTypeForPaging(DocsList.FileType.OTHER, 4000);
  var files = filesresult.getFiles();
  Logger.log(files.length);
  for (var i in files) {
    file = files[i];
    data.push([ 
      file.getName(),
      file.getDateCreated(),
      file.getSize(),
      file.getUrl()
    ]);
  }
  sheet.getRange(1,1,data.length,data[0].length).setValues(data);   
}

Solution 3

Paging is what you're looking for. When you have a large number of results (like 2000 files), you generally divide the request into 'pages', either to show the user page by page or in this case, to stay within the API limits.

The 'token' isn't a big deal.. it's just how your script remembers the page number while it's dealing with the current page.

So there's information about this here: https://developers.google.com/apps-script/reference/docs-list/files-result

The script at the top of the page is quite apt to your situation. Your script becomes something like...

function listFilesInFolder() {
  var folder = DocsList.getFolder("Film");
  //var contents = folder.getFiles();
  var file;
  //var data;

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();

  sheet.appendRow(["Nome", "Data", "Dimensione"]);


 var pageSize = 200;
 var files = null;
 var token = null; // use a null token for the first lookup
 do {
   var result = DocsList.getAllFilesForPaging(pageSize, token);
   files = result.getFiles();
   token = result.getToken();
   for (var i = 0; i < files.length; i++) {
    //Logger.log(files[i].getName());
    file = files[i];
    if (file.getFileType() == "SPREADSHEET") {
      continue;
    }
    data = [ 
      file.getName(),
      file.getDateCreated(),
      file.getSize(),
    ];

    sheet.appendRow(data);
   }
 } while (files.length >= pageSize);

};

I'm not promising this will work.. but I'm sure you can sort it out. Basically the "while loop" on that page replaces the "for loop" from your script. The loop on that page just calls Logger.log(), so you swap that with the sheet.appendRow(data)

Comments

[Serge insas]

this is getting files from the whole drive, not from a specific folder.

[Admin]

Ty Guys, but none of the above scripts work.. Even changing a little the first one i just get a list of >3000 Files with duplicates of the first 250. The second one works better, but i just get a list of 499 files :-/ Anyway, i found a way to get over it. Ther's a sample script on GoogleAppScripts Page that do it.. but save everything in the Log.. I just changed it a little bit: