Sync a folder's contents to a Sheet

Drive is fine for storing files but poor for asking questions about them. Which files are the biggest? What changed this week? Who owns what? You cannot filter, sort, or chart a folder — but you can do all of that to a spreadsheet.

This script reads a folder and writes one row per file into a Sheet, capturing the name, type, size, last-updated date, owner, and a link. Run it on an hourly trigger and the Sheet becomes a live inventory you can sort and filter like any other data. It is the foundation for size audits, ownership reviews, and clean-up reports.

What you’ll need

• The ID of the folder you want to inventory — the string after /folders/ in its URL.
• A Google Sheet to write into, and its spreadsheet ID. The script writes to the first tab.
• Edit access to both the folder and the destination Sheet.

The script

// The folder to inventory and the Sheet to write the inventory into.
const SOURCE_FOLDER_ID = '1abcFolderId';
const INVENTORY_SHEET_ID = '1abcSheetId';

// Column headers, in the order the script writes each file's metadata.
const HEADERS = ['Name', 'Type', 'Size', 'Updated', 'Owner', 'Link'];

/**
 * Reads a Drive folder and rewrites its file inventory into a Sheet,
 * one row per file. Designed to run on a recurring trigger.
 */
function syncFolderToSheet() {
  // 1. Resolve the source folder. Bail out early if the ID is wrong.
  let folder;
  try {
    folder = DriveApp.getFolderById(SOURCE_FOLDER_ID);
  } catch (err) {
    Logger.log('Could not open the source folder — check SOURCE_FOLDER_ID.');
    return;
  }

  // 2. Walk the folder and collect one metadata row per file.
  const files = folder.getFiles();
  const rows = [];
  while (files.hasNext()) {
    const file = files.next();
    rows.push([
      file.getName(),
      file.getMimeType(),
      file.getSize(),
      file.getLastUpdated(),
      file.getOwner() ? file.getOwner().getEmail() : '',
      file.getUrl(),
    ]);
  }

  // 3. Open the destination tab and clear out the previous snapshot.
  const sheet = SpreadsheetApp.openById(INVENTORY_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);

  // 4. Write the new rows in one block, if there are any.
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, HEADERS.length).setValues(rows);
  }

  Logger.log(`Synced ${rows.length} file(s) to the inventory Sheet.`);
}

How it works

1. syncFolderToSheet resolves SOURCE_FOLDER_ID inside a try/catch, so a wrong ID logs a clear message instead of throwing.
2. It calls getFiles() and loops through every file, building a row with the name, MIME type, size in bytes, last-updated date, owner email, and URL. getOwner() can return null on shared drives, so the owner cell falls back to an empty string.
3. It opens the first tab of the inventory Sheet and clears it, so the run always starts from a clean slate rather than appending to stale data.
4. It writes the header row from HEADERS, then writes all file rows in a single setValues call — far faster than writing cell by cell.
5. It logs the file count, which makes it easy to spot in the trigger history when a folder suddenly grows or empties.

Example run

Suppose the folder holds three files. After a run, the first tab of the Sheet looks like this:

Name	Type	Size	Updated	Owner	Link
Q3 report.pdf	application/pdf	482113	2025-08-19 14:02	[email protected]	https://…
Budget.gsheet	application/vnd.google-apps.spreadsheet	0	2025-08-21 09:30	[email protected]	https://…
Logo.png	image/png	24880	2025-06-02 11:15	[email protected]	https://…

From there you can sort by Size to find the heavy files, or filter Updated to see what changed today.

Trigger it

Run this hourly so the inventory stays close to live:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose syncFolderToSheet, event source Time-driven, type Hour timer, and an interval such as every hour.
4. Save. The script clears and rewrites the tab on each run, so the Sheet never drifts out of step with the folder.

Watch out for

• The script reads one folder, not its sub-folders. For a nested structure you would need to recurse — see Visualize a deep folder hierarchy for the walking pattern.
• getSize() reports 0 for native Google files like Docs and Sheets, because they do not count against Drive storage. Do not read a 0 as an empty file.
• The clear-and-rewrite approach means any manual edits or extra columns in the tab are wiped on the next run. Keep notes on a second tab.
• A folder with thousands of files can be slow and may approach the six-minute execution limit. If it does, sync sub-folders separately or reduce the trigger frequency.