# Google Sheets → CSV → Python Pipeline

How to download a Google Sheets document and process it with Python for analysis and charting.

## Quick Download (CSV)

```bash
python3 -c "
import urllib.request
url = 'https://docs.google.com/spreadsheets/d/<SHEET_ID>/export?format=csv&gid=<GID>'
urllib.request.urlretrieve(url, '/tmp/spreadsheet_data.csv')
print('Downloaded successfully')
"
```

**Finding the IDs:**
- `SHEET_ID`: The long string in the spreadsheet URL between `/d/` and `/edit`
- `GID`: The number after `gid=` at the end of the URL

**Example URL:**
```
https://docs.google.com/spreadsheets/d/1x0pAx5uqI-ttgr9x3qk8gah_72oudcINyr2S_ffPxUQ/edit?gid=756284932#gid=756284932
```
→ SHEET_ID = `1x0pAx5uqI-ttgr9x3qk8gah_72oudcINyr2S_ffPxUQ`  
→ GID = `756284932`

## Load and Parse in Python

```python
import csv
from datetime import datetime
from collections import Counter, defaultdict

rows = []
with open('/tmp/spreadsheet_data.csv', 'r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        rows.append(row)

# Parse each row
data = []
for r in rows:
    try:
        waktu = datetime.fromisoformat(r['Waktu '].strip().replace('Z', '+00:00'))
        data.append({
            'waktu': waktu,
            'tanggal': waktu.date(),
            'bulan': waktu.strftime('%Y-%m'),
            'jam': waktu.hour,
            'hari': waktu.strftime('%A'),
            'nomor': r['Nomor Pengirim'].strip(),
            'nama': r['Nama Pengirim'].strip(),
            'pertanyaan': r['pertanyaan'].strip(),
            'jawaban': r['Jawaban AI'].strip(),
        })
    except Exception as e:
        pass  # Skip rows with unparseable timestamps

print(f"Total rows: {len(rows)}")
print(f"Parsed: {len(data)}")
```

## Common Analysis Patterns

```python
# Monthly counts
monthly = Counter(d['bulan'] for d in data)

# Top senders
top_senders = Counter(d['nama'] for d in data).most_common(10)

# Hourly distribution
hourly = Counter(d['jam'] for d in data)

# Unique senders per month
monthly_unique = defaultdict(set)
for d in data:
    monthly_unique[d['bulan']].add(d['nomor'])
```

## Notes

- Column names in DictReader match the CSV header exactly — trailing spaces in column names are common (e.g., `'Waktu '` not `'Waktu'`).
- Timestamps use ISO format with timezone offset (e.g., `2025-09-13T06:12:45.959-04:00`). Use `datetime.fromisoformat()` with `.replace('Z', '+00:00')` to normalize.
- For very large spreadsheets, consider adding pagination or limiting fields at extraction time.