[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

[rusackas]

Still hoping @betodealmeida can chime in here, but in the meantime, perhaps @dosu-bot can!

[dosubot[bot]]

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.

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 ^[1].

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:

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 ^[1][2].

To continue talking to Dosu, mention @dosu.

[BrayNarvi]

The same error too. Can anybody help? @betodealmeida