Build a Drive search index in Sheets

Drive’s own search is fine until it isn’t. It cannot easily answer “every file under the Clients folder, sorted by when it was last touched” or “all the PDFs nobody has opened since spring”. Northwind’s project archive has grown to the point where finding the right file means remembering where someone filed it years ago.

This script flattens a folder tree into a spreadsheet — one row per file, with its path, name, type, last-updated date, and a direct link. Once the metadata lives in a Sheet, QUERY, FILTER, and SORT formulas turn it into something closer to Spotlight: instant, structured search across the whole archive.

What you’ll need

• A root Drive folder to index — the script recurses into every subfolder.
• A Google Sheet to hold the index. The script clears the first tab and rebuilds it on every run, so use a dedicated sheet.
• Edit access to both the folder tree and the index sheet.

The script

/**
 * Rebuilds a searchable file index for a Drive folder tree.
 * Writes one row per file: path, name, MIME type, last-updated, link.
 *
 * @param {string} rootFolderId  ID of the folder to index.
 * @param {string} sheetId       ID of the spreadsheet for the index.
 */
function reindexDrive(rootFolderId, sheetId) {
  // 1. Walk the tree, collecting one row per file.
  const rows = [];
  walk(DriveApp.getFolderById(rootFolderId), '', rows);

  // 2. Open the index sheet and reset it.
  const sheet = SpreadsheetApp.openById(sheetId).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 5)
    .setValues([['Path', 'Name', 'Type', 'Updated', 'Link']]);

  // 3. Write every file row in one bulk call.
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, 5).setValues(rows);
  }
  Logger.log('Indexed ' + rows.length + ' file(s).');
}

/**
 * Recursively scans a folder, pushing one metadata row per file.
 *
 * @param {Folder} folder  The folder to scan.
 * @param {string} path    Path of the parent, for a readable trail.
 * @param {Array}  out     The accumulator array of file rows.
 */
function walk(folder, path, out) {
  // Build a readable path like "Clients/Acme/Brief".
  const full = path ? `${path}/${folder.getName()}` : folder.getName();

  // Record every file in this folder.
  const files = folder.getFiles();
  while (files.hasNext()) {
    const f = files.next();
    out.push([
      full,
      f.getName(),
      f.getMimeType(),
      f.getLastUpdated(),
      f.getUrl(),
    ]);
  }

  // Recurse into every subfolder.
  const subs = folder.getFolders();
  while (subs.hasNext()) walk(subs.next(), full, out);
}

How it works

1. reindexDrive opens the root folder and calls walk to scan the whole tree, collecting file rows into a single rows array.
2. It opens the index spreadsheet, clears the first tab, and writes a header of Path, Name, Type, Updated, and Link.
3. If walk found any files, it writes them all in one bulk setValues call — far faster than writing row by row.
4. walk builds a readable path string so each row shows exactly where the file sits in the hierarchy.
5. For every file it records the name, MIME type, last-updated date, and a direct URL.
6. It then recurses into every subfolder, so a single call indexes the entire tree.

Example run

After a run, the index sheet looks like this:

Path	Name	Type	Updated	Link
Clients/Acme	Brand brief	application/vnd.google-apps.document	2025-11-02	https://docs.google.com/…
Clients/Acme/Assets	logo-final.png	image/png	2025-10-18	https://drive.google.com/…
Clients/Beta	Q3 report.pdf	application/pdf	2025-09-30	https://drive.google.com/…

Now search with formulas. To list every PDF, sorted by most recent:

=QUERY(A2:E, "SELECT * WHERE C = 'application/pdf' ORDER BY D DESC", 0)

To find everything under a particular client:

=FILTER(A2:E, REGEXMATCH(A2:A, "Clients/Acme"))

These run instantly over the sheet — no clicking through Drive folders.

Run it

The index is a snapshot, so rerun it whenever the archive has changed:

1. Paste the script into the Apps Script editor.
2. Add a small wrapper with your folder and sheet IDs:

function runReindex() {
  reindexDrive('YOUR_ROOT_FOLDER_ID', 'YOUR_INDEX_SHEET_ID');
}

3. Select runReindex, click Run, and approve the authorisation prompt.
4. To keep the index fresh, add a time-driven trigger on runReindex from the Triggers panel — a nightly run suits most archives.

Watch out for

• The index is a snapshot, not live. Files added or moved after the last run will not appear until you reindex — schedule it nightly if freshness matters.
• Large trees take time. walk touches every file, and each getUrl and getLastUpdated call is a Drive request. A tree of many thousands of files can approach the six-minute execution limit; index subtrees separately if so.
• A full rebuild clears the sheet first. Any manual columns or notes added to the index tab will be wiped — keep extra working columns on a separate tab.
• MIME types are precise but not friendly. Google Docs show as application/vnd.google-apps.document; add a lookup column if you want plain labels like “Doc” or “Sheet”.
• Shortcuts and files in shared drives behave differently from regular files. Test against your actual archive before relying on the counts.