You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2022/08/22 15:11:17 UTC
[GitHub] [superset] zeriyu opened a new issue, #21152: [Gsheets/shillelagh] Unable to add privately shared sheets
zeriyu opened a new issue, #21152:
URL: https://github.com/apache/superset/issues/21152
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](https://github.com/apache/superset/files/9395499/superset.log)
Thanks!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "min-awm (via GitHub)" <gi...@apache.org>.
min-awm commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-3139376591
You must use a Google Workspace account and configure Domain-Wide Delegation if you use subject. For regular (personal) Google accounts, just delete the subject in file superset/db_engine_specs/gsheets.py
<img width="1227" height="476" alt="Image" src="https://github.com/user-attachments/assets/da2f3a69-15d8-4011-ab9b-399a2316f34a" />
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "iliakasianov (via GitHub)" <gi...@apache.org>.
iliakasianov commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-2486203095
Solution which works for me.
1. Create Database -> Shillelagh
2. SQLAlchemy URI = `gsheets://?service_account_file=/path/toserviceaccount/credentials.json`
3. mount Google Sevice account json file as credentials.json
4. add the service account`s email as Viewer to the Google Sheet document. (maybe not necessary)
5. In Superset`s SQL lab execute query
`SELECT * FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit?gid=174770703#gid=174770703"`
Where URL is your Google sheet document.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] monkey01127 commented on issue #21152: [Gsheets/shillelagh] Unable to add privately shared sheets
Posted by GitBox <gi...@apache.org>.
monkey01127 commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1239174006
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?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-2825775378
Closing this since it's been answered and silent for 5 months since. However, the UI has improved, and you can now add credentials through the DB connection modal with the GSheets connector. Here's the PR explaining. https://github.com/apache/superset/pull/32048
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
dosubot[bot] commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-2159173280
<!-- Greeting -->
Hey @rusackas! I'm here to help while we wait for others to chime in. I'm a bot that can help you with bugs, questions, and becoming a contributor.
<!-- Answer -->
To resolve the error "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 Google Sheet to Superset, ensure the following:
1. **Check for Typos**: Verify that the URL of the Google Sheet is correct and does not contain any typos.
2. **Match the Type of Google Sheets Allowed**: Ensure that the selection for the type of Google Sheets allowed matches the input. This means that the Google Sheet should be shared appropriately and accessible based on the configuration.
3. **Service Account Credentials**: Ensure that the service account credentials are correctly configured and have the necessary permissions to access the Google Sheet. The required permission scope is `https://www.googleapis.com/auth/spreadsheets` <sup>[[1]](https://github.com/apache/superset/blob/master/superset/db_engine_specs/gsheets.py)</sup>.
4. **Sheet Name and URL**: Ensure that both the sheet name and URL are provided and correctly configured in the catalog.
Here is the relevant code snippet that performs these checks:
```python
for name, url in table_catalog.items():
if not name:
errors.append(
SupersetError(
message="Sheet name is required",
error_type=SupersetErrorType.CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {"idx": idx, "name": True}},
),
)
return errors
if not url:
errors.append(
SupersetError(
message="URL is required",
error_type=SupersetErrorType.CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {"idx": idx, "url": True}},
),
)
return errors
try:
results = conn.execute(f'SELECT * FROM "{url}" LIMIT 1')
results.fetchall()
except Exception: # pylint: disable=broad-except
errors.append(
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,
level=ErrorLevel.WARNING,
extra={
"catalog": {"idx": idx, "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.",
},
],
},
),
)
idx += 1
```
Additionally, ensure that the Google Sheet is shared with the service account email and that the service account has the necessary permissions <sup>[[1]](https://github.com/apache/superset/blob/master/superset/db_engine_specs/gsheets.py)</sup><sup>[[2]](https://github.com/apache/superset/blob/master/tests/unit_tests/db_engine_specs/test_gsheets.py)</sup>.
<!-- Dosu Comment Footer -->
*To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] JDiego199 commented on issue #21152: [Gsheets/shillelagh] Unable to add privately shared sheets
Posted by "JDiego199 (via GitHub)" <gi...@apache.org>.
JDiego199 commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1613921419
I had the same error and I solved it by changing the country to the United States in the Google Sheet settings
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "thiyagu-lily (via GitHub)" <gi...@apache.org>.
thiyagu-lily commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-2455594241
any updates on this issue?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "iliakasianov (via GitHub)" <gi...@apache.org>.
iliakasianov commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-2485659701
The same with release tag [4.1.1rc1](https://github.com/apache/superset/releases/tag/4.1.1rc1).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-2159162616
Still hoping @betodealmeida can chime in here, but in the meantime, perhaps @dosu-bot can!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "rinatik66 (via GitHub)" <gi...@apache.org>.
rinatik66 commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1798551372
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](https://webapps.stackexchange.com/questions/113082/setting-up-decimal-separator-thousand-separator-and-date-in-google-spreadsheets)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "shauryagoel (via GitHub)" <gi...@apache.org>.
shauryagoel commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1755317203
Has anyone tried following this [link](https://docs.preset.io/docs/google-sheets-private-connection)?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] sonnysideup commented on issue #21152: [Gsheets/shillelagh] Unable to add privately shared sheets
Posted by "sonnysideup (via GitHub)" <gi...@apache.org>.
sonnysideup commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1597698922
what's happening here? we're hitting the same issue
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] mittalsuraj18 commented on issue #21152: [Gsheets/shillelagh] Unable to add privately shared sheets
Posted by "mittalsuraj18 (via GitHub)" <gi...@apache.org>.
mittalsuraj18 commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1468020734
Facing the same issue here
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "hawkz (via GitHub)" <gi...@apache.org>.
hawkz commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-3323293870
I believe I've solved this for my connection attempt.. wanted to share the steps that worked for me.
1. Go to databases → + Database
2. Give the display name you want and the SQL URI: `gsheets://`
3. Setup a service account in cloud console, making sure you've got drive and sheets APIs enabled.
4. Invite the service account email to have view access to your sheet.
5. Upload the service credentials into the database advanced tab BUT edit them first to add `{"service_account_info": `before the Google downloaded JSON and `}` after.
6. Make SURE that the sheet is free of formulas or content that'll confuse it. (I had a `=now()` that threw me for a while)
7. Go to SQL lab, and query for your sheet in the `SELECT * FROM "https://docs.google.com/spreadsheets/d/1IZgjkXXX6YI/edit?gid=0#gid=0"` format (the gids are copied from the URL as I view the right tab/sheet in the google sheet workbook.
Should then let you query and start doing fun stuff.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "aarfah-a (via GitHub)" <gi...@apache.org>.
aarfah-a commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1790169130
Facing same issue for private sheets. Public sheets are working fine. Please help.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "matheusbsilva (via GitHub)" <gi...@apache.org>.
matheusbsilva commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1821553962
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] stefanocascavilla commented on issue #21152: [Gsheets/shillelagh] Unable to add privately shared sheets
Posted by GitBox <gi...@apache.org>.
stefanocascavilla commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1265387732
Same issue here
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] monkey01127 commented on issue #21152: [Gsheets/shillelagh] Unable to add privately shared sheets
Posted by GitBox <gi...@apache.org>.
monkey01127 commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1239176107
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?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas closed issue #21152: [Gsheets/shillelagh] Unable to add privately shared sheets
URL: https://github.com/apache/superset/issues/21152
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [Gsheets/shillelagh] Unable to add privately shared sheets [superset]
Posted by "nanaones (via GitHub)" <gi...@apache.org>.
nanaones commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1937886551
same too
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] RedHotUnicorn commented on issue #21152: [Gsheets/shillelagh] Unable to add privately shared sheets
Posted by "RedHotUnicorn (via GitHub)" <gi...@apache.org>.
RedHotUnicorn commented on issue #21152:
URL: https://github.com/apache/superset/issues/21152#issuecomment-1615253343
It look like superset could read gsheets only if data inside is in text format
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org