[Gsheets/shillelagh] Unable to add privately shared sheets

[zeriyu]

Hi, first time using superset, hopefully it's the right place.

I recieve "The URL could not be identified. Please check for typos and make sure that ‘Type of Google Sheets allowed’ selection matches the input." when trying to add a privately shared sheet.

How to reproduce the bug

1. Go to '/databaseview/list/'
2. Click on '+' > Data > Connect Google Sheet
3. Fill out data:

Display Name = Google Sheets Type of Google Sheets allowed = Public and privately shared sheets Upload service account JSON Fill Out Sheet name and paste link

5. See error

Expected results

The DB is added successfuly

Actual results

Unable to add the DB

Screenshots

Error:

Console:

Environment

• Firefox 103.0.2 and Chrome 104.0.5112.101
• Latest docker containter (using https://superset.apache.org/docs/installation/installing-superset-using-docker-compose) on Centos 9 Stream

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• [X] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [X] I have reproduced the issue with at least the latest released version of superset.
• [X] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

I have no issues on preset.io I tried on Centos 7 and 9 Stream

$ cat docker/requirements-local.txt psycopg2 shillelagh[gsheetsapi]

superset.log superset_app.log

Thanks!

[monkey01127]

Hello, the content of your log file is very good(superset.log, superset_app.log). It is divided according to the module. May I ask how to match it? Can you post the configuration code?

[stefanocascavilla]

Same issue here

[mittalsuraj18]

Facing the same issue here

[sonnysideup]

what's happening here? we're hitting the same issue

[JDiego199]

I had the same error and I solved it by changing the country to the United States in the Google Sheet settings

[RedHotUnicorn]

It look like superset could read gsheets only if data inside is in text format.

I took text data by sync with formula:

=ArrayFormula(QUERY(to_text(IMPORTRANGE(" %LINK TO GSHEET% ";" %PAGE% ! A:E "));"select *"))

[shauryagoel]

Has anyone tried following this link?

[aarfah-a]

Facing same issue for private sheets. Public sheets are working fine. Please help.

[rinatik66]

What helped me was switching to a dot for the decimal separator by changing the region to the USA. But clearly the problem relates to the superset bug.

Setting up decimal separator, thousand separator and date in Google Spreadsheets

[matheusbsilva]

Facing the same issue here, using https://github.com/betodealmeida/shillelagh with the service account credentials I can query private sheets, but when I try to setup the connection on Superset I get the error @zeriyu showed.

[nanaones]

same too