Option 4: WhatsApp Bot via Twilio

mkdir ~/Desktop/tracker-whatsapp-bot
cd ~/Desktop/tracker-whatsapp-bot

import os
import json
from flask import Flask, request
import anthropic
from google.oauth2 import service_account
from googleapiclient.discovery import build
from twilio.twiml.messaging_response import MessagingResponse
from twilio.rest import Client as TwilioClient

app = Flask(__name__)

# --- Config ---
TWILIO_ACCOUNT_SID = os.environ['TWILIO_ACCOUNT_SID']
TWILIO_AUTH_TOKEN = os.environ['TWILIO_AUTH_TOKEN']
TWILIO_WHATSAPP_NUMBER = os.environ['TWILIO_WHATSAPP_NUMBER']  # e.g., 'whatsapp:+14155238886'
ANTHROPIC_KEY = os.environ['ANTHROPIC_KEY']
SHEET_ID = os.environ['SHEET_ID']
SHEET_TAB = os.environ.get('SHEET_TAB', 'TS-03 Quiz & Assignment')

# Whitelist WhatsApp numbers (e.g., "whatsapp:+919876543210")
ALLOWED_NUMBERS = os.environ['ALLOWED_NUMBERS'].split(',')

# --- Clients ---
claude = anthropic.Anthropic(api_key=ANTHROPIC_KEY)
twilio_client = TwilioClient(TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN)

creds = service_account.Credentials.from_service_account_file(
    '/secrets/service-account.json',
    scopes=['https://www.googleapis.com/auth/spreadsheets']
)
sheets_api = build('sheets', 'v4', credentials=creds)

# In-memory session state (use Redis for production)
pending_confirmations = {}

# --- System prompt ---
SYSTEM_PROMPT = f"""You are a WhatsApp tracker bot. Parse the user's message into JSON.

VALID SUBJECTS ONLY:
- FoVL-Language
- GPU Programming
- Ethics and Professional Life
- Innovation and IP Management
- Advanced AI

Sheet columns: #, Subject, Type, Title, Due Date (DD-Mmm-YYYY), Time (HH:MM IST),
Mode (default: Google Classroom), Source (default: GCR), Status (default: Upcoming),
Days Left (skip - formula), Notes

Return ONLY valid JSON, no markdown or extra text:

READ: {{"action": "read", "query": "..."}}
ADD: {{"action": "add", "row": {{"subject": ..., "type": ..., "title": ..., "due_date": ..., "time": ..., "notes": ...}}}}
UPDATE: {{"action": "update_status", "row_num": N, "new_status": "Completed"}}
HELP: {{"action": "help"}}
UNKNOWN: {{"action": "unknown", "message": "..."}}

Convert SGT to IST if needed (subtract 2h 30m).
"""


def read_sheet(query):
    result = sheets_api.spreadsheets().values().get(
        spreadsheetId=SHEET_ID,
        range=f"'{SHEET_TAB}'!A4:K100"
    ).execute()
    rows = result.get('values', [])

    response = claude.messages.create(
        model="claude-sonnet-4-5",
        max_tokens=1024,
        messages=[{
            "role": "user",
            "content": f"Sheet data:\n{json.dumps(rows)}\n\nQuery: {query}\n\nAnswer concisely for WhatsApp (max 1000 chars, use emojis for visual clarity)."
        }]
    )
    return response.content[0].text


def add_row(row_data):
    existing = sheets_api.spreadsheets().values().get(
        spreadsheetId=SHEET_ID,
        range=f"'{SHEET_TAB}'!A:A"
    ).execute().get('values', [])
    next_num = len([r for r in existing if r and r[0].isdigit()]) + 1

    values = [[
        next_num,
        row_data['subject'],
        row_data.get('type', 'Assignment'),
        row_data.get('title', ''),
        row_data.get('due_date', ''),
        row_data.get('time', '23:59 IST'),
        'Google Classroom',
        'GCR',
        'Upcoming',
        '',
        row_data.get('notes', '')
    ]]

    sheets_api.spreadsheets().values().append(
        spreadsheetId=SHEET_ID,
        range=f"'{SHEET_TAB}'!A5",
        valueInputOption='USER_ENTERED',
        body={'values': values}
    ).execute()
    return f"✅ Added #{next_num}: {row_data['subject']} - {row_data.get('title', '')}"


def update_status(row_num, new_status):
    sheet_row = int(row_num) + 4
    sheets_api.spreadsheets().values().update(
        spreadsheetId=SHEET_ID,
        range=f"'{SHEET_TAB}'!I{sheet_row}",
        valueInputOption='USER_ENTERED',
        body={'values': [[new_status]]}
    ).execute()
    return f"✅ Row #{row_num} → {new_status}"


HELP_MESSAGE = """📚 *TS-03 Tracker Bot*

*Read commands:*
• What's due this week?
• Show upcoming items
• Show all GPU Programming items

*Write commands:*
• Add GPU quiz on Oct 15 at 6pm
• Add Ethics assignment due Friday
(Then reply YES to confirm)

*Update commands:*
• Mark row 5 as completed
• Mark Ethics assignment as done

*Other:*
• /help - Show this message
• /whoami - Show your WhatsApp ID"""


