document-parser / google_sheets_service.py
segovia
log information
207d271
Raw History Blame Contribute Delete
3.71 kB
from __future__ import annotations
import json
import math
import os
from typing import Any
import gspread
from dotenv import load_dotenv
from gspread.exceptions import WorksheetNotFound
from google.oauth2.service_account import Credentials
SCOPES = [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive",
]
load_dotenv()
class GoogleSheetsConfigError(RuntimeError):
"""Raised when required Google Sheets environment configuration is missing or invalid."""
def _build_credentials() -> Credentials:
service_account_json = (os.getenv("GOOGLE_SERVICE_ACCOUNT_JSON") or "").strip()
service_account_file = (os.getenv("GOOGLE_SERVICE_ACCOUNT_FILE") or "").strip()
if service_account_json:
try:
info = json.loads(service_account_json)
except json.JSONDecodeError as exc:
raise GoogleSheetsConfigError("GOOGLE_SERVICE_ACCOUNT_JSON is not valid JSON.") from exc
return Credentials.from_service_account_info(info, scopes=SCOPES)
if service_account_file:
return Credentials.from_service_account_file(service_account_file, scopes=SCOPES)
raise GoogleSheetsConfigError(
"Missing Google credentials. Set GOOGLE_SERVICE_ACCOUNT_JSON or GOOGLE_SERVICE_ACCOUNT_FILE."
)
def _open_worksheet(client: gspread.Client, worksheet_name: str | None = None, column_count: int = 0):
spreadsheet_id = (os.getenv("GOOGLE_SHEETS_SPREADSHEET_ID") or "").strip()
resolved_worksheet_name = (worksheet_name or os.getenv("GOOGLE_SHEETS_WORKSHEET") or "Sheet1").strip()
if not spreadsheet_id:
raise GoogleSheetsConfigError("Missing GOOGLE_SHEETS_SPREADSHEET_ID.")
spreadsheet = client.open_by_key(spreadsheet_id)
try:
return spreadsheet.worksheet(resolved_worksheet_name)
except WorksheetNotFound:
return spreadsheet.add_worksheet(
title=resolved_worksheet_name,
rows=1000,
cols=max(column_count, 1),
)
def _normalize_cell_value(value: Any) -> Any:
if value is None:
return ""
# Pandas uses a dedicated NA sentinel that is not JSON serializable.
if type(value).__name__ == "NAType":
return ""
# Convert numpy scalar wrappers into plain Python values when possible.
if hasattr(value, "item"):
try:
value = value.item()
except Exception:
pass
if isinstance(value, float) and not math.isfinite(value):
return ""
# Fallback for NaN-like values that are not plain floats.
try:
if value != value:
return ""
except Exception:
pass
return value
def _column_index_to_letter(index: int) -> str:
if index < 1:
raise ValueError("Column index must be greater than zero.")
letters = []
while index > 0:
index, remainder = divmod(index - 1, 26)
letters.append(chr(ord("A") + remainder))
return "".join(reversed(letters))
def append_row_to_google_sheet(
row: dict[str, Any],
ordered_columns: list[str],
worksheet_name: str | None = None,
) -> None:
credentials = _build_credentials()
client = gspread.authorize(credentials)
worksheet = _open_worksheet(client, worksheet_name=worksheet_name, column_count=len(ordered_columns))
values = [_normalize_cell_value(row.get(column, "")) for column in ordered_columns]
# Insert a new row at the end of the table anchored at column A.
end_column = _column_index_to_letter(len(values))
table_values = worksheet.get(f"A:{end_column}")
next_row = len(table_values) + 1
worksheet.insert_row(values, index=next_row, value_input_option="USER_ENTERED")