#!/usr/bin/env python3 """ Column Auto-Detection Script for SDR Status Tracker Reads header rows from the Google Sheet and generates column_config.json with detected week configurations and monthly column positions. Usage: python detect_columns.py # Generate config python detect_columns.py --dry-run # Preview without writing file """ import os import re import json import argparse from datetime import datetime from google.oauth2 import service_account from googleapiclient.discovery import build # Configuration SHEET_ID = os.environ.get("GOOGLE_SHEET_ID", "1af6-2KsRqeTQxdw5KVRp2WCrM6RT7HIcl70m-GgGZB4") SHEET_NAME = "DAILY - for SDR to add data🌟" CONFIG_FILE = "column_config.json" def get_sheets_service(): """Create Google Sheets API service using service account credentials.""" creds_json = os.environ.get("GOOGLE_CREDENTIALS") if not creds_json: raise RuntimeError("GOOGLE_CREDENTIALS environment variable not set") creds_dict = json.loads(creds_json) credentials = service_account.Credentials.from_service_account_info( creds_dict, scopes=["https://www.googleapis.com/auth/spreadsheets.readonly"] ) return build("sheets", "v4", credentials=credentials) def fetch_header_rows(service): """Fetch the first 4 rows from the sheet (headers).""" result = service.spreadsheets().values().get( spreadsheetId=SHEET_ID, range=f"'{SHEET_NAME}'!A1:AZ4" # Wide range to capture all columns ).execute() return result.get("values", []) def detect_week_markers(row1): """ Detect WEEK markers from Row 1. Returns list of (week_num, start_col) tuples. Pattern: "WEEK 2", "WEEK 3", etc. """ week_pattern = re.compile(r"WEEK\s*(\d+)", re.IGNORECASE) weeks = [] for col_idx, cell in enumerate(row1): if cell: match = week_pattern.search(str(cell)) if match: week_num = int(match.group(1)) weeks.append((week_num, col_idx)) return weeks def detect_daily_columns(row4, week_start_col, next_week_start=None): """ Detect daily columns within a week block. Daily columns contain day-of-month numbers (1-31) in Row 4. Returns (daily_start, daily_end, target_col, pct_col). """ # Search range: from week_start to next_week_start (or end of row) end_col = next_week_start if next_week_start else len(row4) daily_start = None daily_end = None target_col = None pct_col = None for col_idx in range(week_start_col, end_col): if col_idx >= len(row4): break cell = str(row4[col_idx]).strip().lower() if col_idx < len(row4) else "" # Check if it's a day number (1-31) if cell.isdigit() and 1 <= int(cell) <= 31: if daily_start is None: daily_start = col_idx daily_end = col_idx # Check for TARGET column if "target" in cell or cell == "t": target_col = col_idx # Check for PERCENTAGE column (often contains % or "pct" or just a number) if "%" in cell or "pct" in cell.lower(): pct_col = col_idx # If no explicit pct_col found, it's usually right after target if target_col is not None and pct_col is None: pct_col = target_col + 1 return daily_start, daily_end, target_col, pct_col def detect_monthly_columns(row4): """ Detect monthly TARGET and ACTUAL columns. These are typically labeled "TARGET" and "ACTUAL" (or similar) near the end. """ target_col = None actual_col = None for col_idx, cell in enumerate(row4): cell_str = str(cell).strip().lower() if cell else "" # Look for monthly target (usually labeled differently from weekly) if "monthly" in cell_str or (col_idx > 25 and "target" in cell_str): if target_col is None: target_col = col_idx # Look for monthly actual if "actual" in cell_str or (col_idx > 25 and col_idx == target_col + 1): actual_col = col_idx # Default fallback based on current known structure if target_col is None: target_col = 32 # Column AG if actual_col is None: actual_col = 33 # Column AH return target_col, actual_col def detect_columns(): """ Main detection function. Returns the detected configuration as a dictionary. """ print("Connecting to Google Sheets API...") service = get_sheets_service() print(f"Fetching headers from sheet: {SHEET_NAME}") headers = fetch_header_rows(service) if len(headers) < 4: raise RuntimeError(f"Expected at least 4 header rows, got {len(headers)}") row1 = headers[0] if len(headers) > 0 else [] row4 = headers[3] if len(headers) > 3 else [] print(f"Row 1 has {len(row1)} columns") print(f"Row 4 has {len(row4)} columns") # Detect week markers week_markers = detect_week_markers(row1) print(f"Detected {len(week_markers)} week markers: {week_markers}") # Detect columns for each week weeks_config = [] for i, (week_num, start_col) in enumerate(week_markers): # Get next week's start column (or None for last week) next_start = week_markers[i + 1][1] if i + 1 < len(week_markers) else None daily_start, daily_end, target_col, pct_col = detect_daily_columns( row4, start_col, next_start ) week_config = { "week_num": week_num, "daily_start": daily_start, "daily_end": daily_end, "target_col": target_col, "pct_col": pct_col } weeks_config.append(week_config) print(f" Week {week_num}: daily={daily_start}-{daily_end}, target={target_col}, pct={pct_col}") # Detect monthly columns monthly_target, monthly_actual = detect_monthly_columns(row4) print(f"Monthly columns: target={monthly_target}, actual={monthly_actual}") config = { "generated_at": datetime.utcnow().isoformat() + "Z", "sheet_id": SHEET_ID, "sheet_name": SHEET_NAME, "weeks": weeks_config, "monthly": { "target_col": monthly_target, "actual_col": monthly_actual } } return config def main(): parser = argparse.ArgumentParser(description="Detect column mappings from Google Sheet") parser.add_argument("--dry-run", action="store_true", help="Preview config without writing file") args = parser.parse_args() try: config = detect_columns() print("\n" + "=" * 50) print("Detected Configuration:") print("=" * 50) print(json.dumps(config, indent=2)) if args.dry_run: print("\n[DRY RUN] Config not written to file") else: # Write config file with open(CONFIG_FILE, "w") as f: json.dump(config, f, indent=2) print(f"\nConfig written to {CONFIG_FILE}") return 0 except Exception as e: print(f"Error: {e}") return 1 if __name__ == "__main__": exit(main())