google-apps-scriptgoogle-drive-api

List google drive folder contents where folder is name is based on cell value [solved]


I'm using the original code borrowed from this post to list the contents of a specified folder from drive.

List Google drive folder contents to google sheets with only new files

function wthFolderContents() {
  var folder_id = 'myFolderID';
   
  var folders = DriveApp.getFolderById(folder_id)
  var contents = folders.getFiles();
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("SheetName");
  sheet.clearContents()
  sheet.appendRow( ['name', 'link'] );
  
  var file;
  var name;
  var link;
  var row;
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow ( [name, link] );

What I need is this variable here to be based on the cell value in A1:

 var folder_id = 'myFolderID';

I either need to retrieve the folder ID in another cell based on the value of cell A1 and have the script reference that cell, or have the code dynamically lookup the folder ID based on cell A1.

Any help greatly appreciated.


Solution

  • Here is the solution to my specific problem. When the script is run, it displays every file in the folder 'id here'. I then just run a simple query on colA (JOB#) to filter just the file names I require.

    function getAndListFilesInFolder() {
      var arr,f,file,folderName,subFolders,id,mainFolder,name,own,sh,thisSubFolder,url;
    
      sh = SpreadsheetApp.getActive().getSheetByName('google_drive');
      sh.getRange(1, 1, 100, 10).clear({contentsOnly: true})
    
      id = "id here";
      arr = [["JOB#","DOCUMENT NAME", "URLs", "OWNER"]];
    
      mainFolder = DriveApp.getFolderById(id);
      subFolders = mainFolder.getFolders();
      folderName = mainFolder.getName();
    
      f = mainFolder.getFiles();
    
      while (f.hasNext()) {
        file = f.next();
        name = file.getName()
        url = file.getUrl()
        own = file.getOwner().getName()
    
        arr.push([folderName, name, url, own]);
      };
    
      while (subFolders.hasNext()) {
        thisSubFolder = subFolders.next();
        f = thisSubFolder.getFiles();
        folderName = thisSubFolder.getName();
    
        while (f.hasNext()) {
          file = f.next();
          name = file.getName()
          url = file.getUrl()
          own = file.getOwner().getName()
    
          arr.push([folderName,name, url, own]);  
        };
      };
    
      sh.getRange(1,1, arr.length, arr[0].length).setValues(arr);
      sh.getRange(2,1, arr.length, arr[0].length).sort(1);
    }