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")