sheet_tool.py

# sheet_tool.py
from google.oauth2.service_account import Credentials
import gspread
import pandas as pd
import matplotlib.pyplot as plt
import io
import base64
from typing import Tuple

# CONFIG - change these
CREDENTIALS_FILE = "credentials.json"   # path to your service account JSON
SHEET_ID = "1nOekWGmPsjoHj9T-MFjlNGSFyKPRogVbEjFNRxGgxuM"  # replace with your sheet id
WORKSHEET_INDEX = 0  # first sheet

SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive",
]

def authorize_gs():
    creds = Credentials.from_service_account_file(CREDENTIALS_FILE, scopes=SCOPES)
    client = gspread.authorize(creds)
    return client

def fetch_sheet_as_df(sheet_id: str = SHEET_ID, worksheet_index: int = WORKSHEET_INDEX) -> pd.DataFrame:
    client = authorize_gs()
    sh = client.open_by_key(sheet_id)
    worksheet = sh.get_worksheet(worksheet_index)
    data = worksheet.get_all_values()
    if not data:
        return pd.DataFrame()
    df = pd.DataFrame(data[1:], columns=data[0])
    # try to convert numeric columns where possible
    for col in df.columns:
        df[col] = pd.to_numeric(df[col], errors="ignore")
    return df

def create_pivot(df: pd.DataFrame, index: list, columns: list, values: list, aggfunc: str = "sum") -> pd.DataFrame:
    if df.empty:
        return pd.DataFrame()
    
    # Create pivot table
    pivot = pd.pivot_table(df, index=index, columns=columns, values=values, aggfunc=aggfunc, fill_value=0)
    
    # Flatten multi-index columns for readability
    pivot = pivot.reset_index()
    pivot.columns = [
        " ".join(map(str, c)).strip() if isinstance(c, tuple) else str(c).strip()
        for c in pivot.columns
    ]
    return pivot



def summary_stats(df: pd.DataFrame, numeric_only: bool = True) -> pd.DataFrame:
    if df.empty:
        return pd.DataFrame()
    return df.describe(include="all") if not numeric_only else df.describe()

def plot_dataframe(df: pd.DataFrame, kind: str = "bar", x: str = None, y: list = None, title: str = None, figsize=(8,5)) -> str:
    """
    Creates a matplotlib plot and returns a base64 PNG data URI.
    """
    if df.empty:
        raise ValueError("DataFrame is empty")

    plt.close('all')
    fig, ax = plt.subplots(figsize=figsize)

    if kind == "bar":
        if x is None or y is None:
            df.plot(kind="bar", ax=ax)
        else:
            df.plot(kind="bar", x=x, y=y, ax=ax)
    elif kind == "line":
        df.plot(kind="line", x=x, y=y, ax=ax)
    elif kind == "pie":
        df.set_index(x)[y].plot(kind="pie", ax=ax, autopct='%1.1f%%')
    else:
        df.plot(kind=kind, x=x, y=y, ax=ax)

    if title:
        ax.set_title(title)
    ax.grid(True)
    buf = io.BytesIO()
    fig.tight_layout()
    fig.savefig(buf, format="png")
    buf.seek(0)
    b64 = base64.b64encode(buf.read()).decode("utf-8")
    return "data:image/png;base64," + b64

def df_to_csv_bytes(df: pd.DataFrame) -> bytes:
    return df.to_csv(index=False).encode("utf-8")