Spreadsheet, or Google Sheets, data source

[brylie]

We have internal stakeholders who regularly work with data in spreadsheets. We would like to make it simple for them to get spreadsheet data into the data portal.

Since our staff use Google Sheets (and Drive), it would make sense that they could upload the data spreadsheets to google Drive and then connect them to Superset for dashboarding.

Alternatively, they could upload the spreadsheet to Superset, via the UI.

[issue-label-bot[bot]]

Issue-Label Bot is automatically applying the label #enhancement to this issue, with a confidence of 0.74. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[francishemingway]

Hi Brylie,

This may be of interest to you:
#5915
Francis

[mistercrunch]

This works well, though it's hard to get it to work with non publicly shared google sheets. I'm pretty sure it's doable though.

[brylie]

By way of example, Redash accesses Google Sheets that are shared to a specific "email"

https://redash.io/help/data-sources/querying/google-spreadsheet

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[brylie]

I think the @Stale bot is a bit misguided. I understand it is important to groom the issues, but this feature may be a while in the works.

[jaksmid]

@mistercrunch How do you make it work with non public sheets?
https://github.com/betodealmeida/gsheets-db-api mentions generated json file but it is not clear to me how would you inject that the sqlalchemy connection in superset without modifying the source code. Thanks!

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[brylie]

Is it possible to flag issues, so the stale bot doesn't close them? I.e. this feature request seems important, particularly the need for private sheets.

[mistercrunch]

@brylie pinned it so it won't get closed

It'd be great to have a blog post walking people step by step. The short story is something like:

• pip install gsheetsdb
• pip install gsheetsdb[sqlalchemy]
• create a database connection in Superset with the sqlalachemy url gsheets://
• add a "table" by using the url of the spreadsheet as the table name
• you can also in SQL Lab go SELECT * FROM "{spreadsheet_url}"

[CharlesYe8848]

it sounds doesn't work when i create create a database,can you help me?

[hemantaggarwal]

@mistercrunch :
What will be the full uri for gsheets??

[redko-o]

@hemantaggarwal
Did you make correct URI for gsheets?
Is there a way to work with private sheets?

[mistercrunch]

Full URI is gsheets://. There is a way to work with private sheets, and to see "public within my org" in GSuite, but it's fairly tricky. @betodealmeida can probably provide pointers. We kind of owe the world a blog post on this whole topic.

[hemantaggarwal]

@redko-o : What mistercrunch told is the full URI which works for public sheets but I am unable to find a way to connect private google sheets with Superset.
@mistercrunch : I figured the URI after few trials and this feature of linking public gsheets is really amazing but my use case is to connect private gsheets inside organization.
I didn't know there is way to work with private sheets although I tried to Presto as intermediate to connect gsheets and Superset but that also didn't workout completely.
@betodealmeida Can you please tell us all how to work with organization's google sheet in Superset? It will be a great help.

[redko-o]

