sort-inbox-by-type

Manage Gmail inbox filters and labels through a Google Sheet + Apps Script system.

Status: V1 - Sheet + Apps Script structure ready | Gmail API integration coming

Quick Start

1. Spreadsheet Created ✓

• ID: 10TZ_rN9cRvfMTFd34F_7QGzF4Ee0S4l7cFx7L2XeWxA
• URL: https://docs.google.com/spreadsheets/d/10TZ_rN9cRvfMTFd34F_7QGzF4Ee0S4l7cFx7L2XeWxA/edit

2. Sheets Structure

• Main: Canonical source of truth with all classified senders
  • Email Address | Domain | Last Seen | Type | Category | Originator | Daily Check | Weekly Check | Tags | Status | Gmail Sync
• Unsorted: Staging area for newly discovered unlabeled emails
  • Same headers as Main

3. Add Apps Script

Automated — scripts/deploy-apps-script.js creates the bound project and pushes the code via Nango. See Deploying for prerequisites.

export NANGO_API_KEY='<claude environment key>'
node scripts/deploy-apps-script.js

Manual fallback:

1. Use the checked-in Code.gs as-is -- it is the source of truth and is edited by hand, not generated
2. Open the spreadsheet
3. Click Extensions > Apps Script
4. Paste the contents of Code.gs
5. Click Save (Ctrl+S)
6. Close and refresh the sheet

4. Use the System

After adding the Apps Script, you'll see a "Gmail Manager" menu with:

• Refresh Unsorted: Scan Gmail for unlabeled emails and add to Unsorted sheet (max 100)
• Push Ready Senders: Move classified senders from Unsorted → Main
• Sort Main: Sort by Type → Category → Originator
• Sync Main → Gmail: (Coming soon) Create labels and filters
• Sync Everything: Run all operations

Architecture

Deployment Phase (your machine):

Your Nango (localhost:3004) 
  ↓ [via Drive + Apps Script APIs]
Google Cloud → Deploy script to your account

