🧠 SheetBrain AI

AI-Powered Formula Auditor for Google Sheets

Intelligent formula auditing with AI-powered insights, policy compliance checks, and real-time recommendations

🚀 Live Demo • 📖 Documentation • 🐛 Report Bug

📋 Table of Contents

Overview
Key Features
Architecture
Tech Stack
Getting Started
Deployment
Usage
API Reference
Project Structure
Contributing
License

🌟 Overview

SheetBrain AI is an intelligent Google Sheets add-on that automatically audits spreadsheet formulas using advanced AI models. It analyzes formulas for compliance, best practices, potential errors, and performance issues—providing actionable recommendations in real-time.

Why SheetBrain AI?

✅ Automated Compliance - Enforce organizational policies across all spreadsheets
🔍 Error Detection - Catch circular references, division by zero, and logic errors
📊 Performance Optimization - Identify slow formulas and suggest faster alternatives
🤖 AI-Powered Analysis - Leverages Claude 3.5 Sonnet via OpenRouter for intelligent insights
🎯 Risk Assessment - Categorizes formulas by risk level (low/medium/high)
📝 Contextual Recommendations - Provides specific, actionable improvement suggestions

✨ Key Features

🎯 Smart Formula Auditing

Real-time Analysis - Instant feedback on formula quality and compliance
Multi-Formula Support - Audit entire ranges in a single request
Context-Aware - Understands sheet purpose, department, and organizational context
Policy Enforcement - Custom policy rules with default best practices

🛡️ Security & Authentication

Clerk Integration - Enterprise-grade authentication
JWT Tokens - Secure API access with 15-minute access tokens
Organization Support - Multi-tenant architecture with org-level policies
Rate Limiting - 100 requests/minute per user

📊 Comprehensive Results

Compliance Status - Clear pass/fail indicators for each formula
Risk Scoring - Low, medium, high risk classification
Issue Tracking - Detailed list of problems found
Recommendations - Specific suggestions for improvement
Performance Metrics - Audit duration and token usage

🎨 Modern UI

Google Sheets Integration - Native sidebar interface
Responsive Design - Clean, intuitive user experience
Visual Indicators - Color-coded risk levels and status badges
Real-time Feedback - Loading states and progress indicators

🏗️ Architecture

┌─────────────────┐
│  Google Sheets  │
│    Add-on UI    │
└────────┬────────┘
         │ HTTPS
         ▼
┌─────────────────────────────────────┐
│      Vercel (Backend)               │
│  ┌──────────────────────────────┐   │
│  │   Next.js 15 App Router      │   │
│  │  ┌────────┐    ┌──────────┐  │   │
│  │  │ Auth   │    │  Audit   │  │   │
│  │  │  API   │───▶│   API    │  │   │
│  │  └────────┘    └──────────┘  │   │
│  │       │              │        │   │
│  │       ▼              ▼        │   │
│  │  ┌────────┐    ┌──────────┐  │   │
│  │  │ Clerk  │    │OpenRouter│  │   │
│  │  │  JWT   │    │ (Claude) │  │   │
│  │  └────────┘    └──────────┘  │   │
│  └──────────────────────────────┘   │
└─────────────────────────────────────┘

Data Flow

User Action - Select formulas in Google Sheets
Authentication - Apps Script obtains JWT token via debug-login
Audit Request - Send formulas + context to /api/audit
AI Processing - OpenRouter analyzes with Claude 3.5 Sonnet
Response - Structured results with compliance, risks, recommendations
Display - Render results in sidebar with visual indicators

🛠️ Tech Stack

Backend

Framework - Next.js 15 (App Router)
Language - TypeScript 5.x
Authentication - Clerk with JWT
AI/LLM - OpenRouter + Anthropic SDK
Deployment - Vercel (Portland, pdx1)
Package Manager - pnpm 10.x

Frontend (Apps Script)

Platform - Google Apps Script
Build Tool - Webpack 5
Language - TypeScript compiled to ES5
Deployment - clasp

Key Dependencies

@anthropic-ai/sdk - Claude AI integration
jose - JWT handling
next - React framework
typescript - Type safety
webpack - Module bundler

🚀 Getting Started

Prerequisites

Node.js 20.x or higher
pnpm 10.x or higher
Google Account with Apps Script enabled
Vercel Account (for deployment)
API Keys:
- Clerk API key
- OpenRouter API key

Installation

Clone the repository

git clone https://github.com/yocho1/SheetBrain-AI.git
cd SheetBrain-AI

Install dependencies

pnpm install

Configure environment variables

cd backend
cp .env.example .env.local

Edit .env.local with your credentials:

# Required
CLERK_SECRET_KEY=sk_test_xxx...
SESSION_SECRET=your-32-char-secret
OPENROUTER_API_KEY=sk-or-v1-xxx...
LLM_PROVIDER=openrouter
STRICT_AUDIT=true

# Optional
NEXT_PUBLIC_API_BASE_URL=http://localhost:3000
NODE_ENV=development

