List Every File in a Google Drive Folder to a Sheet with Apps Script
How can I list every file in a Google Drive folder and its subfolders in a Google Sheet?
Add a bound Apps Script to a spreadsheet, save the folder ID in Script Properties, and run it. The script visits the folder and every subfolder, collects name, path, type, owner, last updated date, size and URL for each file, and writes them to a new tab in one call. It only reads Drive and stops early, with a warning row, before the six-minute run limit.
A short Apps Script bound to a spreadsheet can produce a full file list for a Drive folder, subfolders included. It reads each file’s details, keeps them in memory, and writes the finished table to a fresh tab in one step. Nothing in Drive is moved, renamed, shared or deleted.
This is the report people ask for before a cleanup, a handover to a new office manager, or a move to a shared drive. It answers “what is actually in here, and who owns it” without clicking through every folder.
What does each row of the Drive inventory contain?
Each file in the tree becomes one row with seven columns. The values come straight from the DriveApp File methods described in the Apps Script File reference.
| Column | Source method | Notes |
|---|---|---|
| Name | getName() |
Exactly as shown in Drive |
| Path | Built by the script | Folder names from the top folder down, joined with / |
| Type | getMimeType() |
For example application/pdf or application/vnd.google-apps.document |
| Owner | getOwner() then getEmail() |
Blank when Drive does not return an owner or an email |
| Last updated | getLastUpdated() |
A date value the sheet can sort |
| Size (bytes) | getSize() |
Google Docs, Sheets and Slides files report 0 |
| URL | getUrl() |
Opens the file in Drive or the matching editor |
The size column needs a word of warning. Google’s reference says files made in Google Workspace apps do not count toward Drive storage and return 0 bytes. A folder full of Docs will look empty by size even when it holds hundreds of pages. Use the column to spot large PDFs, videos and image exports, not to measure how much work is in a folder.
The owner email may also be blank. The User reference notes that an email is only available in some cases, for example when the owner is in the same domain and the administrator lets users see each other’s addresses. The script writes an empty cell rather than guessing.
How do you set up the spreadsheet and folder ID?
Set up a dedicated spreadsheet for the inventory so the report and the script live together.
- Create a spreadsheet with a clear name such as
Riley Placeholder Drive inventory. Open Extensions, then Apps Script. - Paste the code from the next section over the starter code and save.
- Copy the folder ID. Open the top folder in Drive and copy the part of the address after
/folders/, without anything after a?. - Save the property. In the Apps Script editor, open Project Settings, find Script Properties, add
ROOT_FOLDER_ID, paste the ID as the value and save. - Limit the scopes. In Project Settings, turn on the option to show the
appsscript.jsonmanifest, then apply the manifest shown later in this guide. - Run it. Choose
listFolderTreein the function menu and click Run. Approve the authorization prompt with an account that can open every folder you want listed. - Open the new tab. Its name starts with
Inventoryand ends with the run time.
Run the script from the account that will review the list. A file appears only if that account can see it, so a list run by a junior team member may be shorter than one run by the folder owner.
Which Apps Script walks the folder tree and writes it in one call?
The script below keeps a list of folders to visit. It takes one folder, reads its files, adds its subfolders to the list, and repeats until the list is empty or time runs short. All rows are written at the end with one setValues call, which is much faster than writing a row at a time.
const TIME_BUDGET_MS = 5 * 60 * 1000; // stop well before the 6-minute limit
function listFolderTree() {
const started = Date.now();
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error('Open this script from Extensions > Apps Script in a spreadsheet.');
}
const rootId = (PropertiesService.getScriptProperties()
.getProperty('ROOT_FOLDER_ID') || '').trim();
if (!rootId) {
throw new Error('Set ROOT_FOLDER_ID in Script Properties.');
}
const root = DriveApp.getFolderById(rootId);
const queue = [{ folder: root, path: root.getName() }];
const rows = [];
let stoppedEarly = false;
while (queue.length > 0) {
if (Date.now() - started > TIME_BUDGET_MS) {
stoppedEarly = true;
break;
}
const current = queue.shift();
const files = current.folder.getFiles();
while (files.hasNext()) {
if (Date.now() - started > TIME_BUDGET_MS) {
stoppedEarly = true;
break;
}
const file = files.next();
if (file.isTrashed()) continue;
rows.push([
asText(file.getName()),
asText(current.path),
file.getMimeType(),
ownerEmail(file),
file.getLastUpdated(),
file.getSize(),
file.getUrl()
]);
}
if (stoppedEarly) break;
const subfolders = current.folder.getFolders();
while (subfolders.hasNext()) {
const sub = subfolders.next();
if (sub.isTrashed()) continue;
queue.push({ folder: sub, path: current.path + '/' + sub.getName() });
}
}
const header = ['Name', 'Path', 'Type', 'Owner', 'Last updated', 'Size (bytes)', 'URL'];
const values = [header].concat(rows);
if (stoppedEarly) {
values.push(['INCOMPLETE: time limit reached. ' + queue.length +
' folders not visited. Run on a subfolder instead.', '', '', '', '', '', '']);
}
const tabName = 'Inventory ' + Utilities.formatDate(
new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HHmm');
const sheet = spreadsheet.insertSheet(uniqueName(spreadsheet, tabName));
if (values.length > sheet.getMaxRows()) {
sheet.insertRowsAfter(sheet.getMaxRows(), values.length - sheet.getMaxRows());
}
sheet.getRange(1, 1, values.length, header.length).setValues(values);
sheet.setFrozenRows(1);
spreadsheet.toast(rows.length + ' files listed' +
(stoppedEarly ? ' (incomplete)' : ''), 'Drive inventory', 8);
}
function ownerEmail(file) {
const owner = file.getOwner();
return owner ? (owner.getEmail() || '') : '';
}
// A leading apostrophe stops a name such as "=SUM" being read as a formula.
function asText(value) {
return /^[=+\-@]/.test(value) ? "'" + value : value;
}
function uniqueName(spreadsheet, base) {
let name = base;
let n = 2;
while (spreadsheet.getSheetByName(name)) {
name = base + ' (' + n + ')';
n += 1;
}
return name;
}
The folder methods getFiles() and getFolders() return iterators over the direct children of one folder, as the Folder reference describes. The script handles the depth itself by adding each subfolder to the queue with its path. Items in the trash are skipped.
setValues takes a two-dimensional array whose size must match the range, per the Range reference. The script inserts extra rows first when the list is longer than the new tab, then writes everything in one call.
How does the script stay inside the Apps Script time limit?
Google’s Apps Script quotas page lists a script runtime of six minutes per execution for both consumer and Google Workspace accounts. A run that hits the limit ends with an error. In this script that would mean it never reaches the line that writes the tab, so you would get nothing.
To avoid that, the script checks the clock before each folder and before each file. Once five minutes have passed it stops reading, writes what it has, and adds a final row that starts with INCOMPLETE and says how many folders were never visited. The last minute is left for creating the tab and writing the rows. Google also says quotas can change without notice, so if the page shows a different figure later, adjust TIME_BUDGET_MS to stay under it.
If you see the INCOMPLETE row, do not treat the tab as a full inventory. Run the script again with ROOT_FOLDER_ID set to one of the larger subfolders, and repeat for the others. Each run creates its own tab, so you can combine them later or keep them separate.
Here is what a small sample tree produces. The names are fictional and the URLs are placeholders.
| Name | Path | Type | Owner | Size (bytes) |
|---|---|---|---|---|
| Lease 2026.pdf | Office | application/pdf | avery@example.com | 482113 |
| Supplier list | Office | application/vnd.google-apps.spreadsheet | avery@example.com | 0 |
| Floor plan.png | Office/Move | image/png | morgan@example.com | 1290455 |
| Welcome pack | Office/Onboarding | application/vnd.google-apps.document | 0 |
The last row has an empty owner because Drive did not return an email for that file. The two Google-format files show 0 bytes, as the reference says they will.
How do you keep the script read-only on Drive?
Restrict the project to read-only Drive access and access to the current spreadsheet. With the manifest visible, replace its contents with this:
{
"timeZone": "America/New_York",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/drive.readonly",
"https://www.googleapis.com/auth/spreadsheets.currentonly"
]
}
Change the time zone to your own; it controls the date in the tab name. With these scopes the script can read Drive but has no permission to change it, even if someone edits the code later. It can still write to the spreadsheet it is bound to. Use a dedicated project for this, because replacing the manifest in a project that runs other automation can remove scopes that automation needs.
When is a Drive file list the wrong tool?
The inventory is a snapshot. Files added or renamed after the run will not show until the next run, and a tab from last month may be badly out of date. Put the run date somewhere visible if you share the sheet.
Keep in mind who can open the spreadsheet. The list exposes file names, owners and links, which can be sensitive even when the files themselves are locked down. Viewers of the sheet still need their own permission to open each file.
Choose something else when:
- You need an organization-wide inventory. A single account walking folders will miss anything it cannot see, so it cannot speak for the whole company’s Drive.
- The tree is very large. If every run ends with
INCOMPLETE, split the work by subfolder, or look at a script that saves progress between runs. This one deliberately does not. - You want to compare contents. The list shows names and sizes, not what is inside a file.
For a narrower check in a single folder, finding same-name files in a Google Drive folder groups repeated filenames without touching them. Once the inventory shows which invoices need tidier names, renaming invoice PDFs from a tracking sheet handles that with a dry run first. Both sit with other small-business stack guides.