Detect and report broken file shortcuts

Northwind’s shared drives accumulate shortcuts the way a desk accumulates post-it notes — quick, useful, eventually pointing at nothing. Someone deletes the original file, or moves it into a folder you no longer have access to, and the shortcut quietly turns into a dead end. Click it months later and you get “Sorry, this file does not exist”. Worse, you don’t know it’s broken until you need it.

This script walks a folder, finds every shortcut, and tries to fetch its target. Anything that fails — the original was trashed, the original was moved to private, the original never existed — lands in a report sheet with the shortcut’s name and its dead target ID. Now you have something to tidy up before the next person clicks it.

What you’ll need

• A starting Drive folder ID — the audit walks just this folder by default (extend scan to recurse if you want subfolders).
• A Google Sheet to receive the report. The script rewrites it from scratch on every run, so any tab will do.
• The advanced Drive API service enabled. In the Apps Script editor: Services > add Drive API (v3). The built-in DriveApp can’t query shortcuts directly; Drive.Files.list can.

The script

// Default folder to audit. Pass an override into reportBrokenShortcuts()
// if you want to point at something else without editing the script.
const DEFAULT_FOLDER_ID = '1abcFolderId';

// Default sheet to write the report to.
const DEFAULT_SHEET_ID = '1abcReportSheetId';

// MIME type Drive uses for shortcut entries.
const SHORTCUT_MIME = 'application/vnd.google-apps.shortcut';

/**
 * Walk the folder, collect every broken shortcut, and write the result
 * to the given sheet — clearing it first so each run is fresh.
 *
 * @param {string} [folderId] - Folder to audit. Defaults to DEFAULT_FOLDER_ID.
 * @param {string} [sheetId] - Spreadsheet to write to. Defaults to DEFAULT_SHEET_ID.
 */
function reportBrokenShortcuts(folderId = DEFAULT_FOLDER_ID,
                               sheetId = DEFAULT_SHEET_ID) {
  const broken = [];
  scan(folderId, broken);

  const sheet = SpreadsheetApp.openById(sheetId).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 3)
    .setValues([['Shortcut name', 'Target ID', 'Parent folder']]);

  if (broken.length) {
    sheet.getRange(2, 1, broken.length, 3).setValues(broken);
  }
  Logger.log('Found ' + broken.length + ' broken shortcut(s) in ' + folderId);
}

/**
 * List every shortcut in a folder, try to fetch each target, and push
 * the broken ones onto `out`.
 *
 * @param {string} folderId - Drive folder to scan.
 * @param {Array<Array<string>>} out - Accumulator for [name, targetId, parent] rows.
 */
function scan(folderId, out) {
  // Drive query: shortcuts whose parent is this folder.
  const q = "'" + folderId + "' in parents and mimeType = '" + SHORTCUT_MIME + "'";
  const res = Drive.Files.list({
    q,
    fields: 'files(id,name,shortcutDetails)',
    pageSize: 1000,
  });

  for (const sc of res.files || []) {
    const targetId = sc.shortcutDetails && sc.shortcutDetails.targetId;
    if (!targetId) {
      // A shortcut with no target ID is broken by definition.
      out.push([sc.name, '(missing)', folderId]);
      continue;
    }
    try {
      // Fetching id is the cheapest check — we only care whether it resolves.
      Drive.Files.get(targetId, { fields: 'id' });
    } catch (e) {
      // 404 (deleted) or 403 (no access) both count as broken from the
      // user's perspective: clicking the shortcut gets them nowhere.
      out.push([sc.name, targetId, folderId]);
    }
  }
}

How it works

1. reportBrokenShortcuts accepts optional folder and sheet IDs so you can reuse the same script for different audits without editing constants.
2. It calls scan, which pushes one row per broken shortcut onto a shared array — keeping the report logic in one place and the discovery logic in another.
3. scan runs a Drive query that filters by parent folder and the shortcut MIME type, so the API returns only the entries we care about. pageSize is set high enough that small-to-medium folders fit in one page.
4. For each shortcut, it reads shortcutDetails.targetId. A shortcut with no target ID is already broken — record it and move on.
5. Otherwise it calls Drive.Files.get on the target with the minimum fields we need (id). If that throws, the target is either deleted or inaccessible — both count as broken to the user clicking it, so both land in the report.
6. Back in the caller, the report sheet is cleared, a header is written, and all broken rows go down in a single setValues call. Reruns replace the previous report — there is no stale history to clean up.

Example run

Imagine a Northwind folder contains five shortcuts. After running the audit, the report sheet looks like this:

Shortcut name	Target ID	Parent folder
Q3 launch brief.gdoc	1XyzDeleted	1abcFolderId
Supplier list.gsheet	1XyzMovedToPrivate	1abcFolderId
Old logo.png	(missing)	1abcFolderId

Three shortcuts are broken: one whose target file was trashed, one whose target was moved into a folder the auditor can’t access, and one whose shortcut metadata never had a target in the first place. The other two shortcuts in the folder resolved fine and don’t appear.

Trigger it

For a regular tidy, run it on a schedule:

1. In the Apps Script editor, open Triggers (clock icon on the left).
2. Add a time-driven trigger for reportBrokenShortcuts — weekly is plenty for a stable folder, monthly for an archive.
3. After each run, open the report sheet and either fix or delete the listed shortcuts.

For a one-off audit, call it directly from the editor and pass in the folder and sheet IDs you want to use.

Watch out for

• The script only scans the top level of the folder. To audit a tree, push any folders found into a queue and call scan recursively. Watch the six-minute execution limit on very large drives.
• A 403 (“no access”) is reported the same as a 404 (“deleted”). That’s usually what you want — the shortcut is useless either way — but if you need to distinguish them, inspect e.message in the catch block.
• Shortcuts that resolve fine today can break tomorrow. Re-run regularly; don’t treat a clean report as permanent.
• The Drive Advanced Service must be enabled — DriveApp alone can’t read shortcutDetails. If you see “Drive is not defined”, that’s the fix.