Google Script App list drive folder in Sheets

1
votes

I'm trying to copy to steps in https://ctrlq.org/code/19854-list-files-in-google-drive-folder

It successfully made the headers for the columns in the sheet, but it didn't get/print any data in the rows. Where did I go wrong?

This is what I have:

/* Code written by @hubgit 
   https://gist.github.com/hubgit/3755293
   Updated since DocsList is deprecated 
*/

function listFilesInFolder(folderName) {

  var folder = DriveApp.getFoldersByName(folderName).next();
  var contents = folder.getFiles();

  var file, data, sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();

  sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Type"]);

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

    file = contents[i];

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

    data = [ 
      file.getName(),
      file.getDateCreated(),
      file.getSize(),
      file.getUrl(),
      "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
      file.getDescription(),
      file.getFileType().toString()
    ];

    sheet.appendRow(data);

  }

};
2
google-apps-scriptgoogle-sheetsgoogle-drive-api
You'll need to debug the code. Is the folderName being received by the listFilesInFolder function? Add this line of code at the very top of the function: Logger.log('folderName: ' + folderName);. Run the code, then in the VIEW menu, choose LOGS. Is the correct folder name printed in the Log? – Alan Wells
Thanks @SandyGood. I just got this new error when running it with your added part. Cannot retrieve the next object: iterator has reached the end. (line 8, ... – rin
Every time I try to find a new solution, an new error comes up. If you use docList, it's depreciated. If I use any script with while(contents.hasNext()) { , it creates a server error. Even the tutorials on google references is outdated. ;( – rin
I assume that you are getting the error from this line: var folder = DriveApp.getFoldersByName(folderName).next(); Remove next() from the end, so that you just have: var folder = DriveApp.getFoldersByName(folderName); Then add Logger.log('folder: ' + folder); after the modified line. Check the Logs. It should show a folder iterator or an object. If there are no folders with that name found, then next() won't work. There is no "next" folder. – Alan Wells
To test the code, you can "hard code" a value for folder: Add a line: var folder = "my Folder Name Here"; You can also run the debugger. Choose the name of the function that is in the drop down list to the right of the bug icon, click on line number 10 so that a red dot shows up on the line number on the left. Then click the icon of the bug. All the variable values with their values will appear at the bottom of the window. You can then execute each line one at a time by clicking the icon just to the right of the big square. – Alan Wells

2 Answers

2
votes

You need to tailor the code according to your need. Firstly in ListFilesInFolder(folderName) nothing passed as such so remove the folderName part. Then try for a particular folder name instead. Next, if we don't want to confine to Spreadsheets. We need to remove the lines:

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

There is , in fact, no need for a for loop contents.hasNext() will be sufficient. So the function will ultimately be something like this. It is working.

function listFilesInFolder() {

  var folder = DriveApp.getFoldersByName('SRL').next();
  Logger.log(folder);
  var contents = folder.getFiles();
  Logger.log(contents);

  var file, data, sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();

  sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Type"]);


    if (contents.hasNext()) {

      file = contents.next();

      Logger.log(file);

      data = [ 
      file.getName(),
      file.getDateCreated(),
      file.getSize(),
      file.getUrl(),
      "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
      file.getDescription(),

    ];

    sheet.appendRow(data);


 }

};
1
votes

The following works if you hardcode the folder ID in.

There's also a deeper discussion here.

function listFilesInFolder(folderName) {

   var sheet = SpreadsheetApp.getActiveSheet();
   sheet.appendRow(["Name", "Date", "Size", "URL", "Download", "Description", "Type"]);

    var folder = DriveApp.getFolderById("PUT_YOUR_FOLDERID_IN_HERE");
    //Logger.log(folder);
    var contents = folder.getFiles();

    var cnt = 0;
    var file;

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

        Logger.log(file);
        Logger.log(cnt);

            data = [
                file.getName(),
                file.getDateCreated(),
                file.getSize(),
                file.getUrl(),
                "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
                file.getDescription(),
                file.getMimeType(),
            ];

            sheet.appendRow(data);



    };
};