Build a Drive permissions auditor

Sharing settings drift quietly. Someone flips a client brief to “anyone with the link” for a quick review, a freelancer is added as an editor for one project, and six months later nobody remembers either. At Northwind, that accumulated drift is exactly what a compliance review needs to surface — but clicking through every file’s share dialog by hand is nobody’s idea of a good afternoon.

This script walks a folder tree and writes a tidy report of every file that is either publicly accessible or shared with an editor outside the @northwind.studio domain. The result is a single sheet a reviewer can scan, fix, and sign off — instead of a Drive full of unknowns.

What you’ll need

• A root Drive folder to audit — the script recurses into every subfolder.
• A Google Sheet to hold the report. The script clears the first tab and rewrites it on every run, so use a dedicated sheet.
• Edit access to both the folder tree and the report sheet.
• Your internal domain — anything not ending in it counts as external.

The script

// The internal domain. Editors whose address does not end in this
// are treated as external.
const INTERNAL_DOMAIN = '@northwind.studio';

/**
 * Audits a Drive folder tree and writes every risky file to a sheet.
 * "Risky" means publicly shared, or shared with an external editor.
 *
 * @param {string} rootFolderId  ID of the folder to audit.
 * @param {string} sheetId       ID of the spreadsheet for the report.
 */
function auditPermissions(rootFolderId, sheetId) {
  // 1. Walk the tree, collecting issues into a flat array of rows.
  const issues = [];
  walk(DriveApp.getFolderById(rootFolderId), '', issues);

  // 2. Open the report sheet and reset it.
  const sheet = SpreadsheetApp.openById(sheetId).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 4)
    .setValues([['Path', 'Issue', 'Editors', 'Link']]);

  // 3. Write the findings, if there are any.
  if (issues.length) {
    sheet.getRange(2, 1, issues.length, 4).setValues(issues);
  }
  Logger.log('Found ' + issues.length + ' permission issue(s).');
}

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

  // Check every file in this folder.
  const files = folder.getFiles();
  while (files.hasNext()) {
    const f = files.next();

    // Flag files anyone can open via a link.
    const access = f.getSharingAccess();
    if (access === DriveApp.Access.ANYONE ||
        access === DriveApp.Access.ANYONE_WITH_LINK) {
      out.push([full, 'public link', '', f.getUrl()]);
    }

    // Flag editors whose address is outside the internal domain.
    const external = f.getEditors()
      .filter((e) => !e.getEmail().endsWith(INTERNAL_DOMAIN));
    if (external.length) {
      out.push([
        full,
        'external editor',
        external.map((e) => e.getEmail()).join(','),
        f.getUrl(),
      ]);
    }
  }

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

How it works

1. auditPermissions opens the root folder and calls walk to scan the whole tree, collecting issue rows into a single issues array.
2. It opens the report spreadsheet, clears the first tab, and writes a header row of Path, Issue, Editors, and Link.
3. If walk found anything, it writes every issue row in one bulk setValues call, then logs the total count.
4. walk builds a readable path string so a reviewer can see exactly where each file lives without opening it.
5. For each file it checks getSharingAccess — ANYONE or ANYONE_WITH_LINK means a public link, which gets its own row.
6. It also reads the file’s editors and keeps any whose address does not end in INTERNAL_DOMAIN, recording them as an external-editor issue.
7. Finally it recurses into every subfolder, so a single call covers an entire folder hierarchy.

Example run

Point the script at a Clients folder. The report sheet fills with one row per finding:

Path	Issue	Editors	Link
Clients/Acme/Brief	public link		https://docs.google.com/…
Clients/Acme/Assets	external editor	[email protected]	https://drive.google.com/…
Clients/Beta/Contract	external editor	[email protected]	https://docs.google.com/…

A reviewer works straight down the list: open each link, decide whether the sharing is intentional, and tighten it if not.

Run it

This is an on-demand audit, so run it by hand when a review is due:

1. Paste the script into the Apps Script editor.
2. From a temporary function, call auditPermissions with your folder and sheet IDs, or fill the IDs into a small wrapper:

function runAudit() {
  auditPermissions('YOUR_ROOT_FOLDER_ID', 'YOUR_REPORT_SHEET_ID');
}

3. Select runAudit, click Run, and approve the authorisation prompt.
4. Open the report sheet to see the findings.

To run it on a schedule instead, add a time-driven trigger on runAudit from the Triggers panel.

Watch out for

• A public file shows up only once even if it also has external editors — the public-link row is added first. That is usually fine; the file needs fixing either way.
• getEditors does not include people who only have view access. This audit is about who can change files; widen the check to getViewers if you also care about read access.
• Large trees take time. walk touches every file, and each getSharingAccess call is a Drive request — a folder of thousands of files can approach the six-minute execution limit. Audit subtrees separately if you hit it.
• The script reports; it does not fix. It deliberately never changes sharing settings, so a reviewer always makes the call. Add setSharing only if you are certain you want automatic remediation.
• Files in shared drives have different ownership rules. DriveApp can still read them, but the “owner” concept differs — treat shared-drive results with extra care.