🗂️ Google Drive Sorting Utility

Automated AI-powered Google Drive cleanup and service-intelligence pipeline for agencies and data teams.

Scans your Drive for stray files, uses Claude AI to identify which client each file belongs to, presents suggestions in a Google Sheet for human approval, then moves everything to the right place — on a monthly schedule, automatically.

On top of sorting, it can also label every file in every client folder with the service(s) it represents (Audit, Analytics, Dashboarding, Attribution, etc.), store the results in PostgreSQL, and serve aggregate stats (label distribution, client journey stages, service bundles, documentation gaps) over HTTP for a dashboard.

Built by Marketlytics and open-sourced for the broader analytics community.

The Problem

If you run an agency, your Google Drive looks like this:

Analytics-ML/
├── Ferguson Roofing/          ← correct ✓
├── Trupanion/                 ← correct ✓
├── Ferguson Roofing - GTM Code Changes.gdoc   ← stray ✗
├── 230401_Marketing_Metrics.gsheet            ← stray ✗
├── Zepz - AppsFlyer Guide.gdoc               ← stray ✗
└── Copy of Report.gdoc                        ← stray ✗

Files accumulate in root folders instead of their client subfolders. Finding anything becomes impossible. Knowledge walks out the door when team members leave.

This utility fixes that automatically.

How It Works

Every month on the 5th (Cloud Scheduler × every 45 min × 4 hours)
         │
         ▼
1. Scan Drive for stray files across configured scan targets
         │
         ▼
2. Fuzzy match filenames → client folders (free, no AI)
   ~30-40% matched instantly at score ≥85
         │
         ▼
3. Claude AI matches remaining files
   700 folders → filtered to ≤120 relevant per batch → Claude picks
   Validated back against real folder list before accepting
         │
         ▼
4. Unmatched files → suggestion pass
   Similar files clustered → one Claude call per cluster
   Suggests new folder names for files with no existing destination
         │
         ▼
5. Results written to Google Sheet
   Human reviews: Approve / Disapprove / Override in manual_folder
         │
         ▼
6. Move command executes approved rows
   NEW_FOLDER → folder auto-created → file moved
   Override → file moved to manual_folder destination
   Already moved → skipped (idempotent)

All state lives in the Google Sheet — if a run fails, the next one picks up exactly where it left off.

Architecture

Cloud Scheduler (×2 cron jobs)
        │  HTTP GET every 45 min
        ▼
GCP Cloud Function (Python 3.11, Gen2, 540s timeout)
   drive_manager(request) — routes on ?action=
        │
        ├── ?action=(none) → audit   (fuzzy + Claude match → sheet)
        ├── ?action=move    → mover  (reads approvals → moves files)
        ├── ?action=label   → labeller (keyword + Claude → sheet + Postgres)
        ├── ?action=stats   → dashboard stats (reads Postgres, JSON out, CORS-enabled)
        │
        ├── Google Drive API v3  ── scan + move files
        ├── Google Sheets API    ── read/write audit/label tabs
        ├── Anthropic Claude API ── AI matching, suggestions, and labelling
        │                           (claude-haiku-4-5-20251001)
        └── Cloud SQL (PostgreSQL, via Cloud SQL Python Connector)
                                    ── stores per-file service labels for
                                       analytics/dashboarding

Cost: ~$0.40–0.50/month on Anthropic Claude Haiku for audit matching (labelling adds more Claude calls, offset by the free keyword pre-match — see Cost Estimate). GCP Cloud Functions free tier covers the compute. Google Sheets API is free. Cloud SQL PostgreSQL is billed separately by GCP (skip it if you only need the sort/move workflow — labelling and stats are optional).

Prerequisites

GCP project with Cloud Functions and Cloud Scheduler enabled
Google Drive with a consistent client folder structure
Google Sheet (blank, you just need the ID from the URL)
Anthropic API key — console.anthropic.com
Gmail account with App Password enabled (for email reports)
Python 3.11+ for local testing
Cloud SQL for PostgreSQL instance — optional, only needed for the service labelling pipeline and ?action=stats dashboard endpoint (see Service Labelling Pipeline)

Setup — Step by Step

1. Clone the repo

git clone https://github.com/marketlytics/gdrive-sorting-utility.git
cd gdrive-sorting-utility

2. Create a GCP Service Account

# Create service account
gcloud iam service-accounts create gdrive-clean-utility \
  --display-name="Drive Sorting Utility"

# Download key
gcloud iam service-accounts keys create sa-key.json \
  --iam-account=gdrive-clean-utility@YOUR-PROJECT.iam.gserviceaccount.com

