List Google Drive Folder File Names and URLs to a Google Sheet

This Google Apps script produces a new Google sheet containing the list of files in a Google Drive folder, along with their URLs on Google Drive. Such a file is helpful for creating a datalinks manifest for importing datalinks into an ortext.

Steps for installing and running the Google Apps script:

  1. Open a new Google Sheet document from the Google account for which you want a Google Drive directory listing.
  2. In the toolbar for the new Google Sheet, go to Tools -> Script editor
  3. Create script for: Script as Web App
  4. A code editor will open. Replace any sample code in the editor with the code below.
  5. Within the inserted code, replace your-folder with the name of folder for which you want a listing.
  6. Save the inserted code; application will ask for a file name.
  7. Run by clicking on the right-pointing triangle in the button bar.
  8. ‘Authorization required ‘ pop up will appear; grant authorization.
  9. A listing of the files in the specified folder, along with URLs for those files, will then appear in your My Drive as a Google Sheet named listing of folder {your-folder}

The Google Apps script code for listing a folder with URLs to a new Google Sheet:

// replace your-folder below with the folder for which you want a listing
function listFolderContents() {
  var foldername = 'your-folder';
  var folderlisting = 'listing of folder ' + foldername;
  
  var folders = DriveApp.getFoldersByName(foldername)
  var folder = folders.next();
  var contents = folder.getFiles();
  
  var ss = SpreadsheetApp.create(folderlisting);
  var sheet = ss.getActiveSheet();
  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] );     
  }  
};
  1. Hello Sir!
    Is it possible to get the list from subfolder? Unfortunately, it doesn’t work this way:
    var foldername = ‘your-folder/subfolder’;

    Thank you in advance!

  2. Thanks for posting. This is very helpful.
    I tried the code and it works perfectly. Only thing I want to modify is every time I run the script it creates new file. Is there a way to creates the list and URL to single google spreadsheet?

  3. As per nfractals, if the new URLs could be put on the same sheet as the first listing, that would be very helpful. I hope it can be done. Thank you!

    1. var sheet = SpreadsheetApp/*.create(‘listing of folder ‘ + NAME)*/.getActiveSheet();
      sheet.appendRow( [‘type’,’getName’, ‘getUrl’, ‘getId’, ‘getOwner’, ‘getMimeType’] );

      function NameOfFolder() {
      FLS(DriveApp.getFoldersByName(‘BhN456′),’folder’);
      }

      function FLS(fls,type) {
      while(fls.hasNext()) {
      var fl = fls.next();
      sheet.appendRow( [type, fl.getName(), fl.getUrl(), fl.getId(), fl.getOwner(), type==’file’?fl.getMimeType():”0″] );
      if(type==’folder’){
      FLS(fl.getFolders(),’folder’);
      FLS(fl.getFiles(),’file’);
      }
      }
      }

  4. Doesn’t work for me. I get the error: Cannot retrieve the next object: iterator has reached the end. (line 7, file “Code”)Dismiss

    1. George, You’ll get this error if you don’t do step 5. And foldername is case sensitive. I got this error with a folder “Music Scores” and foldername=”music scores”. Worked great once I corrected the case.

      1. So I’m a code idiot, but I had the same issue and then I realized that I had created a new folder name (because I thought this was where it was going to put the new file) but it really wanted the existing folder name that I was trying to map. Hope that helps.

    2. same, and when running the function in the sheet, I get the error “you do not have permission to call getFoldersByName (line 6)”

  5. Got the same error Cannot retrieve the next object: iterator has reached the end. (line 7, file “Code”)Dismiss

    Any fixes? Thanks!

  6. It can generate a spreadsheet successfully, but nothing in the list. only has one line with “name” and “link”… what I did wrong?

    1. sorry, just found my needs is to get the folder linkage, not the files in the folder. so the testing folder is empty.. that caused my problem above. The script works now.

      But why can only get the file’s hyperlink, but folder’s? Does it mean the folder is not a file in Google drive?

      1. 😐
        I think the “folders” in Google are just tags, in reality. I ran into this oddity before, too. So, the “folder” info is embedded into the file hyperlink, not it’s own object.

  7. Worked like a charm. Thank you for the step-by-step explanation and guide. I found other scripts online but none of them took the time to explain how to use it and I am very new to this so it was appreciated…
    Really good work friend, thank you for posting, keep up the good work!

  8. I used your script as the basis for a Google App script project which I expanded and reworked as sheet.appendRow is not the most efficient way of writing back to the spreadsheet.
    I ran this against a folder that has 1952 subfolders and it always times out at about 1100 so I decided to take this to the next level utilising Drive Rest API and outputting to a HTML file. Take a look if you too experienced apps script timing out: https://github.com/beatleuk/gdfl

  9. Hi, It was very useful code. I ran the same and it works fine I couldn’t be found anywhere in my drive. I dunno where it is getting saved.

  10. Can this be made to work with a folder that’s been “Shared with me”, rather than one of my own?

    1. I did this. It works, as long as you have access, and the Folder Name is accurate and cased properly. I did run into issues when the folder name had an apostrophe. Not sure there’s a way out of that one.

  11. Hi is it possible to format the sheet or to specify an existing sheet so that the links get output in the format or in the sheet?

    #2) is it possible to filter out sheets being output so only sheets that have a certain value in a cell get listed?

    For example, if I have a list of subscribers and I only want to output the ones having the same cell value as “Active”

    3) Also is there a way to update the same form/sheet that was output with any new sheets everytime is open?

    Thanks it is great btw im just not good at scriptingq

  12. I have a slightly different script that iterates through the main folder through all sub-folders and puts it all in one file. It is actually 2 scripts, the main script, then one that calls the main script with the main folder name.

    function ListFolderContents ( sheet, folder_name ) {

    // Get folder pointer
    var folder = DriveApp.getFoldersByName ( folder_name );
    var current_folder = folder.next();

    // Get files and links in folder
    var file_names = current_folder.getFiles ();
    var file_name;
    var name;
    var link;
    while ( file_names.hasNext () ) {
    file_name = file_names.next ();
    name = file_name.getName ();
    link = file_name.getUrl ();
    sheet.appendRow ( [name, link] );
    }

    // Get sub-folders in folder
    var sub_folder_names = current_folder.getFolders ();
    var sub_folder_name;
    var name;
    while ( sub_folder_names.hasNext () ) {
    sub_folder_name = sub_folder_names.next ();
    name = sub_folder_name.getName ();
    sheet.appendRow ( [name] );
    ListFolderContents ( sheet, sub_folder_name );
    }
    }

    function StartListing () {

    folder_name = (”);

    var output_file_name = ‘Listing Of Folder ‘ + folder_name;

    var ss = SpreadsheetApp.create ( output_file_name );
    var sheet = ss.getActiveSheet ();
    sheet.appendRow ( [‘Name’, ‘Link’] );

    ListFolderContents ( sheet, folder_name );

    }

    1. With the first script i only created a sheet with two named rows.

      WIth this script im getting an error on line 35 var output_file_name= ‘Listing Of Folder’

      any suggestions, Im a very beginner level ( i am jsut trying to get a list of all my files in my google drive ( links are awesome too!)

    2. This appears to be useful but I am not able to get it work. Can you please let me know where to put the actual folder name, will it run as a function output or will have to be run from the google script page.

      1. Yup I got it work successfully.

        Here is the script with the quotes corrected. Also replace folder_name_goes_here variable with your folder name below. Also, make sure on the tool bar that you have “StartListing” selected from the “Select Function” dropdown before you select “Run” icon.

        function ListFolderContents ( sheet, folder_name ) {
        // Get folder pointer
        var folder = DriveApp.getFoldersByName ( folder_name );
        var current_folder = folder.next();
        // Get files and links in folder
        var file_names = current_folder.getFiles ();
        var file_name;
        var name;
        var link;
        while ( file_names.hasNext () ) {
        file_name = file_names.next ();
        name = file_name.getName ();
        link = file_name.getUrl ();
        sheet.appendRow ( [name, link] );
        }

        // Get sub-folders in folder
        var sub_folder_names = current_folder.getFolders ();
        var sub_folder_name;
        var name;
        while ( sub_folder_names.hasNext () ) {
        sub_folder_name = sub_folder_names.next ();
        name = sub_folder_name.getName ();
        sheet.appendRow ( [name] );
        ListFolderContents ( sheet, sub_folder_name );
        }
        }

        function StartListing () {
        folder_name = (‘folder_name_goes_here’);
        var output_file_name = ‘Listing Of Folder ‘ + folder_name;
        var ss = SpreadsheetApp.create ( output_file_name );
        var sheet = ss.getActiveSheet ();
        sheet.appendRow ( [‘Name’, ‘Link’] );
        ListFolderContents ( sheet, folder_name );
        }

      2. I adapted the code above and was able to run this successfully. Replace “Folder Name Here” with the name of the folder you’re assessing. Once you do that, save the file and select the “StartListing” command (next to the bug icon on the toolbar).

        function ListFolderContents ( sheet, folder_name ) {

        // Get folder pointer
        var folder = DriveApp.getFoldersByName ( folder_name );
        var current_folder = folder.next();

        // Get files and links in folder
        var file_names = current_folder.getFiles ();
        var file_name;
        var name;
        var link;
        while ( file_names.hasNext () ) {
        file_name = file_names.next ();
        name = file_name.getName ();
        link = file_name.getUrl ();
        sheet.appendRow ( [name, link] );
        }

        // Get sub-folders in folder
        var sub_folder_names = current_folder.getFolders ();
        var sub_folder_name;
        var name;
        while ( sub_folder_names.hasNext () ) {
        sub_folder_name = sub_folder_names.next ();
        name = sub_folder_name.getName ();
        sheet.appendRow ( [name] );
        ListFolderContents ( sheet, sub_folder_name );
        }
        }

        function StartListing () {

        folder_name = (“Folder Name Here”);

        var output_file_name = ‘Listing Of Folder’ + folder_name;

        var ss = SpreadsheetApp.create ( output_file_name );
        var sheet = ss.getActiveSheet ();
        sheet.appendRow ( [‘Name’, ‘Link’] );

        ListFolderContents ( sheet, folder_name );

        }

  13. I have worked for days trying to create a sheet that list all subfolders out of a subfolder and another sheet that list all files in a subfolder. With a prayer and your instructions, I have finally succeeded! Thank you.

  14. Thank you for this! This helped me dump my progress for a non-profit for which I am doing contract work. They didn’t want to access the files, and this saved me a ton of time as I report progress.

  15. I ran the script and got the following error: “Cannot retrieve the next object: iterator has reached the end. (line 7, file “Code”)

    I replaced ‘your-folder’ with the name of my folder.

    Any idea why some people are able to run it successfully, but it fails for others?”

  16. Is it possible to get a ‘share’ (if all files have permissions set) link rather than a direct link that can only be accessed by the owner?

  17. Thanks for the script!
    First I got the error: Cannot retrieve the next object: iterator has reached the end. (line 7, file “Code”)Dismiss. Then I realized that I should have copied the foldername and not the folder ID. Now it works fine.

  18. This worked great for me!

    I needed a list of files from a folder that was ~shared~ with me (I have read-only permissions). I wasn’t sure where to to find my list once the script ran, but there it was, in my own top-level folder.

    I’m really impressed because I usually manage to make things harder than they need to be! Once I realized I just had to add the folder name (not the url), it ran.

  19. I can’t find a file created with listing. No errors in scripts but file does not seems to be created. 🙁 help plz

  20. Thank you so much! Worked for me! And yes, I did the same mistake of putting the folder ID instead of just folder name! Haha!

  21. I know my limitation of 30 minutes but I have close to 25,000 files. I’m getting an Execution Error after 30 minutes of “Exceeded maximum execution time”. Any suggestion?

    By the way, my knowledge of scrips is limited to Google what I need, copy and paste and following the instructions.

Leave a Reply to Nitesh Dashore Cancel reply

Your email address will not be published. Required fields are marked *