@hemantaggarwal, @mistercrunch thanks for the hints!
Also, find an example of connecting BigQuery to the Superset (https://superset.incubator.apache.org/installation.html#google-bigquery). They use Google Service Account to do that adding JSON configuration to the Database extras. Probably, It can be the way of gsheets can work :)

[hemantaggarwal]

@redko-o
I also went through this but it didn't work out for me as my organization don't use Big Query. Due to this we tried using Presto as it also uses google service account but google service account is consider as external account inside gsuite domain even if it is created through organization ID, hence cannot use it with private gsheets.

[anshuman73]

@hemantaggarwal I think one way might be to give access of the Gsheets to that particular service account email ID. I think Redash does something similar.

[anshuman73]

Also, sorry if I'm misunderstanding, but was a final way found to connect superset to public Gsheets in an org?

I went through this, but couldn't get how to do this in Superset itself.

[hemantaggarwal]

@anshuman73 : Public gsheets are easy to connect like mentioned above, only issue is with private gsheets.
If you have organization specific domain then it will treat service account also as outside organization id and hence can't share with it.

[anshuman73]

@hemantaggarwal Ahh, okay. Thanks for the clarity

[ispulkit]

Can you explain a bit about the workaround for private and org-based gSheets?

[ghost]

Any update on the workaround for private gSheets?

Edit: I ended up figuring it out. A workaround is to share the sheet for anyone who has the link, then use that shared URL to query the Sheet.

[betodealmeida]

For org-based sheets:

1. Create a service account (https://console.cloud.google.com/iam-admin/serviceaccounts/).
2. Enable domain-wide delegation on the account and save the Client ID.
3. Download the key as JSON.
4. Add the "Client ID" to "Manage OAuth clients" (https://admin.google.com/${YOUR-DOMAIN-HERE}/AdminHome?chromeless=1#OGX:ManageOauthClients), with the following scopes:

• https://www.googleapis.com/auth/spreadsheets.readonly
• https://www.googleapis.com/auth/drive.readonly
• https://spreadsheets.google.com/feeds

5. In the DB configuration in Superset add the JSON payload to a key called service_account_info under "Secure Extra".
6. Alternatively, add a key called service_account_file under "Secure Extra" pointing to the location of the JSON file.

An example of Secure Extra:

{
    #"service_account_file": "/path/to/file.json",  # either use service_account_file or service_account_info
    "service_account_info": {
      "type": "service_account",
      "project_id": "gsheetsdb",
      "private_key_id": "XXX",
      "private_key": "-----BEGIN PRIVATE KEY-----\nXXX\n-----END PRIVATE KEY-----\n",
      "client_email": "[email protected]",
      "client_id": "109948634192943148536",
      "auth_uri": "https://accounts.google.com/o/oauth2/auth",
      "token_uri": "https://oauth2.googleapis.com/token",
      "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
      "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/shillelagh%40gsheetsdb.iam.gserviceaccount.com"
    }
}

This will give Superset access too all spreadsheets in the company. This means that if your CEO has shared a spreadsheet with [email protected] and someone is logged as [email protected] they will have access to the spreadsheet. This is probably not what you want.

To prevent this, the easy solution is to edit "Secure Extra" again and add this key:

"subject": "[email protected]",

Where [email protected] is a limited account. With this, the Gsheets connector will impersonate [email protected], so if something is shared to a specific email (like [email protected]) they won't have access. Superset will have access only to documents shared within the org but not to a specific user.

The best solution is to replace subject dynamically based on the logged-in user. This will allow the CTO in the example above to user Superset to see the spreadsheet that was shared only with them (but if they create a chart and the charts gets cached potentially other users could see it!). To do this:

1. Enable "Impersonate User" in the Superset DB configuration for the Gsheets connection.
2. Create a DB_CONNECTION_MUTATOR so that subject is populated based on the user:

from sqlalchemy.engine.url import make_url

def DB_CONNECTION_MUTATOR(uri, params, username, security_manager, source):
    driver = make_url(uri).drivername
    if driver == "gsheets":
        user = security_manager.find_user(username=username)
        if user and user.email:
            params["subject"] = user.email
    return uri, params

[kamalkeshavani-aiinside]

@betodealmeida Is there a way I can use this feature without providing domain wide delegation? It will not be possible in my organization to add this access to a service account, but I still want to be able to connect private sheets easily in Superset.
I am okay with adding manual access(to required private sheets) for the service account.

[betodealmeida]

@kamalkeshavani-aiinside you can pass an OAuth token directly to the connection string, but as far as I know these are short lived:

engine = create_engine(
    "gsheets://",
    access_token="XXX",
)

One way to get the token is here: https://developers.google.com/oauthplayground/

I have plans of implementing support for auth in Superset via user tokens like this. Superset would identify that the connection needs to be authenticated, then do the OAuth dance to get the token and refresh it if necessary. But it's not on the roadmap yet.

[kamalkeshavani-aiinside]

@betodealmeida Thank you for that, it helped.

[yanoliv]

I tried to create a a GoogleSheet database connection in superset (V 1.2.0), but it's a 2 step Form with only the 'SQLALCHEMY URL' input, (and not the form show in https://docs.preset.io/v1/docs/google-sheets-private-connection)
Do you know why ? Superset version ?
thanks

[betodealmeida]

@yanoliv yeah, it's probably an old version. You can add the service account info to the "secure_extra" of the database, it should still work.

[samudurand]

I followed the instructions from @betodealmeida , but I had to change a few things to get things working.

• Skip step 4 (I don't know where that should be done).
• Share the sheet you want to query with the email of your service account

[aoelvp94]

I ran the following lines in order to build a minimal workable example with Google Sheets:

docker pull amancevice/superset
docker run --detach --name superset-container -p 8088:8088 amancevice/superset
docker exec -it superset-container pip install shillelagh==0.4.3
docker exec -it superset-container superset-init
docker exec -it superset-container superset set_database_uri -d "Google Sheets" -u gsheets://
docker restart superset-container 

Go to http://localhost:8088/, and then to SQL Lab/SQL Editor and run:

SELECT * FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/"

[CharlesYe8848]

您好，你发的邮件我已收到。