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!

  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?

  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. 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

  11. 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.

  12. 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.

  13. 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.

Leave a comment (will be included in public domain license)

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