Get a list of Drive files into a spreadsheet

Question

I am playing around with AwesomeTable and like the Post-It template. My use case is to curate a nice interface to a list of frequently used files. The list won't change dramatically over time, but it will change weekly. I'd love to be able to automatically generate the list of files (via tags, names, last modified date, etc.) instead of manually adding them. Is there a way for the underlying Google Sheet to query Google Drive to get a list of Drive files into the Google Sheet that Awesome-Tables uses?

Answer 1

It sounds like Files Cabinet is what you're looking for. It's a free add-on to Awesome Table.

To quote their blurb, Files Cabinet "helps you list a Google Drive folder. It explores every child folder and lists every file that can be found. After giving you a quick count of all the folders and all the files, you get a list of the files that are ready to be displayed in a catalog created with Awesome Table."

For those, like myself, who may live down a coal mine and were previously ignorant of these things, Awesome Table is an extremely popular add-on (10+ million users) for Google spreadsheets that enables users to create visualisations of spreadsheet data with little or no coding; the visualisations can be accessed via Google spreadsheet or can be embedded in a web page. At the time of writing, it is "*free for up to 1000 pageviews / month".

Answer 2

It's true - "Files Cabinet", the add-on for "Awesome Tables" does help list a Google Drive folder. But it occurs to me that some people looking at this question and answer may be trying to create a list of folders and files using just the Google Sheets script. This code is written for them.

The following code accepts a Google Drive folder name as a variable ('foldername' on line 9; just edit the folder name to suit). It will list all the files, sub-folders and their files in/under the named folder.

As you can see, the code is not strictly original, and I have given credit to those who originally developed the code that I further developed.

Note: it is possible to create a list of all the files, sub-folders and their files held on Google Drive. The difference between the code to do this, and the code shown below is very subtle. The same command getFolders is used; the difference is whether the command is called from a named folder, as in this example, or from the DriveApp (var files=DriveApp.getFiles();). You have been warned ;)

function ListNamedFilesandFolders() {
/* Adapted from Code written by @hubgit https://gist.github.com/hubgit/3755293
Updated since DocsList is deprecated  https://ctrlq.org/code/19854-list-files-in-google-drive-folder
*/

  
  // List all files and sub-folders in a single folder on Google Drive
  // declare the folder name
  var foldername = 'myfoldername';
  
  // declare this sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // clear any existing contents
  sheet.clear();
  // append a header row
  sheet.appendRow(["Folder","Name", "Date Last Updated", "Size", "URL", "ID", "Description", "Type"]);
  
  
  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  // folders is a "Folder Iterator" but there is only one unique folder name called, so it has only one value (next)
  var folders = DriveApp.getFoldersByName(foldername);
  var foldersnext = folders.next();
  // Logger.log("THE FOLDER IS "+foldersnext);// DEBUG
  
  // declare an array to push data into the spreadsheet
  var data = [];

  // list files in this folder
  // myfiles is a File Iterator
  var myfiles = foldersnext.getFiles();
  
  // Logger.log("FILES IN THIS FOLDER"); // DEBUG

  // loop through files in this folder
  while (myfiles.hasNext()) {
    var myfile = myfiles.next();
    var fname = myfile.getName();
    var fdate = myfile.getLastUpdated(); 
    var fsize = myfile.getSize();
    var furl = myfile.getUrl();
    var fid = myfile.getId();
    var fdesc = myfile.getDescription();
    var ftype = myfile.getMimeType();
    //Logger.log("File Name is "+myfile.getName()); //Logger.log("Date is "+myfile.getLastUpdated()); //Logger.log("Size is "+myfile.getSize());
    //Logger.log("URL is "+myfile.getUrl()); //Logger.log("ID is "+myfile.getId()); //Logger.log("Description is "+myfile.getDescription());
    //Logger.log("File Type is "+myfile.getMimeType());
    
    // Populate the array for this file
    data = [ 
      foldersnext,
      fname,
      fdate,
      fsize,
      furl,
      fid,
      fdesc,
      ftype
    ];
    //Logger.log("data = "+data); //DEBUG
    sheet.appendRow(data);
  } // Completes listing of the files in the named folder
  
  // Now get the subfolder
  // subfolders is a Folder Iterator
  var subfolders = foldersnext.getFolders();
  //Logger.log("THE SUBFOLDER(S) ARE"); DEBUG HEADING
  
  // now start a loop on the SubFolder list
  while (subfolders.hasNext()) {
    var subfolderdata = [];
    var mysubfolders = subfolders.next();
    var mysubfolder = mysubfolders.getName();  
    //Logger.log("Subfolder name:"+mysubfolder); //DEBUG
   
    // Get the files
    // mysubfiles is a File Iterator
    var mysubfiles = mysubfolders.getFiles();
    //Logger.log("FILES IN THIS FOLDER"); //DEBUG HEADING

    // now start a loop on the files in the subfolder
    while (mysubfiles.hasNext()) {
      var smyfile = mysubfiles.next();
      var sfname =  smyfile.getName();
      var sfdate =  smyfile.getLastUpdated(); 
      var sfsize =  smyfile.getSize();
      var sfurl =  smyfile.getUrl();
      var sfid =  smyfile.getId();
      var sfdesc =  smyfile.getDescription();
      var sftype =  smyfile.getMimeType();
      //Logger.log("Subfolder is "+foldersnext+"/"+mysubfolder); 
      //Logger.log("File Name is "+ smyfile.getName()); Logger.log("Date is "+ smyfile.getLastUpdated()); Logger.log("Size is "+ smyfile.getSize());
      //Logger.log("URL is "+ smyfile.getUrl()); Logger.log("ID is "+ smyfile.getId()); Logger.log("Description is "+ smyfile.getDescription());Logger.log("File Type is "+ smyfile.getMimeType());
      subfolderdata = [ 
        (foldersnext+"/"+mysubfolder),
        sfname,
        sfdate,
        sfsize,
        sfurl,
        sfid,
        sfdesc,
        sftype
      ];
      //Logger.log("subfolderdata = "+subfolderdata);
      sheet.appendRow(subfolderdata);
    }
  }
}

---

FWIW, this is the how the spreadsheet looks (less my private information) when the code is run.