Start development server

pnpm dev

Backend will run at http://localhost:3000

Testing the API

PowerShell:

# Get JWT token
$jwt = (Invoke-RestMethod -Uri "http://localhost:3000/api/auth/debug-login" -Method Post -Body (@{
  userId="test_user"
  email="test@sheetbrain.com"
  orgId="test_org"
  role="editor"
} | ConvertTo-Json) -ContentType "application/json").accessToken

# Test audit endpoint
$body = @{
  range = "A1:A3"
  context = @{
    sheetName="Sheet1"
    range="A1:A3"
    organization="Test Corp"
    department="Finance"
    sheetPurpose="Formula Analysis"
    data=@{
      formulas=@(
        @("=SUM(B1:B10)", "", "")
        @("=IF(C1>100,'High','Low')", "", "")
        @("=VLOOKUP(D1,E1:F10,2,FALSE)", "", "")
      )
    }
  }
} | ConvertTo-Json -Depth 6

Invoke-RestMethod -Uri "http://localhost:3000/api/audit" -Method Post -Headers @{ Authorization = "Bearer $jwt" } -Body $body -ContentType "application/json" | ConvertTo-Json -Depth 6

Bash:

# Get JWT token
JWT=$(curl -s -X POST http://localhost:3000/api/auth/debug-login \
  -H "Content-Type: application/json" \
  -d '{"userId":"test_user","email":"test@sheetbrain.com","orgId":"test_org","role":"editor"}' | jq -r '.accessToken')

# Test audit endpoint
curl -X POST http://localhost:3000/api/audit \
  -H "Authorization: Bearer $JWT" \
  -H "Content-Type: application/json" \
  -d '{
    "range": "A1:A3",
    "context": {
      "sheetName": "Sheet1",
      "organization": "Test Corp",
      "data": {
        "formulas": [["=SUM(B1:B10)"], ["=IF(C1>100,\"High\",\"Low\")"], ["=VLOOKUP(D1,E1:F10,2,FALSE)"]]
      }
    }
  }'

🚀 Deployment

Backend (Vercel)

Push to GitHub

git add .
git commit -m "feat: initial deployment"
git push origin main

Deploy to Vercel

cd backend
vercel --prod

Configure environment variables in Vercel Dashboard:
- CLERK_SECRET_KEY
- SESSION_SECRET
- OPENROUTER_API_KEY
- STRICT_AUDIT=true
- LLM_PROVIDER=openrouter
Set Root Directory to backend in Vercel project settings

Production URL: https://sheetbrain-ai.vercel.app

Apps Script Add-on

Build the add-on

cd apps-script
pnpm build

Update backend URL in src/index.ts:

return 'https://sheetbrain-ai.vercel.app';

Deploy to Google Apps Script

pnpm clasp login
pnpm clasp create --type sheets --title "SheetBrain AI"
pnpm clasp push --force

Open your Google Sheet and paste the code from apps-script-standalone-code.js
Run onOpen function from Apps Script editor to create the menu

📖 Usage

In Google Sheets

Open your Google Sheet
Access SheetBrain AI
- Look for "SheetBrain AI" menu in the top menu bar
- Click "SheetBrain AI" → "Open Audit Panel"
Select formulas to audit
- Click on cells containing formulas
- Select a range (e.g., A1:A10)
Run audit
- Click "Run Audit" button in the sidebar
- Wait for AI analysis (typically 2-5 seconds)
Review results
- ✓ Compliant formulas (green)
- ✗ Non-compliant formulas (red)
- Risk levels: Low, Medium, High
- Detailed issues and recommendations

Example Results

Formula 1: A1
✓ COMPLIANT  Risk: LOW
=SUM(B1:B10)
✓ No issues found

Formula 2: A2
✗ NON-COMPLIANT  Risk: HIGH
=SUM(B1:B10)/0
⚠ Issues:
  - Division by zero error
  - Will produce #DIV/0! error
💡 Recommendations:
  - Add error handling: =IFERROR(SUM(B1:B10)/C1, 0)
  - Validate divisor is non-zero

📚 API Reference

Authentication

Debug Login (Development Only)

POST /api/auth/debug-login
Content-Type: application/json

{
  "userId": "test_user",
  "email": "test@example.com",
  "orgId": "test_org",
  "role": "editor"
}

Response:

{
  "accessToken": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
}

Audit Endpoint

Audit Formulas

POST /api/audit
Authorization: Bearer <access_token>
Content-Type: application/json

{
  "range": "A1:A5",
  "context": {
    "sheetName": "Q1 Report",
    "organization": "Acme Corp",
    "department": "Finance",
    "sheetPurpose": "Quarterly reconciliation",
    "data": {
      "formulas": [
        ["=SUM(B1:B10)"],
        ["=IF(C1>100,'High','Low')"],
        ["=VLOOKUP(D1,Sheet2!A:B,2,FALSE)"]
      ]
    }
  }
}