How to get List of Files with Size from Google Drive Folder and Save into Excel Sheet
Vložit
- čas přidán 5. 09. 2024
- How to get List of Files with Size from Google Drive Folder and Save into Excel Sheet, in this video we are going to learn How to get List of Files with Size from Google Drive Folder and Save into Excel Sheet.
Script:
function listFolderContents() {
var foldername = 'Final Logos'; // provide the name of Folder from which you want to get the list of files
var ListOfFiles = 'ListOfFiles_' + foldername;
var folders = DriveApp.getFoldersByName(foldername)
var folder = folders.next();
var contents = folder.getFiles();
var ss = SpreadsheetApp.create(ListOfFiles);
var sheet = ss.getActiveSheet();
sheet.appendRow( ['name', 'link','sizeInMB'] );
var var_file;
var var_name;
var var_link;
var var_size;
while(contents.hasNext()) {
var_file = contents.next();
var_name = var_file.getName();
var_link = var_file.getUrl();
var_size=var_file.getSize()/1024.0/1024.0;
sheet.appendRow( [var_name, var_link,var_size] );
}
};
#ExcelSheet #ExcelTricks #GoogleDrive
This is brilliant, simple and exactly what I needed. I had never done App Scripting before and I don't know if I'm ready to be a coder yet but this one saved me hours and hours of time on a project. I thank you very much for taking the time to make a great post. BRAVO!
Thank you for leaving detailed and nice comments!!Glad to hear that it was helpful
Worked perfectly. Could have been a 2 minute tutorial, though - but thank you!
Thank you for sharing this!!! Really easy to understand. I want to include parent folders though plus their subfolders, what changes in the code should be done? Thank you. Really appreciate it. :)
It's working, chief! However I had to modify slightly the code so it loads the list to the current file (and, of course, in order to clear anteriorly the active sheet every time as it is triggered periodically). Cheers 👍
Code:
function listFolderContents() {
var foldername = 'YourFolderName'; // provide the name of Folder from which you want to get the list of files
var ListOfFiles = 'ListOfFiles_' + foldername;
var folders = DriveApp.getFoldersByName(foldername)
var folder = folders.next();
var contents = folder.getFiles();
SpreadsheetApp.getActiveSheet().clear();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
sheet.appendRow( ['name', 'link','sizeInMB'] );
var var_file;
var var_name;
var var_link;
var var_size;
while(contents.hasNext()) {
var_file = contents.next();
var_name = var_file.getName();
var_link = var_file.getUrl();
var_size=var_file.getSize()/1024.0/1024.0;
sheet.appendRow( [var_name, var_link,var_size] );
}
};
thats exactly what i want to do, do you mind sharing the code?
@@user-mc8cj3ls6m
function listFolderContents() {
var foldername = 'YourFolderName'; // provide the name of Folder from which you want to get the list of files
var ListOfFiles = 'ListOfFiles_' + foldername;
var folders = DriveApp.getFoldersByName(foldername)
var folder = folders.next();
var contents = folder.getFiles();
SpreadsheetApp.getActiveSheet().clear();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
sheet.appendRow( ['name', 'link','sizeInMB'] );
var var_file;
var var_name;
var var_link;
var var_size;
while(contents.hasNext()) {
var_file = contents.next();
var_name = var_file.getName();
var_link = var_file.getUrl();
var_size=var_file.getSize()/1024.0/1024.0;
sheet.appendRow( [var_name, var_link,var_size] );
}
};
TypeError: Cannot read properties of null (reading 'clear')
Thanks so much! A brilliant script and just what I was looking for!
WONDERFUL!
Do you have this version for shared drive?
Thanks a bunch, that helped me a lot
dude thats useful tool. thanks for sharing.
This is wonderful, thank you!!
Outstanding work friend
Thanks so much for the help! This has been useful for complete files, but now I've come up with an obstacle. Sometimes I search for a specific phrase, so only the files with that content appear, and next I want to get the filenames of that filtered list. Could you please suggest any option for this?
Nice work.. thanks!
well done mein freund
Thank you
Hi, it works but not in subfolders....do you know how to make it work for google drive folder with a few subfolders? Thanks!
Thank you, amazing work!
1. I'm Interested in knowing how to get this works for subfolders too.
2. what will happen if I have the same folder names on the drive? will it take both, or just the first one?
@@moshiblum4664 Hey! 8 months later but what the heck, maybe this helps someone else. By the code attached to the video, if two files have the same name, the code only catches the first one it find. Either just rename one of the folders (temporarily if you want to swtich back later), or if you have the same problem as me where you don't own the folder, I've reworked the code so you can specify the parent folder:
function listFolderContents() {
var parentFolderName = 'writeYourParentFolderNameHere'; // Name of the parent folder
var subFolderName = 'writeYourSubFolderNameHere; // Name of the subfolder you're interested in
var ListOfFiles = 'ListOfFiles_' + parentFolderName;
// Get the parent folder
var parentFolders = DriveApp.getFoldersByName(parentFolderName);
var parentFolder = parentFolders.next();
// Get all folders with the specified name within the parent folder
var subfolders = parentFolder.getFoldersByName(subFolderName);
// Iterate over each subfolder and retrieve its contents
while (subfolders.hasNext()) {
var folder = subfolders.next();
var contents = folder.getFiles();
var ss = SpreadsheetApp.create(ListOfFiles);
var sheet = ss.getActiveSheet();
sheet.appendRow(['name', 'link', 'sizeInMB']);
var var_file;
var var_name;
var var_link;
var var_size;
// Process the files within the subfolder
while (contents.hasNext()) {
var_file = contents.next();
var_name = var_file.getName();
var_link = var_file.getUrl();
var_size = var_file.getSize() / 1024.0 / 1024.0;
sheet.appendRow([var_name, var_link, var_size]);
}
}
}
Big thanks to @TechBrothersIT for the original code, helped a bunch!
Hi, Thank you for sharing such an amazing script, this is going to save a lot of time making links for files on my G Drive, but I'm having a problem with the timer limit from the App Script, I have so many files (around 16.000) that I can't create files within the 6 minutes limit that the App Script has, is there a way to bypass that?
is it possible to list only text files and total number of pages in an sheet? folder name, file name, file type, file page number, file size MB)
This code is great but it is not working if there is any sub folder in the drive. Is there any solution for it?
Thank you so much! First one I found that worked :)
Hi, I really appreciate this but I have zero knowledge in coding. I just followed as instructed: copy paste the code in App Script, provided the folder name save and run but I am always getting "Error An unknown error has occurred, please try again later." How can I trace what I'm missing? Thank you.
Does it work with Shared Folders as well? I get an error: "Exception: Cannot retrieve the next object: iterator has reached the end."
is there a way to get the list of files in drive contain more than 1000 data??
Hey I used the code it worked great but how do I update it when I add more items to the drive folder?
question, how to get length videos? what code do we have to use?
What is the language used to create the script?
Not working if there are folders with same name. How to handle then? And how to look recursively? Any idea?
I have a way:
Step 1: open the google drive, show the list folder in google drive.
Step 2: scrolling capture the screen
Step 3: Trim the picture have just capture
step 4: use web changing image to excel file
The google spread sheet is showing only the headers. Am I doing something wrong?
Hi, is there a way to list all files of subfolders in 1 folder ?
Not sure if you were looking for the same thing I was, but I need a list of all Folder Names that existed in a Shared Drive, vs. Files Names. I tweaked the code to below and it worked like a charm. To be clear, this will ONLY give you the Folders Names in the Parent Folder. Any Files that are in the folder as well are not included.
function listFolderContents() {
var foldername = 'Clients'; // provide the name of Folder from which you want to get the list of files
var ListOfFiles = 'ListOfFiles_' + foldername;
var folders = DriveApp.getFoldersByName(foldername)
var folder = folders.next();
var contents = folder.getFiles();
var ss = SpreadsheetApp.create(ListOfFiles);
var sheet = ss.getActiveSheet();
sheet.appendRow( ['name', 'link','sizeInMB'] );
var var_file;
var var_name;
var var_link;
var var_size;
while(contents.hasNext()) {
var_file = contents.next();
var_name = var_file.getName();
var_link = var_file.getUrl();
var_size=var_file.getSize()/1024.0/1024.0;
sheet.appendRow( [var_name, var_link,var_size] );
}
};
Can you please share the script as was mentioned on the video?
Script is added in the Description, please take look, Thanks For Your Comment.
Will this update automatically when a new file or folder is added?
Nope you have to re run it
Hi! Can you use this for a shared Google drive?
I have not tried. not sure
I did try it in a shared dirve and it worked
As long as you can see a file from you account you can do it
what about get thumbnail of an image
It worked but was not recursive so it did not traverse the folder hierarchy that was inside the folder. Did anyone take the time to make this a recursive algorithm?
The too comment has a reply in which he has given the for code for this check that out
I killed that script by having 160.222.333.111 Files in my Google Drive. 2238 GB of Data
That's crazy
ooops.... Houston we have a problem:
Google is blocking the app since it's trying "to access sensitive info"
This app is blocked
This app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.
Did you try to share the folder