Incubator-superset: 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 :thumbsup: or :thumbsdown: to give our bot feedback!

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

[francishemingway]

Hi Brylie,

This may be of interest to you:
https://github.com/apache/incubator-superset/pull/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}"

[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?