This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/superset.git
The following commit(s) were added to refs/heads/master by this push:
new 855bb4e21c8 fix(gsheets): align service-account validation and
serialize upload dates (#44695)
855bb4e21c8 is described below
commit 855bb4e21c8492775eb8bdd0ac7fe165c2dca513
Author: Amin Ghadersohi <[email protected]>
AuthorDate: Thu Oct 1 12:50:52 2026 +1000
fix(gsheets): align service-account validation and serialize upload dates
(#44695)
---
docs/admin_docs/configuration/google-sheets.mdx | 49 +++++
superset/db_engine_specs/gsheets.py | 81 +++++--
tests/unit_tests/db_engine_specs/test_gsheets.py | 259 ++++++++++++++++++++++-
3 files changed, 374 insertions(+), 15 deletions(-)
diff --git a/docs/admin_docs/configuration/google-sheets.mdx
b/docs/admin_docs/configuration/google-sheets.mdx
new file mode 100644
index 00000000000..0f888f9e314
--- /dev/null
+++ b/docs/admin_docs/configuration/google-sheets.mdx
@@ -0,0 +1,49 @@
+---
+title: Google Sheets credentials
+---
+
+{/*
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+*/}
+
+## Service-account connections
+
+When configuring Google Sheets with service-account JSON in the database
dialog,
+share private spreadsheets with the service account's `client_email`.
+Both catalog validation and queries use the service account, not the Google
+identity of the administrator adding the spreadsheet. This applies when
creating
+a connection and when editing its catalog.
+
+The dialog's stored `impersonate_user` flag does not enable domain-wide
delegation
+for this service-account credential path. A spreadsheet shared only with the
+administrator cannot be validated on that basis, even if the service account
has
+domain-wide delegation enabled. Creating an empty connection and editing it
later
+does not change the identity used to access the spreadsheet.
+
+## Upload values
+
+Date and datetime upload values are sent as ISO strings rather than numeric
+nanosecond counts. Timezone-aware datetimes are converted to UTC, then uploaded
+without a timezone suffix so Google Sheets can parse them as datetime cells
+with `USER_ENTERED` input. For example, `2024-03-01 00:30:00+02:00` is
uploaded as
+`2024-02-29 22:30:00`. The original timezone is not retained; interpret these
cell
+values as UTC. Naive datetimes keep their original clock time.
+
+Nullable integer columns retain integer values in the upload payload, and
missing
+values become empty cells. Time-only values use `HH:MM:SS[.ffffff]` strings.
+Durations are sent as signed `H:MM:SS[.ffffff]` strings, with hours including
days.
diff --git a/superset/db_engine_specs/gsheets.py
b/superset/db_engine_specs/gsheets.py
index b1edb0cceb0..2eb210b46ce 100644
--- a/superset/db_engine_specs/gsheets.py
+++ b/superset/db_engine_specs/gsheets.py
@@ -19,10 +19,12 @@ from __future__ import annotations
import logging
import re
-from datetime import datetime
+from datetime import date, datetime, time, timedelta, UTC
+from decimal import Decimal
from re import Pattern
from typing import Any, TYPE_CHECKING, TypedDict
+import numpy as np
import pandas as pd
from apispec import APISpec
from apispec.ext.marshmallow import MarshmallowPlugin
@@ -64,6 +66,56 @@ SYNTAX_ERROR_REGEX = re.compile('SQLError: near
"(?P<server_error>.*?)": syntax
ma_plugin = MarshmallowPlugin()
+def _to_python_value(value: Any) -> Any:
+ """
+ Convert numpy and pandas scalars into their Python equivalents.
+ """
+ if isinstance(value, np.datetime64):
+ # ``.item()`` returns an int for nanosecond precision; go through
pandas.
+ value = pd.Timestamp(value)
+ elif isinstance(value, np.timedelta64):
+ # Durations also return an int from ``.item()`` at nanosecond
precision.
+ value = pd.Timedelta(value)
+ elif isinstance(value, np.generic):
+ value = value.item()
+ if value is pd.NaT:
+ return None
+ if isinstance(value, pd.Timestamp):
+ return value.to_pydatetime()
+ if isinstance(value, pd.Timedelta):
+ return value.to_pytimedelta()
+ return value
+
+
+def to_json_value(value: Any) -> Any:
+ """
+ Convert a dataframe cell into a JSON value the Sheets API parses back.
+
+ Dates and timestamps become ISO strings (``USER_ENTERED`` input parses them
+ as dates). Aware timestamps are normalized to UTC with the offset removed;
+ naive timestamps retain their clock time. Durations become signed
+ ``H:MM:SS[.ffffff]`` strings with total hours (including days), and numpy
+ scalars become Python scalars.
+ """
+ value = _to_python_value(value)
+ if isinstance(value, datetime):
+ if value.utcoffset() is not None:
+ value = value.astimezone(UTC).replace(tzinfo=None)
+ return value.isoformat(sep=" ")
+ if isinstance(value, (date, time)):
+ return value.isoformat()
+ if isinstance(value, timedelta):
+ sign = "-" if value < timedelta(0) else ""
+ value = abs(value)
+ hours = value.days * 24 + value.seconds // 3600
+ minutes, seconds = divmod(value.seconds % 3600, 60)
+ fraction = f".{value.microseconds:06d}" if value.microseconds else ""
+ return f"{sign}{hours}:{minutes:02d}:{seconds:02d}{fraction}"
+ if isinstance(value, Decimal):
+ return str(value)
+ return value
+
+
class GSheetsParametersSchema(Schema):
catalog = fields.Dict()
service_account_info = EncryptedString(
@@ -93,7 +145,7 @@ DELEGATION_KEY = "domain_wide_delegation"
class GSheetsParametersType(TypedDict, total=False):
- service_account_info: str
+ service_account_info: str | dict[str, Any]
catalog: dict[str, str] | None
oauth2_client_info: dict[str, str] | None
@@ -102,6 +154,7 @@ class GSheetsPropertiesType(TypedDict, total=False):
parameters: GSheetsParametersType
catalog: dict[str, str]
masked_encrypted_extra: str
+ impersonate_user: bool
class GSheetsEngineSpec(ShillelaghEngineSpec):
@@ -461,19 +514,18 @@ class GSheetsEngineSpec(ShillelaghEngineSpec):
)
return errors
- # We need a subject in case domain wide delegation is set, otherwise
the
- # check will fail. This means that the admin will be able to add sheets
- # that only they have access, even if later users are not able to
access
- # them.
- subject = g.user.email if g.user else None
-
+ # Service-account queries use connect_args.adapter_kwargs, which
replaces
+ # the dialect's URL-derived adapter_kwargs, so they run as the service
+ # account unless the secure extra opts into domain-wide delegation.
+ # Validate as the same service account even when the modal has stored
+ # impersonate_user=True; a delegated subject breaks non-DWD
credentials.
engine = create_engine(
"gsheets://",
connect_args={
"adapter_kwargs": {
"gsheetsapi": {
"service_account_info": encrypted_credentials,
- "subject": subject,
+ "subject": None,
}
}
},
@@ -641,12 +693,17 @@ class GSheetsEngineSpec(ShillelaghEngineSpec):
spreadsheet_url = payload["spreadsheetUrl"]
# insert data
- data = df.fillna("").values.tolist()
- data.insert(0, df.columns.values.tolist())
+ normalized_df = df.astype(object).where(df.notna(), None)
+ # Convert cells outside pandas to avoid inferring floats for nullable
ints.
+ values = [
+ [to_json_value(value) if value is not None else "" for value in
row]
+ for row in normalized_df.itertuples(index=False, name=None)
+ ]
+ values.insert(0, df.columns.values.tolist())
body = {
"range": range_,
"majorDimension": "ROWS",
- "values": data,
+ "values": values,
}
url = (
"https://sheets.googleapis.com/v4/spreadsheets/";
diff --git a/tests/unit_tests/db_engine_specs/test_gsheets.py
b/tests/unit_tests/db_engine_specs/test_gsheets.py
index 52f724c1e33..abc220c3294 100644
--- a/tests/unit_tests/db_engine_specs/test_gsheets.py
+++ b/tests/unit_tests/db_engine_specs/test_gsheets.py
@@ -23,11 +23,12 @@
from __future__ import annotations
-from datetime import datetime
+from datetime import datetime, time, timedelta, timezone, UTC
from typing import Any, TYPE_CHECKING
from unittest.mock import MagicMock
from urllib.parse import parse_qs, urlparse
+import numpy as np
import pandas as pd
import pytest
import sqlalchemy
@@ -200,6 +201,7 @@ def test_validate_parameters_catalog(
]
properties: GSheetsPropertiesType = {
+ "impersonate_user": True,
"parameters": {"service_account_info": "", "catalog": None},
"catalog": {
"private_sheet": "https://docs.google.com/spreadsheets/d/1/edit";,
@@ -268,7 +270,7 @@ def test_validate_parameters_catalog(
"adapter_kwargs": {
"gsheetsapi": {
"service_account_info": {},
- "subject": "[email protected]",
+ "subject": None,
}
}
},
@@ -342,7 +344,7 @@ def test_validate_parameters_catalog_and_credentials(
"adapter_kwargs": {
"gsheetsapi": {
"service_account_info": {},
- "subject": "[email protected]",
+ "subject": None,
}
}
},
@@ -1274,6 +1276,257 @@ def test_convert_dttm(
assert_convert_dttm(GSheetsEngineSpec, target_type, expected_result, dttm)
+def test_upload_dates(mocker: MockerFixture) -> None:
+ """
+ Test that date and numpy values are uploaded as JSON values.
+ """
+ from datetime import date
+ from decimal import Decimal
+
+ import numpy as np
+
+ from superset.db_engine_specs.gsheets import GSheetsEngineSpec
+
+ mocker.patch("superset.db_engine_specs.gsheets.db")
+ get_adapter_for_table_name = mocker.patch(
+ "shillelagh.backends.apsw.dialects.base.get_adapter_for_table_name"
+ )
+ session = get_adapter_for_table_name()._get_session()
+ session.post().json.return_value = {
+ "spreadsheetId": 1,
+ "spreadsheetUrl": "https://docs.example.org";,
+ "sheets": [{"properties": {"title": "sample_data"}}],
+ }
+
+ database = mocker.MagicMock()
+ database.get_extra.return_value = {}
+
+ df = pd.DataFrame(
+ {
+ "i": np.array([1, 2], dtype="int64"),
+ "f": [1.5, np.nan],
+ "d": [date(2024, 2, 29), None],
+ "ts": pd.to_datetime(["2024-02-29 23:59:58", None]),
+ "dec": [Decimal("1.10"), None],
+ }
+ )
+ GSheetsEngineSpec.df_to_sql(database, Table("sample_data"), df, {})
+
+ body = session.post.call_args_list[-1].kwargs["json"]
+ assert json.loads(json.dumps(body["values"])) == [
+ ["i", "f", "d", "ts", "dec"],
+ [1, 1.5, "2024-02-29", "2024-02-29 23:59:58", "1.10"],
+ [2, "", "", "", ""],
+ ]
+
+
[email protected](
+ "column, expected",
+ [
+ (
+ pd.Series(pd.to_datetime(["2024-03-01 00:30:00.123456+02:00",
None])),
+ ["2024-02-29 22:30:00.123456", ""],
+ ),
+ (
+ pd.Series(pd.to_datetime(["2024-02-29 23:30:00-05:00", None])),
+ ["2024-03-01 04:30:00", ""],
+ ),
+ (
+ pd.Series([1, None, 9007199254740993], dtype="Int64"),
+ [1, "", 9007199254740993],
+ ),
+ (
+ pd.Series([time(12, 34, 56, 123456), None, time(0, 0)]),
+ ["12:34:56.123456", "", "00:00:00"],
+ ),
+ (
+ pd.Series(pd.to_timedelta([5, 90_000_000_000, None], unit="ns")),
+ ["0:00:00", "0:01:30", ""],
+ ),
+ ],
+ ids=["positive-offset", "negative-offset", "nullable-int", "time",
"ns-duration"],
+)
+def test_upload_cell_types(
+ mocker: MockerFixture,
+ column: pd.Series,
+ expected: list[str | int],
+) -> None:
+ """Serialize cells without offsets or pandas' integer-to-float
inference."""
+ from superset.db_engine_specs.gsheets import GSheetsEngineSpec
+
+ mocker.patch("superset.db_engine_specs.gsheets.db")
+ get_adapter = mocker.patch(
+ "shillelagh.backends.apsw.dialects.base.get_adapter_for_table_name"
+ )
+ session = get_adapter.return_value._get_session.return_value
+ session.post.return_value.json.return_value = {
+ "spreadsheetId": 1,
+ "spreadsheetUrl": "https://docs.example.org";,
+ "sheets": [{"properties": {"title": "sample_data"}}],
+ }
+ database = mocker.MagicMock()
+ database.get_extra.return_value = {}
+ df = pd.DataFrame({"value": column})
+ original = df.copy(deep=True)
+
+ GSheetsEngineSpec.df_to_sql(database, Table("sample_data"), df, {})
+
+ request = session.post.call_args.kwargs
+ assert request["params"] == {"valueInputOption": "USER_ENTERED"}
+ values = json.loads(json.dumps(request["json"]["values"]))
+ assert values == [["value"], *[[value] for value in expected]]
+ # Numeric equality alone would accept 1.0 in place of 1.
+ assert [type(row[0]) for row in values[1:]] == [type(value) for value in
expected]
+ pd.testing.assert_frame_equal(df, original)
+
+
[email protected](
+ "value, expected",
+ [
+ (datetime(2024, 3, 1, 0, 30, tzinfo=UTC), "2024-03-01 00:30:00"),
+ (
+ datetime(2024, 3, 1, 0, 30, tzinfo=timezone(timedelta(hours=2))),
+ "2024-02-29 22:30:00",
+ ),
+ (pd.Timestamp("2024-03-01 00:30:00", tz="Asia/Kolkata"), "2024-02-29
19:00:00"),
+ (datetime(2024, 3, 1, 0, 30), "2024-03-01 00:30:00"),
+ ],
+)
+def test_to_json_value_datetime_utc(value: datetime, expected: str) -> None:
+ """Aware timestamps become naive UTC; naive timestamps retain their clock
time."""
+ from superset.db_engine_specs.gsheets import to_json_value
+
+ assert to_json_value(value) == expected
+
+
[email protected](
+ "value, expected",
+ [
+ (np.datetime64("2024-02-29T23:59:58", "us"), "2024-02-29 23:59:58"),
+ (np.datetime64("2024-02-29T23:59:58", "ns"), "2024-02-29 23:59:58"),
+ (np.datetime64("2024-02-29", "D"), "2024-02-29 00:00:00"),
+ (np.datetime64("NaT"), None),
+ (np.timedelta64(90, "s"), "0:01:30"),
+ (np.timedelta64(90_000_000_000, "ns"), "0:01:30"),
+ (np.timedelta64(5, "ns"), "0:00:00"),
+ (np.timedelta64("NaT", "ns"), None),
+ (pd.Timedelta(seconds=90), "0:01:30"),
+ (timedelta(hours=1), "1:00:00"),
+ (timedelta(days=2, seconds=61), "48:01:01"),
+ (timedelta(seconds=-90), "-0:01:30"),
+ (timedelta(days=-2), "-48:00:00"),
+ (timedelta(microseconds=1), "0:00:00.000001"),
+ (timedelta(microseconds=-1), "-0:00:00.000001"),
+ (pd.Timedelta(days=2, microseconds=123456), "48:00:00.123456"),
+ (np.int64(3), 3),
+ (time(12, 34, 56, 123456), "12:34:56.123456"),
+ ],
+)
+def test_to_json_value_numpy_dates_and_durations(value: Any, expected: Any) ->
None:
+ """
+ Test that numpy dates and durations become JSON-serializable values.
+ """
+ from superset.db_engine_specs.gsheets import to_json_value
+
+ result = to_json_value(value)
+ assert result == expected
+ json.dumps(result)
+
+
[email protected]("impersonate_user", [None, False, True])
[email protected](
+ "serialized_credentials", [False, True], ids=["edit", "create"]
+)
[email protected]("catalog_in_parameters", [False, True])
+def test_validate_parameters_service_account_subject(
+ mocker: MockerFixture,
+ impersonate_user: bool | None,
+ serialized_credentials: bool,
+ catalog_in_parameters: bool,
+) -> None:
+ """Create and edit validate as the service account, even with the modal
flag."""
+ from superset.db_engine_specs.gsheets import (
+ GSheetsEngineSpec,
+ GSheetsPropertiesType,
+ )
+
+ g = mocker.patch("superset.db_engine_specs.gsheets.g")
+ g.user.email = "[email protected]"
+ create_engine =
mocker.patch("superset.db_engine_specs.gsheets.create_engine")
+ mocker.patch.object(GSheetsEngineSpec, "register_engine_events")
+ credentials = {"client_email": "[email protected]", "private_key": "KEY"}
+ sheet_url = "https://docs.google.com/spreadsheets/d/1/edit";
+ properties: GSheetsPropertiesType = {
+ "parameters": {
+ "service_account_info": (
+ json.dumps(credentials) if serialized_credentials else
credentials
+ ),
+ },
+ }
+ if catalog_in_parameters:
+ properties["parameters"]["catalog"] = {"sheet": sheet_url}
+ else:
+ properties["catalog"] = {"sheet": sheet_url}
+ if impersonate_user is not None:
+ properties["impersonate_user"] = impersonate_user
+
+ assert GSheetsEngineSpec.validate_parameters(properties) == []
+
+ create_engine.assert_called_once_with(
+ "gsheets://",
+ connect_args={
+ "adapter_kwargs": {
+ "gsheetsapi": {"service_account_info": credentials, "subject":
None},
+ },
+ },
+ )
+ conn = create_engine.return_value.connect.return_value
+ assert str(conn.execute.call_args.args[0]) == (
+ 'SELECT * FROM "https://docs.google.com/spreadsheets/d/1/edit"; LIMIT 1'
+ )
+ conn.execute.return_value.fetchall.assert_called_once()
+
+
[email protected]("impersonate_user", [False, True])
+def test_query_service_account_subject(
+ mocker: MockerFixture,
+ impersonate_user: bool,
+) -> None:
+ """Exercise SQLAlchemy's final DBAPI arguments, not just the URL
subject."""
+ from superset.models.core import Database
+
+ user = mocker.MagicMock(email="[email protected]")
+ mocker.patch("superset.models.core.get_username", return_value="admin")
+ mocker.patch(
+ "superset.extensions.security_manager.find_user",
+ return_value=user,
+ )
+ credentials = {"client_email": "[email protected]", "private_key": "KEY"}
+ catalog = {"sheet": "https://docs.google.com/spreadsheets/d/1/edit"}
+ database = Database(
+ database_name="sheets",
+ sqlalchemy_uri="gsheets://",
+ impersonate_user=impersonate_user,
+ encrypted_extra=json.dumps({"service_account_info": credentials}),
+ extra=json.dumps({"engine_params": {"catalog": catalog}}),
+ )
+ engine = database._get_sqla_engine()
+ connect = mocker.spy(engine.dialect.dbapi, "connect")
+ try:
+ # No network or Google credentials are needed for a literal query.
+ with engine.connect() as conn:
+ assert conn.exec_driver_sql("SELECT 1").scalar() == 1
+ adapter_kwargs =
connect.call_args.kwargs["adapter_kwargs"]["gsheetsapi"]
+ assert adapter_kwargs["service_account_info"] == credentials
+ assert adapter_kwargs["catalog"] == catalog
+ assert adapter_kwargs.get("subject") is None
+ # Without the delegation opt-in the subject is removed from the URL
too,
+ # since connect_args replaces the URL-derived adapter_kwargs.
+ assert "subject" not in engine.url.query
+ finally:
+ engine.dispose()
+
+
def _service_account_adapter_kwargs(
mocker: MockerFixture,
encrypted_extra: dict[str, Any],
