Google Sheets – Get Name and Link of File Based on Folder ID

google sheetsgoogle-apps-script

I created a script to get the name and link file based on Folder ID. The file name and link are displayed on sheet 'Get_Link'.
enter image description here
File spreadsheet

The script that I wrote like this:

function listFilesInFolder(folderName) {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Get_Link');
//sheet.appendRow(["File Name","URL"]);

var folderID = [];
folderID = SpreadsheetApp.getActive()
                         .getRange('Folder_ID!A2:B')
                         .getValues()
                         .reduce(function(p, c) {
                           if (c[0] !== '' && c[1] !== '') {
                                p.push( DriveApp.getFolderById(c[1]).getFiles() );
                                return p; }
                          }, []);

//var searchTerm = Browser.inputBox("Masukkan Folder ID:");
//var folder = DriveApp.getFolderById(searchTerm);
//var contents = folder.getFiles();

var cnt = 0;
var file;

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

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

        sheet.appendRow(data);
    };
};

But having an error,
enter image description here
how about the correct script?

Best Answer

How about the following modifications? I think that there are 2 modification points in your script.

Modification points:

  • Reason of your error is that folderID becomes undefined.
  • When folderID has values, an error occurs at while (folderID.hasNext()) {. Because folderID is an array.

When above points are reflected to your script, it becomes as follows.

From:

.reduce(function(p, c) {
  if (c[0] !== '' && c[1] !== '') {
       p.push( DriveApp.getFolderById(c[1]).getFiles() );
       return p; }
 }, []);

To:

.reduce(function(p, c) {
  if (c[0] !== '' && c[1] !== '') {
    p.push( DriveApp.getFolderById(c[1]).getFiles() );
  }
  return p; // Modified
}, []);

and

From:

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

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

        sheet.appendRow(data);
    };
};

To:

for (var i = 0; i < folderID.length; i++) { // Added
  while (folderID[i].hasNext()) { // Modified
    var file = folderID[i].next(); // Modified
    cnt++;
    var data = [
      file.getName(),
      file.getUrl(),
    ];
    sheet.appendRow(data);
  }
}

If I misunderstood your question and this was not the result you want, I apologize.

Related Topic