Google sheets connection breaks in 4.1.1

[dcernag]

Bug description

When upgrading to 4.1.1 now public google sheet db connections can't be edited and get InvalidParametersError or table does not exist when querying with SQL lab

Validation goes through for the public sheet without issues if i create a new db connection but then i can't query it or edit the connection. Something weird is that even if its public and i select the option to add public sheet it asks for service account json and marks it as required

Already tried downgrading the shillelagh version as far back as i could and also added the OAUTH configuration that was added recently

Screenshots/recordings

No response

Superset version

4.1.1

Python version

3.10

Node version

I don't know

Browser

Chrome

Additional context

WARNING:superset.views.error_handling:SupersetErrorsException
 Traceback (most recent call last):
   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request
     rv = self.dispatch_request()
   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1469, in dispatch_request
     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
   File "/usr/local/lib/python3.10/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
     return f(self, *args, **kwargs)
   File "/app/superset/views/base_api.py", line 119, in wraps
     duration, response = time_function(f, self, *args, **kwargs)
   File "/app/superset/utils/core.py", line 1364, in time_function
     response = func(*args, **kwargs)
   File "/app/superset/utils/log.py", line 303, in wrapper
     value = f(*args, **kwargs)
   File "/app/superset/views/base_api.py", line 91, in wraps
     return f(self, *args, **kwargs)
   File "/app/superset/databases/api.py", line 2163, in validate_parameters
     command.run()
   File "/app/superset/commands/database/validate.py", line 72, in run
     raise InvalidParametersError(errors)
superset.commands.database.exceptions.InvalidParametersError: [SupersetError(message='The URL could not be identified. Please check for typos and make sure that 'Type of Google Sheets allowed' selection matches the input.', error_type=<SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR: 'TABLE_DOES_NOT_EXIST_ERROR'>, level=<ErrorLevel.WARNING: 'warning'>, extra={'catalog': {'idx': 0, 'url': True}, 'issue_codes': [{'code': 1003, 'message': 'Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.'}, {'code': 1005, 'message': 'Issue 1005 - The table was deleted or renamed in the database.'}]})] 

"GET /api/v1/database/7/table_metadata/extra/?name=Attrition&schema=main HTTP/1.1" 200 16 "https://superset.example.com/explore/?viz_type=table&datasource=79__table" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36"
"POST /superset/log/?explode=events HTTP/1.1" 200 9 "https://superset.example.com/explore/?form_data_key=WaVB3xp5ofR6q824VvXAid3pWiVbVIGuiwlLjfU_Fh6HU9a0MeOw4JsDjOb1LRad&viz_type=table&datasource=79__table&datasource_id=79&datasource_type=table" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36"
"POST /superset/log/?explode=events HTTP/1.1" 200 9 "https://superset.example.com/explore/?form_data_key=WaVB3xp5ofR6q824VvXAid3pWiVbVIGuiwlLjfU_Fh6HU9a0MeOw4JsDjOb1LRad&viz_type=table&datasource=79__table&datasource_id=79&datasource_type=table" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36"
[superset-775f6f54d6-k8vnw superset] 2024-11-28 19:30:47,527:WARNING:superset.connectors.sqla.models:Query SELECT COUNT(*) AS "COUNT(*)"
[superset-775f6f54d6-k8vnw superset] FROM main."Attrition"
[superset-775f6f54d6-k8vnw superset]  LIMIT 50000 OFFSET 0 on schema main failed
[superset-775f6f54d6-k8vnw superset] Traceback (most recent call last):
[superset-775f6f54d6-k8vnw superset]   File "/app/superset/connectors/sqla/models.py", line 1761, in query
[superset-775f6f54d6-k8vnw superset]     df = self.database.get_df(
[superset-775f6f54d6-k8vnw superset]   File "/app/superset/models/core.py", line 677, in get_df
[superset-775f6f54d6-k8vnw superset]     self.db_engine_spec.execute(cursor, sql_, self)
[superset-775f6f54d6-k8vnw superset]   File "/app/superset/db_engine_specs/base.py", line 1845, in execute
[superset-775f6f54d6-k8vnw superset]     raise cls.get_dbapi_mapped_exception(ex) from ex
[superset-775f6f54d6-k8vnw superset]   File "/app/superset/db_engine_specs/base.py", line 1841, in execute
[superset-775f6f54d6-k8vnw superset]     cursor.execute(query)
[superset-775f6f54d6-k8vnw superset]   File "/usr/local/lib/python3.10/site-packages/shillelagh/backends/apsw/db.py", line 101, in wrapper
[superset-775f6f54d6-k8vnw superset]     return method(self, *args, **kwargs)
[superset-775f6f54d6-k8vnw superset]   File "/usr/local/lib/python3.10/site-packages/shillelagh/backends/apsw/db.py", line 254, in execute
[superset-775f6f54d6-k8vnw superset]     raise ProgrammingError(message) from ex
[superset-775f6f54d6-k8vnw superset] shillelagh.exceptions.ProgrammingError: no such table: main.Attrition
[superset-775f6f54d6-k8vnw superset] 34.128.171.139 - - [28/Nov/2024:19:30:47 +0000] "POST /datasource/samples?force=false&datasource_type=table&datasource_id=79 HTTP/1.1" 422 42 "https://superset.example.com/explore/?form_data_key=WaVB3xp5ofR6q824VvXAid3pWiVbVIGuiwlLjfU_Fh6HU9a0MeOw4JsDjOb1LRad&viz_type=table&datasource=79__table&datasource_id=79&datasource_type=table" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36"

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hey @dcernag, I'm here to help you with the issue you're facing. I can assist with bug fixes, answer your questions, and guide you on becoming a contributor. Let's tackle this together while we wait for a human maintainer.

In Superset version 4.1.1, there were changes to the Google Sheets database connection handling that might be affecting your setup. Here are some key points that could be relevant to the issues you're experiencing:

1. Validation of Parameters: The system now validates parameters more strictly, including checking for the presence of a catalog and service_account_info. If these are not provided, errors are returned ^[1].

2. Service Account JSON Requirement: For public Google Sheets, a service account JSON should not be required. However, if the system is incorrectly marking it as required, it might be due to a misconfiguration or a bug in the UI logic. The isPublic state should be set to true for public sheets, which means no service account JSON is needed ^[2].

3. Error Handling: The InvalidParametersError and TABLE_DOES_NOT_EXIST_ERROR you are encountering could be due to URL identification issues or mismatches in the expected type of Google Sheets URL. Ensure that the URL is correct and matches the type of Google Sheets allowed ^[3].

4. Session Management and Data Upload: The handling of POST requests and data upload processes have been updated, which might affect how connections are managed and validated ^[1].

To address these issues, you might want to double-check the configuration settings for your Google Sheets connection, especially the type of sheets allowed and the service account settings. If the problem persists, it could be beneficial to review the changes in the codebase related to Google Sheets handling in version 4.1.1 for any potential bugs or misconfigurations.

To continue talking to Dosu, mention @dosu.

[dcernag]

Yeah i can confirm that the url is valid and open to the public, also when i create a new db connection there is a catalog present. Service account textbox is not shown when adding a new connection but it shows during editing even if public is selected.

@dosu