List of All Google Drive Files & Folder in Google Sheet / Microsoft Excel

List of All Google Drive Files & Folder in Google Sheet with Shareable Link and Drive Location with in seconds

 

Make a list of All folders and File Saved on Google Drive within Seconds

Google Drive is a most popular cloud storage service of Google for free to everyone. In this drive we can easily manage save our files and folders which will be easily accessible anytime and anywhere. We can upload, download and delete our files present in Google Drive. We also can find documents, spread sheets, and slides by searching File Title and File Contents, etc.

We also can make a list of all Files and Folders with shareable link saved in Google drive on spread sheet. This is a very helpful for us. This can make convenient to everybody to get all files and folder in a single Microsoft Excel File. After list up all files and folder we can easily find which file and folder is available in our drive. We can share required files and folders to anyone by copying the given link in excel file

In this article we will see STEP WISE how we can make a list of all files and folders saved in Google Drive?

Step Wise Guide

First of all just login your desired account and go to www.drive.google.com

Step 1  Create new Google Sheet:

Create a new Google Spreadsheet file in which we will create a list of all Google Drive files and folders.

Create Google Sheet and name it.

Step 2  Go to Script Editor

In a sheet title bar ,select "Extensions" >> "Apps Script".

 In new Tab Script Editor will open. Remove all text written in Script editor:

Step 3  Copy given Script and past in Script Editor

Now Copy ↓ below script and past in Script Editor: 

function onOpen() {   var SS = SpreadsheetApp.getActiveSpreadsheet();   var ui = SpreadsheetApp.getUi();   ui.createMenu('List Files/Folders')     .addItem('List All Files and Folders', 'listFilesAndFolders')     .addToUi(); };   function listFilesAndFolders(){   var folderId = Browser.inputBox('Enter folder ID', Browser.Buttons.OK_CANCEL);   if (folderId === "") {     Browser.msgBox('Folder ID is invalid');     return;   }   getFolderTree(folderId, true);  };   // Get Folder Tree function getFolderTree(folderId, listAll) {   try {     // Get folder by id     var parentFolder = DriveApp.getFolderById(folderId);          // Initialise the sheet     var file, data, sheet = SpreadsheetApp.getActiveSheet();     sheet.clear();     sheet.appendRow(["Full Path", "Name","Type" ,"Date", "URL", "Last Updated", "Description", "Size","Owner Email"]);          // Get files and folders     getChildFolders(parentFolder.getName(), parentFolder, data, sheet, listAll);   } catch (e) {     Logger.log(e.toString());   } };   // Get the list of files and folders and their metadata in recursive mode function getChildFolders(parentName, parent, data, sheet, listAll) {   var childFolders = parent.getFolders();     // List folders inside the folder   while (childFolders.hasNext()) {     var childFolder = childFolders.next();     var folderId = childFolder.getId();     data = [        parentName + "/" + childFolder.getName(),       childFolder.getName(),       "Folder",       childFolder.getDateCreated(),       childFolder.getUrl(),       childFolder.getLastUpdated(),       childFolder.getDescription(),       childFolder.getSize()/1024,       childFolder.getOwner().getEmail()     ];     // Write     sheet.appendRow(data);          // List files inside the folder     var files = childFolder.getFiles();     while (listAll & files.hasNext()) {       var childFile = files.next();       data = [          parentName + "/" + childFolder.getName() + "/" + childFile.getName(),         childFile.getName(),         "Files",         childFile.getDateCreated(),         childFile.getUrl(),         childFile.getLastUpdated(),         childFile.getDescription(),         childFile.getSize()/1024,         childFile.getOwner().getEmail(),       ];       // Write       sheet.appendRow(data);     }     // Recursive call of the subfolder     getChildFolders(parentName + "/" + childFolder.getName(), childFolder, data, sheet, listAll);     } };

 Past copied Script here and press Save

Step 4  Then Run this script by pressing “Run” Button:

Step 5  Give Permission by clicking “Review Permission”

Step 6  Select your Google drive account:

Step 7  “Click on Advance”:

 Step 8  Then Click on “Go to Untitled project (unsafe)”

Select “Allow”

Step 9 

 Now go to google sheet tab and First “REFRESH” Tab then you will get “List Files/Folders” manu. Click on “List All Files and Folders” manu and choose “List All Files and Folders”:

 How to Find “Folder ID”?

 To get “folderId”, first, go to the desired folder in google drive whose files/folders you want to list and then copy the URL just after 'https://drive.google.com/drive/folders/' as shown below and then paste it in the above popup.

 

 Past this ID in this box and press “OK”

Then you will see the magic. All folders and files name with location and shareable link will be copied in the google sheet. It may takes some times to complete the process. It all depends on your data saved on drive.

The output looks like this:

 

Now you can save this file in google sheets and also you can download this sheet in your computer for easy access to your files and folder.

From this file you can easily share required files to anybody.