# Base64-encode the key for .env.yaml
base64 -w 0 sa-key.json   # Linux/Mac
# OR on Windows:
certutil -encode sa-key.json sa-key-b64.txt

3. Share your Drive folders with the SA

The service account needs access to your Drive folders. In Google Drive:

Right-click your root client folder (e.g. Analytics-ML)
Share → paste the SA email → Editor
Repeat for any other scan target folders

The SA email is in sa-key.json under client_email.

4. Share your Google Sheet with the SA

Open the sheet → Share → paste SA email → Editor.

5. Configure secrets

cp .env.yaml.example .env.yaml

Edit .env.yaml with your values. See .env.yaml.example for what each field means.

6. Configure your Drive structure

Edit config.yaml:

agency:
  name: "Your Agency Name"

drive:
  scan_targets:
    - parent: "Your Root Folder"
      child: null
    - parent: "Your Root Folder"
      child: "Old Clients Subfolder"

  root_folder: "Your Root Folder"

  excluded_folders:
    - "Internal Projects"
    - "Templates"
    # add any folder that should never be a move destination

7. Install dependencies

pip install -r requirements.txt

8. Run locally first

# Audit mode — scans Drive and fills the sheet
py -u run_local.py

# Mover mode — moves approved rows
py -u run_local.py move

Check the sheet to verify results before deploying.

Deploy to GCP

gcloud functions deploy gdrive_sorting_utility \
  --gen2 \
  --runtime=python311 \
  --region=us-central1 \
  --source=. \
  --entry-point=drive_manager \
  --trigger-http \
  --allow-unauthenticated \
  --timeout=540s \
  --memory=1024MB \
  --env-vars-file=.env.yaml

Set up Cloud Scheduler

