Spaces:
Sleeping
Sleeping
File size: 3,712 Bytes
81ecf5d 207d271 81ecf5d 207d271 81ecf5d 207d271 81ecf5d 207d271 81ecf5d db9f03c 81ecf5d db9f03c 81ecf5d 23282ad 207d271 81ecf5d 207d271 81ecf5d 23282ad | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 | 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")
|