Runtime Phase (Google's cloud):

Gmail (unlabeled emails)
    ↓
Apps Script (native GmailApp/SpreadsheetApp APIs)
    ↓
Unsorted Sheet (you classify Type/Category/Originator)
    ↓
Push Ready Senders
    ↓
Main Sheet (canonical, sorted, complete)
    ↓
(Future) Sync → Gmail creates labels/filters via Gmail API

Key Point: Nango is only used for deployment. Once deployed, the Apps Script runs in Google's cloud with full native API access (no localhost needed).

Deploying

Prerequisites

The Apps Script API needs two switches, and the second one is easy to miss:

1. Enabled on the GCP project — console.cloud.google.com/apis/library/script.googleapis.com (project buoyant-sum-510719-g4)
2. Allowed for the user account — script.google.com/home/usersettings, signed in as twofreejetskis99@gmail.com

Switch 2 is per-account, is not implied by any OAuth scope, and must be set by hand — the toggle does not respond to browser automation. Without it every call returns 403 PERMISSION_DENIED naming this exact URL.

Full detail: Apps Script API Access.

Running it

export NANGO_API_KEY='<claude environment key>'   # see wiki: Account API Key Workflow
node scripts/deploy-apps-script.js

CONNECTION_ID and PROVIDER_CONFIG_KEY default to the current Google connection and can be overridden by environment variable.

Gotchas this script already works around

These each cost time on 2026-10-06 and are written up in the wiki — don't rediscover them:

Trap	What actually happens
Provider name in the proxy path	Nango appends everything after /proxy/ to the provider base_url and reads the provider from the header. /proxy/google/drive/v3/files becomes .../google/drive/v3/files upstream → Google's own HTML 404. An HTML body from /proxy means the path is wrong, not the auth.
Connection-Id: default	No such connection. Nango reports it as a bare server_error: Failed to get connection, which reads like expired credentials. It must be the connection's UUID.
Apps Script on www.googleapis.com	It isn't there — it lives on script.googleapis.com. Those calls pass a Base-Url-Override header.
Creating the project via Drive	application/vnd.google-apps.script+json is export-only. Posting it to /drive/v3/files returns 500 Internal Error. Use POST /v1/projects on the Apps Script API, with parentId set to the spreadsheet so the project is bound and gets its onOpen menu.
expired_oauth2_with_refresh_token in Nango logs	Routine refresh cron. Appears constantly on healthy connections. Not a symptom of anything.
:createExecutionRequest	Not a real Apps Script API method. Calls fall through to Drive's web front end and return an HTML 404 in the account's own language. There is no manifest endpoint either.
Content PUT dropping the manifest	PUT /v1/projects/{id}/content replaces all files. Send appsscript (JSON) and Code (SERVER_JS) together. Names carry no extension.
Deployments and triggers	A bound script needs neither. onOpen is a simple trigger that fires on container open; deployments are for web apps and add-ons.

Which project it targets

Re-running is safe: the script reuses the existing project rather than binding a new one each time.

It has to, because a bound project cannot be searched for. It is a child of the spreadsheet, not a Drive file — it never shows up in /drive/v3/files, and the Apps Script API has no list method. So the id is remembered in .script-project-id at the project root, written on create and read on every later run. Override with SCRIPT_PROJECT_ID. If the remembered project is gone (404/403), a fresh one is created and the file is rewritten.

Deleting a stray bound project is UI-only — script.google.com/home/projects/{scriptId} → bin icon. Go by ID: identically titled projects can't be told apart in the project list, and the confirmation is Delete forever with no trash.

Features

V1 (Current)

• ✓ Sheet structure (Main + Unsorted)
• ✓ Discovery: Pull first 100 unlabeled senders
• ✓ Classification: UI for assigning Type/Category/Originator
• ✓ Sorting: Auto-sort Main by Type → Category → Originator
• ✓ Deduplication: Prevent duplicate entries

V2 (Planned)

• Gmail label creation (via Gmail API)
• Filter generation (Nango → Gmail API)
• Retroactive label application
• Automatic daily discovery
• Support for tags (Daily Check, Weekly Check, Priority, etc.)
• Tag-based filter generation

File Structure

Everything specific to this spreadsheet lives in this folder:

sort-inbox-by-type/
├── README.md (this file)
├── config.py / config.js           # Sheet id, title, headers, tabs, row floor
├── Code.gs                         # Apps Script source (hand-maintained)
├── .script-project-id              # Bound Apps Script project id
├── scripts/
│   ├── deploy-apps-script.js       # Create + deploy the bound script via Nango
│   ├── apply-dependent-validation.py  # Rebuild the dependent dropdowns via API
│   ├── setup-inbox-sheet.py        # Bootstrap: title + headers
│   ├── setup-settings.py           # Settings sheet + Labels import
│   ├── fix-sheet-setup.py          # Repairs title/tab names/headers
│   └── format-sheets.py            # Formatting, with --title and --sheets
└── docs/
    ├── deployment.md
    └── nango-setup.md

All of the above read their sheet id, title, headers and tab names from config.py / config.js — change the spreadsheet in one place.

Generic, reusable pieces live at the workspace root:

../lib/nango_sheets.py              # Nango auth + Sheets client + builders
../lib/nango-sheets.js              # Node counterpart
../scripts/setup-apps-script-and-drive.sh   # Nango scope config (gated)
../docs/apps-script-drive-setup.md  # Guide for the above
../.env                             # Google OAuth credentials

Next Steps

1. ✓ Open the spreadsheet and verify structure
2. ✓ Add the Apps Script code
3. ✓ Test discovery: Click "Refresh Unsorted"
4. ✓ Classify a few senders (set Type, Category, Originator)
5. ✓ Test push: Click "Push Ready Senders"
6. ⏭️ (When ready) Implement Gmail sync to create filters

Schema

Type

The email category type (e.g., "Messages", "Notifications", "Newsletters")

Category

Sub-category under Type (e.g., "Travel", "Shopping", "Work")

Originator

Specific sender/domain (e.g., "Uber", "Nike", "Gmail Alerts")

Example full path: Notifications/Travel/Uber

Optional Tags

• Daily Check: Review daily
• Weekly Check: Weekly review
• Priority: Urgent/important
• Custom tags as needed

Questions?

See the workspace root (../) for system configuration: .env, lib/, and docs/apps-script-drive-setup.md.