Export list of Google shared drives

List all Google Drive links of Files / folder to Google Sheet

Diwas Poudel Oct 11 2021 Fix 0 comment

In my case, I have created a sheet name "GoogleDriveList"

2. In a sheet title bar ,select "Tools" >> "Script Editor".

This will open Script Editor.

3 In the Script Editor, delete everything from the textbox and paste the following script.

functiononOpen(){

varSS=SpreadsheetApp.getActiveSpreadsheet();

varui=SpreadsheetApp.getUi();

ui.createMenu('ListFiles/Folders')

.addItem('ListAllFilesandFolders','listFilesAndFolders')

.addToUi();

};

function listFilesAndFolders(){

varfolderId=Browser.inputBox('EnterfolderID',Browser.Buttons.OK_CANCEL);

if(folderId===""){

Browser.msgBox('FolderIDisinvalid');

return;

}

getFolderTree(folderId,true);

};

//GetFolderTree

functiongetFolderTree(folderId,listAll){

try{

//Getfolderbyid

varparentFolder=DriveApp.getFolderById(folderId);

//Initialisethesheet

varfile,data,sheet=SpreadsheetApp.getActiveSheet();

sheet.clear();

sheet.appendRow(["FullPath","Name","Type","Date","URL","LastUpdated","Description","Size","OwnerEmail"]);

//Getfilesandfolders

getChildFolders(parentFolder.getName(),parentFolder,data,sheet,listAll);

}catch(e){

Logger.log(e.toString());

}

};

//Getthelistoffilesandfoldersandtheirmetadatainrecursivemode

functiongetChildFolders(parentName,parent,data,sheet,listAll){

varchildFolders=parent.getFolders();

//Listfoldersinsidethefolder

while(childFolders.hasNext()){

varchildFolder=childFolders.next();

varfolderId=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);

//Listfilesinsidethefolder

varfiles=childFolder.getFiles();

while(listAll&files.hasNext()){

varchildFile=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);

}

//Recursivecallofthesubfolder

getChildFolders(parentName+"/"+childFolder.getName(),childFolder,data,sheet,listAll);

}

};

Source Code of this is located here. I have done a little modification.

4 Click on the Edit icon.

5. Goto the same Google Sheets File and refresh the page.
Then you will get the "ListFiles/Folders" menu, click on it, and select the "ListAllFilesandFolders" item.

6. Popup will appear and there type folder Id.

How to get FolderId?

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

7. Press the Ok button in the popup.
Then all your folders, files links are listed in google docs one by one. Depending on the number of files and folders of Google Drive, it may take some time to display all the files.

The output looks like this:

Video: