src/google_sheets_uploader.py

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
import os
import json

def get_gspread_client():
    scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
    
    google_creds_json = os.environ.get("GOOGLE_CREDENTIALS_JSON")

    if google_creds_json:
        # Use credentials from Hugging Face secret
        creds_dict = json.loads(google_creds_json)
        creds = ServiceAccountCredentials.from_json_keyfile_dict(creds_dict, scope)
    else:
        # Fallback to local credentials file
        creds = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope)
        
    client = gspread.authorize(creds)
    return client

def upload_to_google_sheets(session_data):
    client = get_gspread_client()
    sheet = client.open("Bipolar Chatbot Logs").sheet1
    
    # Convert session_data to a pandas DataFrame
    df = pd.DataFrame(session_data)
    
    # Get existing headers from the sheet
    try:
        existing_headers = sheet.row_values(1)
    except gspread.exceptions.APIError:
        existing_headers = []

    # Define column order
    columns = ['query', 'response', 'sources', 'feedback', 'source_feedback']
    df = df[columns]

    # If the sheet is empty, write the headers
    if not existing_headers:
        sheet.append_row(columns)
    
    # Append the DataFrame to the sheet
    for row in df.itertuples(index=False, name=None):
        sheet.append_row(list(row))