"""Inspect Sheets read requests offline; compare explicitly fictional cell values. Install: python3 -m pip install google-api-python-client==2.201.0 Run: python3 -B value_render_request_inventory.py No request is executed. Bundled discovery and the real SDK construct the URIs. Prepared replies below are examples, never responses obtained from Google. """ from decimal import Decimal import hashlib from importlib.metadata import version import json import math import socket from unittest.mock import patch from urllib.parse import parse_qs, unquote, urlsplit SDK_VERSION = "2.201.0" DISCOVERY_SHA256 = "8b0f38455eba2a0fb9e731b499dcfd59e0f5fe7fa1ef16423d2e4c049f89e45d" SPREADSHEET = "fictional-orders-spreadsheet" RANGE = "'Commandes ERP'!A2:D4" checked = [] attempts = {"socket_connections": 0, "http_requests": 0} def check(label, condition): if not condition: raise AssertionError(label) checked.append(label) def deny_connection(*args, **kwargs): attempts["socket_connections"] += 1 raise RuntimeError("This exercise must not open a connection") class OfflineHttp: def request(self, *args, **kwargs): attempts["http_requests"] += 1 raise RuntimeError("Construct the request; do not execute it") def amount_from_number(value): """Local numeric-column rule, not a Google or ERP schema validator.""" if type(value) not in (int, float): raise TypeError("A numeric amount was expected, not presentation text") if not math.isfinite(value): raise ValueError("The amount must be finite") return Decimal(str(value)) def expect_error(label, error_type, operation): try: operation() except error_type: check(label, True) else: raise AssertionError(label + ": expected error was not raised") def document_read_contract(query): """Apply documented defaults locally; this does not emulate Google's engine.""" mode = query.get("valueRenderOption", ["FORMATTED_VALUE"])[0] date_mode = query.get("dateTimeRenderOption", ["SERIAL_NUMBER"])[0] return { "value_mode_from_request_or_documented_default": mode, "date_mode_from_request_or_documented_default": date_mode, "date_option_ignored_according_to_documentation": mode == "FORMATTED_VALUE", "source": "Google values.get reference, consulted 2026-10-16", "server_behavior_executed": False, } def inspect_requests(): with patch.object(socket.socket, "connect", deny_connection), \ patch.object(socket.socket, "connect_ex", deny_connection), \ patch.object(socket, "create_connection", deny_connection): from googleapiclient.discovery import build_from_document from googleapiclient.discovery_cache import get_static_doc check("pinned official SDK version", version("google-api-python-client") == SDK_VERSION) discovery = get_static_doc("sheets", "v4") check("bundled discovery bytes", hashlib.sha256(discovery.encode()).hexdigest() == DISCOVERY_SHA256) schema = json.loads(discovery) check("bundled discovery revision", schema["revision"] == "20260921") service = build_from_document(discovery, http=OfflineHttp()) profiles = [ ("date_option_only", {"dateTimeRenderOption": "SERIAL_NUMBER"}), ("formatted_explicit", {"valueRenderOption": "FORMATTED_VALUE", "dateTimeRenderOption": "SERIAL_NUMBER"}), ("numeric_amount_formatted_date", {"valueRenderOption": "UNFORMATTED_VALUE", "dateTimeRenderOption": "FORMATTED_STRING"}), ("numeric_amount_serial_date", {"valueRenderOption": "UNFORMATTED_VALUE", "dateTimeRenderOption": "SERIAL_NUMBER"}), ("formula_read", {"valueRenderOption": "FORMULA"}), ("column_oriented", {"valueRenderOption": "UNFORMATTED_VALUE", "dateTimeRenderOption": "SERIAL_NUMBER", "majorDimension": "COLUMNS"}), ] observations = [] for name, supplied in profiles: params = {"majorDimension": "ROWS", **supplied} request = service.spreadsheets().values().get( spreadsheetId=SPREADSHEET, range=RANGE, **params ) uri = urlsplit(request.uri) query = parse_qs(uri.query) check(name + ": GET without body", request.method == "GET" and request.body is None) check(name + ": endpoint", uri.scheme == "https" and uri.netloc == "sheets.googleapis.com") check(name + ": decoded range", unquote(uri.path) == "/v4/spreadsheets/" + SPREADSHEET + "/values/" + RANGE) check(name + ": supplied parameters retained", all(query.get(k) == [v] for k, v in params.items())) check(name + ": no write option or credential", not {"valueInputOption", "key", "access_token"}.intersection(query)) observations.append({"profile": name, "actual_sdk_uri": request.uri, "documented_contract": document_read_contract(query)}) first = parse_qs(urlsplit(observations[0]["actual_sdk_uri"]).query) check("SDK does not insert the default value render option", "valueRenderOption" not in first) check("date-only mode is ignored by the documented default", observations[0]["documented_contract"]["date_option_ignored_according_to_documentation"]) check("explicit formatted mode also ignores the date option by contract", observations[1]["documented_contract"]["date_option_ignored_according_to_documentation"]) check("unformatted mode differs from the formatted default", not observations[2]["documented_contract"]["date_option_ignored_according_to_documentation"]) formula_query = parse_qs(urlsplit(observations[4]["actual_sdk_uri"]).query) check("SDK does not insert the default date mode", "dateTimeRenderOption" not in formula_query) for label, invalid in [ ("write parameter is not a read parameter", {"valueInputOption": "RAW"}), ("RAW is not a value render mode", {"valueRenderOption": "RAW"}), ("FORMATTED_STRING is not a value render mode", {"valueRenderOption": "FORMATTED_STRING"}), ("locale is not a values.get query parameter", {"locale": "fr_FR"}), ]: expect_error(label, TypeError, lambda invalid=invalid: service.spreadsheets().values().get(spreadsheetId=SPREADSHEET, range=RANGE, **invalid)) check("no HTTP request during construction", attempts["http_requests"] == 0) check("no socket connection during guarded imports and construction", attempts["socket_connections"] == 0) return observations def inspect_prepared_values(): # Explicit examples prepared by the author, not an API or format simulator. display = "1\u202f250,50 €" formatted_row = ["00125", "14/10/2026", display, "EUR"] numeric_date_text_row = ["00125", "14/10/2026", 1250.5, "EUR"] numeric_date_serial_row = ["00125", 46309, 1250.5, "EUR"] expect_error("float rejects the prepared French currency string", ValueError, lambda: float(display)) wrong_digits_amount = Decimal("".join(c for c in display if c.isdecimal())) expected_amount = amount_from_number(numeric_date_text_row[2]) check("expected prepared amount", expected_amount == Decimal("1250.50")) check("digits-only counterexample is a hundredfold error", wrong_digits_amount == expected_amount * 100) check("date text and numeric amount have different types", type(numeric_date_text_row[1]) is str and type(numeric_date_text_row[2]) is float) check("serial date and amount are both numeric", type(numeric_date_serial_row[1]) is int and type(numeric_date_serial_row[2]) is float) check("identifier remains the original string", numeric_date_text_row[0] == "00125") check("global numeric casting loses the leading zeroes", str(int(numeric_date_text_row[0])) == "125") check("currency is supplied separately in the local column contract", numeric_date_text_row[3] == "EUR") check("zero is a valid local numeric amount", amount_from_number(0) == Decimal("0")) check("negative numeric values are not silently made positive", amount_from_number(-12.5) == Decimal("-12.5")) for value in [display, "1250.50", "1250,50", True, None, "=SUM(C2:C3)"]: expect_error("numeric-column rule rejects " + repr(value), TypeError, lambda value=value: amount_from_number(value)) for value in [float("nan"), float("inf")]: expect_error("numeric-column rule rejects non-finite " + str(value), ValueError, lambda value=value: amount_from_number(value)) numeric_cell = {"userEnteredValue": {"numberValue": 1250.5}, "effectiveValue": {"numberValue": 1250.5}, "formattedValue": display} text_cell = {"userEnteredValue": {"stringValue": display}, "effectiveValue": {"stringValue": display}, "formattedValue": display} check("the two prepared cells display the same text", numeric_cell["formattedValue"] == text_cell["formattedValue"]) check("their effective value types differ", "numberValue" in numeric_cell["effectiveValue"] and "stringValue" in text_cell["effectiveValue"]) check("literal text example remains text", text_cell["effectiveValue"] == text_cell["userEnteredValue"]) expect_error("a text cell is not fixed by our numeric-column reader", TypeError, lambda: amount_from_number(text_cell["effectiveValue"]["stringValue"])) formula_cell = {"userEnteredValue": {"formulaValue": "=SUM(C2:C3)"}, "effectiveValue": {"numberValue": 1250.5}} check("prepared formula and effective result are distinct", formula_cell["userEnteredValue"] != formula_cell["effectiveValue"]) check("formula text is not calculated by the exercise", formula_cell["userEnteredValue"]["formulaValue"] == "=SUM(C2:C3)") ragged_example = {"range": "'Commandes ERP'!A2:D4", "majorDimension": "ROWS", "values": [formatted_row, ["00126"]]} check("a requested range is not the observed row count", len(ragged_example["values"]) == 2) check("the prepared final row omits trailing cells", len(ragged_example["values"][1]) == 1) check("a missing amount is not an observed zero", len(ragged_example["values"][1]) <= 2) return {"formatted_row": formatted_row, "numeric_amount_formatted_date_row": numeric_date_text_row, "numeric_amount_serial_date_row": numeric_date_serial_row, "expected_amount": str(expected_amount), "wrong_digits_only_amount": str(wrong_digits_amount), "same_display_different_types": [numeric_cell, text_cell], "formula_cell": formula_cell, "trailing_empty_cells_example": ragged_example} def main(): requests = inspect_requests() prepared = inspect_prepared_values() result = {"checks_passed": len(checked), "actual_sdk_version": SDK_VERSION, "bundled_discovery_revision": "20260921", "requests_constructed_not_executed": requests, "prepared_examples_not_google_responses": prepared, "guard_observations": attempts, "scope": {"actual_sdk_request_serialization": True, "native_api_calls": 0, "spreadsheet_or_erp_writes": 0, "persistent_writes_by_exercise": 0, "google_formatting_or_formula_engine_tested": False, "native_account_or_business_mapping_certified": False}, "checks": checked} print(json.dumps(result, ensure_ascii=False, indent=2, allow_nan=False)) if __name__ == "__main__": main()