sheets_client.py

#!/usr/bin/env python3
"""
Google Sheets Client - Abstracted sheet access functionality.
"""

from typing import Optional, List
import gspread
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
import pickle
import os.path


# Scopes for Google Sheets API
SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets.readonly'
]

# Token file to store user credentials
TOKEN_FILE = "token.pickle"
CREDENTIALS_FILE = "credentials.json"


class GoogleSheetsClient:
    """Handles Google Sheets authentication and access."""
    
    def __init__(self, credentials_path: str = CREDENTIALS_FILE):
        """
        Initialize the Google Sheets client.
        
        Args:
            credentials_path: Path to the OAuth2 client credentials JSON file
        """
        self.credentials_path = credentials_path
        self.client: Optional[gspread.Client] = None
        self._authenticated = False
        
    def authenticate(self) -> None:
        """
        Authenticate with Google Sheets API using OAuth2 flow.
        Opens browser for user to login with Google account.
        """
        creds: Optional[Credentials] = None
        
        # Check if we have saved credentials
        if os.path.exists(TOKEN_FILE):
            with open(TOKEN_FILE, 'rb') as token:
                creds = pickle.load(token)
        
        # If no valid credentials, let user log in
        if not creds or not creds.valid:
            if creds and creds.expired and creds.refresh_token:
                print("Refreshing expired credentials...")
                creds.refresh(Request())
            else:
                if not os.path.exists(self.credentials_path):
                    raise FileNotFoundError(
                        f"Credentials file not found: {self.credentials_path}\n"
                        "Please download OAuth2 credentials from Google Cloud Console:\n"
                        "1. Go to https://console.cloud.google.com/\n"
                        "2. Enable Google Sheets API\n"
                        "3. Create OAuth2 credentials (Desktop app)\n"
                        "4. Download as 'credentials.json'"
                    )
                print("Opening browser for Google login...")
                flow = InstalledAppFlow.from_client_secrets_file(
                    self.credentials_path, SCOPES
                )
                creds = flow.run_local_server(port=0)
            
            # Save credentials for future use
            with open(TOKEN_FILE, 'wb') as token:
                pickle.dump(creds, token)
        
        self.client = gspread.authorize(creds)
        self._authenticated = True
    
    @property
    def is_authenticated(self) -> bool:
        """Check if the client is authenticated."""
        return self._authenticated and self.client is not None
    
    def open_spreadsheet(self, spreadsheet_id: str) -> gspread.Spreadsheet:
        """
        Open a spreadsheet by its ID.
        
        Args:
            spreadsheet_id: The Google Sheets spreadsheet ID
            
        Returns:
            gspread.Spreadsheet object
        """
        if not self.is_authenticated:
            raise RuntimeError("Must authenticate before accessing spreadsheets")
        
        return self.client.open_by_key(spreadsheet_id)
    
    def get_worksheet_data(self, spreadsheet_id: str, sheet_name: str) -> List[List[str]]:
        """
        Get all data from a specific worksheet.
        
        Args:
            spreadsheet_id: The Google Sheets spreadsheet ID
            sheet_name: The name of the worksheet
            
        Returns:
            List of rows, where each row is a list of cell values
        """
        spreadsheet = self.open_spreadsheet(spreadsheet_id)
        worksheet = spreadsheet.worksheet(sheet_name)
        return worksheet.get_all_values()
    
    def get_first_worksheet_data(self, spreadsheet_id: str) -> List[List[str]]:
        """
        Get all data from the first worksheet in a spreadsheet.
        
        Args:
            spreadsheet_id: The Google Sheets spreadsheet ID
            
        Returns:
            List of rows, where each row is a list of cell values
        """
        spreadsheet = self.open_spreadsheet(spreadsheet_id)
        worksheet = spreadsheet.get_worksheet(0)
        return worksheet.get_all_values()