GoogleSheetsUtils¶

GoogleSheetsUtils makes it easy to read from and write to Google Sheets — a common destination for eval results, test reports, and conversation analytics that need to be shared with non-technical stakeholders.

You'll typically reach for this class after running a batch of evaluations with ToolEvals or SimulationEvals and wanting to push the results DataFrame into a shared spreadsheet automatically.

Quick Example¶

from cxas_scrapi import GoogleSheetsUtils
import pandas as pd

gs = GoogleSheetsUtils(creds_path="/path/to/service_account.json")

# Read data from a sheet
df = gs.read_sheet(
    spreadsheet_id="1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
    sheet_name="Tool Eval Results",
)
print(df.head())

# Write a DataFrame back to a sheet
results_df = pd.DataFrame([
    {"test": "lookup_account", "status": "PASSED", "latency_ms": 145.2},
    {"test": "set_session_state", "status": "FAILED", "latency_ms": 88.0},
])

gs.write_sheet(
    spreadsheet_id="1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
    sheet_name="Tool Eval Results",
    data=results_df,
)
print("Results uploaded to Google Sheets!")

Reference¶

GoogleSheetsUtils ¶

GoogleSheetsUtils(creds_path=None, creds_dict=None, creds=None, scope=None)

Bases: Common

Utility class for dataframe functions and Google Sheets integrations.

Source code in src/cxas_scrapi/utils/google_sheets_utils.py

def __init__(
    self,
    creds_path: str = None,
    creds_dict: Dict[str, str] = None,
    creds: Any = None,
    scope: List[str] = None,
):
    # Ensure sheets scopes are included
    auth_scopes = scope or []
    for s in SHEETS_SCOPE:
        if s not in auth_scopes:
            auth_scopes.append(s)

    super().__init__(
        creds_path=creds_path,
        creds_dict=creds_dict,
        creds=creds,
        scope=auth_scopes,
    )

    try:
        session = AuthorizedSession(self.creds)
        session.headers.update({"User-Agent": self.user_agent})
        self.sheets_client = gspread.authorize(None, session=session)
        # Pre-flight check for local ADC auth
        if "google.colab" not in sys.modules:
            self._preflight_api_checks()
    except Exception as e:
        self.sheets_client = None
        self._handle_api_error(e)

sheets_to_dataframe ¶

sheets_to_dataframe(sheet_name, worksheet_name=None)

Move data from Google Sheets to a pandas DataFrame.

Parameters:

Name	Type	Description	Default
`sheet_name`	`str`	The name of the Google Sheet document.	required
`worksheet_name`	`Optional[str]`	The name of the specific worksheet tab. If None, defaults to the first sheet.	`None`

Returns:

Type	Description
`DataFrame`	A pandas DataFrame containing the sheet data.

Source code in src/cxas_scrapi/utils/google_sheets_utils.py

def sheets_to_dataframe(
    self, sheet_name: str, worksheet_name: Optional[str] = None
) -> pd.DataFrame:
    """Move data from Google Sheets to a pandas DataFrame.

    Args:
        sheet_name: The name of the Google Sheet document.
        worksheet_name: The name of the specific worksheet tab. If None,
            defaults to the first sheet.

    Returns:
        A pandas DataFrame containing the sheet data.
    """
    if not self.sheets_client:
        raise RuntimeError(
            "Sheets client is not authorized. See earlier "
            "initialization errors."
        )

    try:
        g_sheets = self.sheets_client.open(sheet_name)
        if worksheet_name:
            sheet = g_sheets.worksheet(worksheet_name)
        else:
            sheet = g_sheets.sheet1

        data_pull = sheet.get_all_values()

        if not data_pull:
            return pd.DataFrame()

        data = pd.DataFrame(columns=data_pull[0], data=data_pull[1:])
        return data
    except Exception as e:
        self._handle_api_error(e)
        return pd.DataFrame()

dataframe_to_sheets ¶

dataframe_to_sheets(dataframe, sheet_name, worksheet_name=None)

Move data from a pandas DataFrame to Google Sheets.

Parameters:

Name	Type	Description	Default
`dataframe`	`DataFrame`	The pandas DataFrame to write.	required
`sheet_name`	`str`	The name of the Google Sheet document.	required
`worksheet_name`	`Optional[str]`	The name of the specific worksheet tab. If None, defaults to the first sheet.	`None`

Source code in src/cxas_scrapi/utils/google_sheets_utils.py

def dataframe_to_sheets(
    self,
    dataframe: pd.DataFrame,
    sheet_name: str,
    worksheet_name: Optional[str] = None,
):
    """Move data from a pandas DataFrame to Google Sheets.

    Args:
        dataframe: The pandas DataFrame to write.
        sheet_name: The name of the Google Sheet document.
        worksheet_name: The name of the specific worksheet tab. If None,
            defaults to the first sheet.
    """
    if not self.sheets_client:
        raise RuntimeError(
            "Sheets client is not authorized. See earlier "
            "initialization errors."
        )

    try:
        g_sheets = self.sheets_client.open(sheet_name)
        if worksheet_name:
            worksheet = g_sheets.worksheet(worksheet_name)
        else:
            worksheet = g_sheets.sheet1

        worksheet.clear()  # Clear existing data before writing
        set_with_dataframe(worksheet, dataframe)
    except Exception as e:
        self._handle_api_error(e)

append_dataframe_to_sheets ¶

append_dataframe_to_sheets(dataframe, sheet_name, worksheet_name=None)

Append data from a pandas DataFrame to an existing Google Sheet tab.

Parameters:

Name	Type	Description	Default
`dataframe`	`DataFrame`	The pandas DataFrame to append.	required
`sheet_name`	`str`	The name of the Google Sheet document.	required
`worksheet_name`	`Optional[str]`	The name of the specific worksheet tab. If None, defaults to the first sheet.	`None`

Source code in src/cxas_scrapi/utils/google_sheets_utils.py

def append_dataframe_to_sheets(
    self,
    dataframe: pd.DataFrame,
    sheet_name: str,
    worksheet_name: Optional[str] = None,
):
    """Append data from a pandas DataFrame to an existing Google Sheet tab.

    Args:
        dataframe: The pandas DataFrame to append.
        sheet_name: The name of the Google Sheet document.
        worksheet_name: The name of the specific worksheet tab. If None,
            defaults to the first sheet.
    """
    existing_df = self.sheets_to_dataframe(sheet_name, worksheet_name)

    if existing_df.empty:
        combined_df = dataframe
    else:
        combined_df = pd.concat([existing_df, dataframe], ignore_index=True)

    self.dataframe_to_sheets(combined_df, sheet_name, worksheet_name)