Two jobs are needed (cron can't express "every 45 minutes" in one expression):

# Job A — fires at :00 past each hour
gcloud scheduler jobs create http drive-audit-a \
  --schedule="0 13-16 5 * *" \
  --uri="https://YOUR-REGION-YOUR-PROJECT.cloudfunctions.net/gdrive_sorting_utility" \
  --http-method=GET \
  --time-zone="UTC" \
  --location=us-central1

# Job B — fires at :45 past each hour
gcloud scheduler jobs create http drive-audit-b \
  --schedule="45 13-16 5 * *" \
  --uri="https://YOUR-REGION-YOUR-PROJECT.cloudfunctions.net/gdrive_sorting_utility" \
  --http-method=GET \
  --time-zone="UTC" \
  --location=us-central1

Adjust the hour range (13-16) to match when you want the audit to run in UTC.

Trigger manually

# Run audit
curl https://YOUR-FUNCTION-URL/gdrive_sorting_utility

# Run mover (after reviewing the sheet)
curl "https://YOUR-FUNCTION-URL/gdrive_sorting_utility?action=move"

# Target a specific audit tab
curl "https://YOUR-FUNCTION-URL/gdrive_sorting_utility?action=move&tab=2026-05-05"

# Run the service labelling pass (requires Cloud SQL configured — see below)
curl "https://YOUR-FUNCTION-URL/gdrive_sorting_utility?action=label"

# Fetch dashboard stats JSON (CORS-enabled, safe to call from a browser)
curl "https://YOUR-FUNCTION-URL/gdrive_sorting_utility?action=stats"

Google Sheet — Reviewer Guide

After each audit run, open the sheet and review the new tab (Audit YYYY-MM-DD HH:MM).

Column	Purpose
`stray_file_name`	Original filename
`suggested_folder`	AI-suggested destination
`confidence`	high / medium / low
`reason`	Why this match was made
`status`	MATCHED / UNMATCHED / NEW_FOLDER
`action`	Set this: Approve or Disapprove
`manual_folder`	Override the suggestion — type an exact folder name OR paste a Drive folder ID from the URL
`notes`	System notes — "validation failed" = weak match, review carefully
`moved`	Written "yes" after successful move

Three outcomes

MATCHED — AI found an existing client folder. Review the reason column. If it makes sense → Approve.

NEW_FOLDER — File belongs to a client with no existing folder. Approve to auto-create the folder and move the file. Check that a similar folder doesn't already exist first.

UNMATCHED — No client signal in the filename (generic files, date-only names). Pre-filled with your archive folder. Approve to archive, Disapprove to leave in place, or type a folder name in manual_folder if you know where it belongs.

manual_folder — two modes

Folder name (for direct subfolders):

Ferguson Roofing

Folder ID (for nested/deep folders — copy from Drive URL):

https://drive.google.com/drive/folders/1ABC123xyz...?
                                       └── paste this part

File Naming Convention

For best results, name all new files:

ClientName - DocumentType - YYYY-MM.ext

Examples:

Ferguson Roofing - GTM Audit - 2026-05.gdoc
Trupanion - Measurement Plan - 2025-11.gdoc
Zepz - AppsFlyer Implementation - 2026-03.gdoc

With this convention, the fuzzy matcher alone handles ~80%+ of files with no AI cost.

Environment Variables

Variable	Description
`SA_KEY_JSON_B64`	Base64-encoded GCP service account JSON
`ANTHROPIC_API_KEY`	Anthropic API key
`EXISTING_SHEET_ID`	Google Sheets ID for audit tabs
`SHEET_NAME_PREFIX`	Tab name prefix (default: "Audit")
`SMTP_USER`	Gmail address for outbound reports
`SMTP_PASSWORD`	Gmail App Password
`REPORT_EMAIL`	Recipient for all report emails
`FUNCTION_URL`	Deployed Cloud Function URL
`CLOUD_SQL_INSTANCE`	Cloud SQL connection name (`project:region:instance`) — optional, only needed for `?action=label` / `?action=stats`
`DB_NAME`	PostgreSQL database name — optional, labelling pipeline only
`DB_USER`	PostgreSQL user — optional, labelling pipeline only
`DB_PASSWORD`	PostgreSQL password — optional, labelling pipeline only

Cost Estimate

Component	Cost
Claude Haiku (3,000 files/month, audit)	~$0.40–0.50
Claude Haiku (labelling, only files not resolved by keyword pre-match)	small — most files never leave the free keyword stage
GCP Cloud Functions	Free tier (540s × 32 runs)
Cloud Scheduler	Free tier (3 jobs)
Sheets API	Free
Cloud SQL for PostgreSQL (labelling + stats only)	Smallest tier instance, billed by GCP — skip entirely if you don't need labelling
Total (sort/move only)	~$0.50/month

A $20 Anthropic credit lasts approximately 22 months of monthly audits. Labelling is optional and only adds cost if you configure CLOUD_SQL_INSTANCE/DB_* and call ?action=label.

Local Development

gdrive-sorting-utility/
├── main.py                       # All Cloud Function code
├── run_local.py                  # Local test runner
├── config.yaml                   # Your Drive structure config
├── requirements.txt              # Python dependencies
├── labelling_examples.csv        # Few-shot examples fed to Claude for labelling
├── .env.yaml                     # Secrets (never commit)
├── .env.yaml.example             # Secrets template
├── .gcloudignore                 # Files excluded from GCP deploy
└── sa-key.json                   # Service account key (never commit)

Run locally:

# Audit
py -u run_local.py

# Mover
py -u run_local.py move

# Mover targeting specific tab
py -u run_local.py move 2026-05-05

# Service labelling pass
py -u run_local.py label

Service Labelling Pipeline

A second, independent pipeline (?action=label) walks every file in every client folder (not just strays) and tags each one with the service(s) it represents — Audit, Analytics, Dashboarding, Attribution, Segmentation, Data Engineering, Documentation, Proposal, Operations, or Data. Unlike the sort/move pipeline, labelling is fully automatic — there is no human approval step, results are written straight to the sheet and to PostgreSQL.

Taxonomy

Defined in LABEL_TAXONOMY in main.py:

Code	Service
`AUDIT`	Audit & Discovery
`DASHBOARD`	Dashboarding & Reporting
`ATTRIBUTION`	Advanced Attribution
`SEGMENTATION`	Customer Segmentation
`ANALYTICS`	Analytics & Implementation
`DATA-ENGINEERING`	Data Engineering & Pipelines
`DOCUMENTATION`	Training & Documentation
`PROPOSAL`	Proposal / SOW
`OPERATIONS`	Project Operations
`DATA`	Raw Data & Validation
`UNKNOWN`	Could Not Determine

A file is labelled by the service being delivered, not the document format — e.g. a dashboard that's actually about attribution modelling gets ATTRIBUTION, not DASHBOARD. Most files get exactly one label; multiple labels are only assigned when a filename genuinely covers two distinct services.

Two-stage labelling

Keyword pre-match (free) — keyword_label() scores the filename + client folder against LABEL_KEYWORDS, with LABEL_NEGATIVE_RULES suppressing known false positives (e.g. "audit" in the name blocks a stray ANALYTICS match) and a compound rule so "audit" + "recommendation" resolves to AUDIT, not ANALYTICS. Matches scoring ≥90 confidence are accepted without calling Claude.
Claude fallback — everything else goes to claude-haiku-4-5-20251001 in batches of 50, grounded with manually-verified few-shot examples loaded from labelling_examples.csv at call time. Any code Claude returns that isn't in LABEL_TAXONOMY is discarded; files with no valid label fall back to UNKNOWN.

Runs are resumable across invocations exactly like the audit pipeline — a Labels YYYY-MM-DD HH:MM tab is created on the first run and reused (via the is_labelled column) until all files are processed, then renamed with a ✓ suffix and a summary email is sent.

PostgreSQL storage

Each labelled batch is upserted into gdrive_intelligence.drive_file_labels (keyed on file_id) via the Cloud SQL Python Connector. This table isn't created automatically — provision it once before running ?action=label:

CREATE SCHEMA IF NOT EXISTS gdrive_intelligence;

CREATE TABLE gdrive_intelligence.drive_file_labels (
    file_id         TEXT PRIMARY KEY,
    file_name       TEXT,
    client_folder   TEXT,
    service_labels  TEXT[],
    service_names   TEXT[],
    confidence      TEXT,
    label_reason    TEXT,
    labelled_by     TEXT,
    folder_type     TEXT,
    depth           INT,
    labelled_at     TIMESTAMPTZ DEFAULT NOW()
);

Dashboard stats endpoint

?action=stats runs a set of analysis queries against that table and returns JSON — label distribution, confidence/labeller breakdown, top clients by service diversity, client "journey stage" (audit-only vs. core vs. full-stack), service co-occurrence, single-service clients, audit-only/analytics-only client lists, clients with a high UNKNOWN rate, clients missing documentation, and cross-sell bundle counts. It's CORS-enabled (Access-Control-Allow-Origin: *, plus an OPTIONS preflight handler on drive_manager()) so it can be called directly from a browser-based dashboard.

How the AI Matching Works

Folder pre-filtering (700 → ≤120)

Before each Claude call, the full folder list is reduced to the most relevant candidates:

Strategy A: For each file in the batch, score all 700 folders using token_set_ratio on the full normalised filename. Keep top 50 per file.
Strategy B: Add any folder sharing a 4+ character token with any file in the batch (safety net for Strategy A misses).
Cap at 120: If the union exceeds 120, rank by relevance and trim.

Claude only ever sees ≤120 folders — never all 700. This dramatically reduces hallucination.

Validation gate

Every folder name Claude returns is fuzzy-matched back against the real folder list. Anything scoring below 85 is rejected and the row stays UNMATCHED. Claude cannot hallucinate a destination.

Client name extraction

The normaliser strips file extensions, .com/.io domain suffixes, date patterns, and business suffixes (Inc, LLC, Ltd) before scoring. extract_best_token() finds the client name even when it appears in the middle or end of a filename:

"Audit - Nov - Ferguson Roofing - GTM" → "Ferguson Roofing"
"Report - 2026-03 - Trupanion AU"      → "Trupanion AU"

Contributing

PRs welcome. Open an issue first for anything beyond small fixes.

Particularly wanted:

Support for SharePoint / OneDrive
Multi-language filename normalisation
Slack notification instead of email
Web UI for the approval step

Built With

Anthropic Claude — AI matching and service labelling
Google Drive API v3 — file scanning and moving
gspread — Google Sheets integration
RapidFuzz — fuzzy matching
GCP Cloud Functions — serverless execution
Cloud SQL for PostgreSQL via the Cloud SQL Python Connector + pg8000 — service label storage for the dashboard

License

MIT — see LICENSE

Built by Marketlytics · Pakistan's leading data analytics agency

Name		Name	Last commit message	Last commit date
Latest commit History 4 Commits
.gcloudignore		.gcloudignore
.gitignore		.gitignore
Company Cookbook - Service Delivery Items.docx		Company Cookbook - Service Delivery Items.docx
LICENSE		LICENSE
Readme.md		Readme.md
config.yaml		config.yaml
gdrive_sorting_utility_flowchart.svg		gdrive_sorting_utility_flowchart.svg
labelling_examples.csv		labelling_examples.csv
main.py		main.py
requirements.txt		requirements.txt
~$mpany Cookbook - Service Delivery Items.docx		~$mpany Cookbook - Service Delivery Items.docx

Uh oh!

Folders and files

Latest commit

History

Repository files navigation