@app.route('/whatsapp', methods=['POST'])
def whatsapp():
    incoming_msg = request.values.get('Body', '').strip()
    sender = request.values.get('From', '')  # 'whatsapp:+91xxx'

    resp = MessagingResponse()
    msg = resp.message()

    # --- Auth check ---
    if sender not in ALLOWED_NUMBERS:
        msg.body(f"⛔ Unauthorized. Your number: {sender}\n\nAsk admin to whitelist you.")
        return str(resp)

    # --- Special commands ---
    if incoming_msg.lower() in ['/help', 'help']:
        msg.body(HELP_MESSAGE)
        return str(resp)

    if incoming_msg.lower() == '/whoami':
        msg.body(f"Your ID: {sender}")
        return str(resp)

    # --- Confirmation flow ---
    if incoming_msg.lower() in ['yes', 'y', 'confirm']:
        if sender in pending_confirmations:
            result = add_row(pending_confirmations[sender])
            del pending_confirmations[sender]
            msg.body(result)
        else:
            msg.body("Nothing pending to confirm.")
        return str(resp)

    if incoming_msg.lower() in ['no', 'cancel']:
        pending_confirmations.pop(sender, None)
        msg.body("❌ Cancelled.")
        return str(resp)

    try:
        # --- Parse with Claude ---
        parse_response = claude.messages.create(
            model="claude-sonnet-4-5",
            max_tokens=1024,
            system=SYSTEM_PROMPT,
            messages=[{"role": "user", "content": incoming_msg}]
        )
        raw = parse_response.content[0].text.replace('```json', '').replace('```', '').strip()
        parsed = json.loads(raw)

        # --- Execute ---
        if parsed['action'] == 'read':
            answer = read_sheet(parsed['query'])
            msg.body(answer)

        elif parsed['action'] == 'add':
            row = parsed['row']
            pending_confirmations[sender] = row
            preview = (
                f"📋 *Add this row?*\n\n"
                f"Subject: {row['subject']}\n"
                f"Type: {row.get('type', 'Assignment')}\n"
                f"Title: {row.get('title', '')}\n"
                f"Date: {row.get('due_date', '')}\n"
                f"Time: {row.get('time', '')}\n"
                f"Notes: {row.get('notes', '-')}\n\n"
                f"Reply *YES* to confirm, *NO* to cancel."
            )
            msg.body(preview)

        elif parsed['action'] == 'update_status':
            result = update_status(parsed['row_num'], parsed['new_status'])
            msg.body(result)

        elif parsed['action'] == 'help':
            msg.body(HELP_MESSAGE)

        elif parsed['action'] == 'unknown':
            msg.body(f"🤔 {parsed['message']}\n\nSend /help for examples.")

    except Exception as e:
        msg.body(f"❌ Error: {str(e)[:200]}\n\nSend /help")

    return str(resp)


@app.route('/', methods=['GET'])
def health():
    return 'OK'


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=int(os.environ.get('PORT', 8080)))

Flask==3.0.0
anthropic==0.34.0
twilio==8.10.0
google-api-python-client==2.100.0
google-auth==2.23.0
gunicorn==21.2.0

# Store service account in Secret Manager
gcloud secrets create sheets-service-account --data-file=service-account.json

# Deploy
gcloud run deploy tracker-whatsapp-bot \
  --source . \
  --region asia-southeast1 \
  --allow-unauthenticated \
  --set-env-vars TWILIO_ACCOUNT_SID=AC...,TWILIO_AUTH_TOKEN=xxx,TWILIO_WHATSAPP_NUMBER=whatsapp:+14155238886,ANTHROPIC_KEY=sk-ant-xxx,SHEET_ID=your_sheet_id,ALLOWED_NUMBERS=whatsapp:+919876543210 \
  --set-secrets /secrets/service-account.json=sheets-service-account:latest

gcloud run services update tracker-whatsapp-bot \
  --region asia-southeast1 \
  --update-env-vars ALLOWED_NUMBERS=whatsapp:+919876543210,whatsapp:+911234567890

/help          → shows help menu
what's due?    → returns sheet items
add GPU quiz on Oct 15 6pm  → asks for confirmation
yes            → adds the row

from twilio.request_validator import RequestValidator

validator = RequestValidator(TWILIO_AUTH_TOKEN)

@app.route('/whatsapp', methods=['POST'])
def whatsapp():
    # Validate request came from Twilio
    twilio_signature = request.headers.get('X-Twilio-Signature', '')
    if not validator.validate(request.url, request.form, twilio_signature):
        return 'Unauthorized', 403
    # ... rest of handler

@app.route('/daily-digest', methods=['POST'])
def daily_digest():
    digest = read_sheet("What's due today and tomorrow?")
    for number in ALLOWED_NUMBERS:
        twilio_client.messages.create(
            from_=TWILIO_WHATSAPP_NUMBER,
            to=number,
            body=f"🌅 *Morning Digest*\n\n{digest}"
        )
    return 'ok'

if request.values.get('MediaContentType0', '').startswith('audio'):
    audio_url = request.values.get('MediaUrl0')
    transcript = transcribe_via_whisper(audio_url)
    # Then process transcript as text

Hi all 👋

I've set up a WhatsApp bot for our TS-03 tracker.
You can text it naturally like:

📱 Save this number: +1 415 523 8886
1️⃣ WhatsApp message: join <your-code-here>
2️⃣ Then just ask: "what's due this week?" or "show GPU items"

Bot updates our shared Google Sheet automatically 🚀

Try it and ping me if you get stuck!
— [Your Name]