EPIC Google Sheets to Interactive Dashboard in Python ft. Streamlit / CSS

[@iNEEL]

Great work, Fanilo. This video is going to be a goldmine for all streamlit users!

[@andfanilo]

💪 let's hope so! thanks for the support :)

Awesome tutorial as always... I've used CSS too but that :has selector is a really great trick... Thanks for sharing and thanks a lot for the mention of my dashboard about Poverty ❤

[@andfanilo]

Credits to arnaudmiribel.github.io/streamlit-extras/extras/stylable_container/ for the initial idea, plus st.html makes it a little easier! And I did already tell you your dashboard was the initial trigger to this video? So thanks for building/sharing it 😊 looking for your next dashboard!

@@andfanilo I couldn't feel more proud, thank you for your support, I have learned a lot from your videos, I'm really glad to be useful for you and to give something back.

[@anirudhvinod3966]

Thank you for this tutorial, it was very helpful. I am currently trying to build a similar dashboard which displays the stock prices live. So I have been playing around with different charting packages and

[@andfanilo]

Glad it was helpful! Sorry the end of the comment is cut on my side, what libraries are you playing with? How is using st.experimental_fragment(run_every=…) to update the plot every second for pseudo realtime data?

[@anirudhvinod3966]

@@andfanilo Hey sorry about that I got cut out while writing the comment. So I came across the lightweight-charts library, which generates the best looking charts imo. But the problem I'm facing now is that I'm unable to convert it into a live chart. So I wanted to ask if you would know much about that and could provide any advice on it. And I actually haven't used the st.experimental_fragment() method. I think you've already pointed me in the right direction! Let me try if that method works with lightweight-charts and will let you know. Thank you!

[@MuhammadQasim-92]

Great tutorial Fanilo.

[@andfanilo]

@@MuhammadQasim-92 thanks for the support ^^

[@kareemrasheedbabatunde9026]

You finally did it

[@andfanilo]

It was a long ride! Hope you enjoyed it :) see you on the next one!

[@kareemrasheedbabatunde9026]

@@andfanilo just finish watching, its awesome

[@ici6308]

This is the game changer.

[@andfanilo]

Thanks for watching, see you on the next one :)

[@tylerjrichards_ds]

I really love the sparklines, one of my favorite features. And I HATE how hard it is still to read / write with google sheets, st.connection makes it much easier but on the google side it still is not super easy

[@andfanilo]

Yeah, I find this service account creation demanding for new users 😅 that could be automated maybe, like with Terraform or a command to paste into Google Shell Sparklines ftw, I initially wanted to use the st-aggrid ones but they require an enterprise license so yay for Streamlit Sparklines! Thanks for watching :)

[@DavidOden-kk8nm]

Thank you @Fanilo❤

[@andfanilo]

Thanks for watching, see you on the next one :)

[@hiddenni]

can we use libraries like daisyui to change the css using the invisible span element? it would be amazing

[@andfanilo]

I have tried but utterly failed because Tailwind needs JS now so st.html won't embed it easily... I think for now your best bet is to create a Streamlit component for your favorite daisyui components, like github.com/okld/streamlit-elements did for Material or github.com/ObservedObserver/streamlit-shadcn-ui for Shadcn

[@hiddenni]

@@andfanilo if i use components.html(), instead of st.html(), would work?

[@andfanilo]

@@hiddenni 🤔 yeah I think it should, keep us updated if you try it out

[@gabrr]

Amazing, stuff man!

[@andfanilo]

Thanks for watching, see you on the next one :)

[@trungphamquang5701]

I love new trick with CSS :D

[@andfanilo]

Eheh, this is actually a customized version of arnaudmiribel.github.io/streamlit-extras/extras/stylable_container/ made simpler with st.html. I figured it would be better to explain it so people get to learn CSS (sometimes I think my channel will be rebranded "CSS for data scientists"!) Until one day we get custom classes on widgets maybe one day 🙃

[@contrarianthinker]

Hey can you help me write some formulas I have for a spreadsheet

[@ChristhianSantanaCunha]

Hello, first I want to thank you for your videos. Your material helps the streamlit community a lot. Is there any component that allows you to use a polygon by drawing on geojson layers in folium or leafmap to cut the data spatially and download the data that is overlapped by this polygon? This would be very relevant to the geospatial analysis community and would greatly enhance its use among us. thanks again.

[@andfanilo]

Hello! I don't do much geospatial analysis so I'm terrible at giving recommendations for that nor following the news about it. Have you tried streamlit-folium's draw support folium.streamlit.app/draw_support ? You probably need to do the geointersection between layers and maps yourself though... Otherwise, that may be a case where ipyleaflet + Panel may be a better combo 🤔

[@ChristhianSantanaCunha]

@@andfanilo Thank you Fanilo. You have great contributions that help me A LOT. I did a lot of searching on support, but I'm going to post my question there. Thank you and stay well, I wish you success!

[@LouisDeconinck]

Great tutorial, how would you go about making this reaponsive so it also looks nice on mobile devices?

[@andfanilo]

Thanks! The Streamlit columns should be responsive already ( like in the resize gif on blog.streamlit.io/introducing-new-layout-options-for-streamlit/#go-horizontal-with-columns ), and adding use_container_width on the Plotly chart should resize the plot for smaller screen sizes You won't have much control on size breakpoints for a different layout depending on the size of the mobile device screen though...so not much more, if mobile responsive to different screen sizes is of capital importance then maybe you'll need to change framework for one that adds CSS breakpoints

[@GodX36999]

Anyway thank goodness

[@WhylerGame]

Great content, thank you. Is it possible to create a bidirectional component with st.html ? I'm trying to make an image button and get the onclick value in python.

[@andfanilo]

Thanks for the support! At this time it's not possible to get a callback from HTML to Python with st.html, you have to build a bidirectional component BUT you don't have to use the usual react template or react-less template from github.com/streamlit/component-template You can use discuss.streamlit.io/t/code-snippet-create-components-without-any-frontend-tooling-no-react-babel-webpack-etc/13064 instead Also I think there's this st-clickable-image discuss.streamlit.io/t/custom-component-to-display-clickable-images/21604 you can maybe get inspired from its code

[@kiritojuni]

why is that st.column_config.AreaChartColumn is not allow in my streamlit? any step that i missed? I also cannot apply style.format, any clue?

[@andfanilo]

Hello, that's strange, which streamlit/python/OS are you running?

[@kiritojuni]

@@andfanilo turns out just need to update my stremlit version because in older version only can use barchart

[@v1tr4elits]

Can you make a video of controlling IoT device like with esp32 with streamlit ?

[@andfanilo]

I know some creators install Streamlit on Rasberry Pi, but that is the extent of my knowledge on IoT 😅 I'll look into it but I won't guarantee an immediate video about it. Any resources to point to?

[@Raven9960]

I have tried uploading this to streamlit but its not working. I need help since we cant change json to toml and even if we did airbyte is not able to read the format correctly.

[@andfanilo]

Hey! You can stringify your JSON into a TOML value and read it + JSONify it back like in blog.streamlit.io/streamlit-firestore-continued/#convert-our-json-key-into-a-secrets-toml-file I haven't tried to confirm but it should work, let us know if it does work!

[@Raven9960]

@@andfanilo omg it works!! 😭😭😭 thank you soo much fam, like literally been frustrated to find a solution. Thanks alot life saver

[@aleman12345]

3 minutes after release, new record!

[@andfanilo]

Ahah you're the first :D congrats!

[@harshgupta3641]

This is reallly amazing stuff can we use google sheets without using google cloud ?

[@andfanilo]

Thank you! I'm not sure I understood the question though, you mean without creating a Google Cloud Service account?

[@harshgupta3641]

@@andfanilo yes , I tried using streamlit_gsheets package after making spreadsheet public but not able to read the other worksheets , only by default first worksheet is coming with the help of url

[@picklenickil]

hey man, why dont you put these under pay wall? its a gem and a waste that you dont get compensated for all these work. I had a question (as wll as on st-com-cloud): Im aware of the st-paywall package that lets you put content under a paywall, but is it possible to implement a monthly subscription model? What kind of communication do i have to have btweeen streamlit and stripe (lets say for simplicity) or secondly lets say im measuring the resources ( GPU minutes/ Open AI tokens) the user is using and want them to “pre-pay” or pre-load credits. Im counting, validating and saving them, just need to connect them to a payment pipeline. any suggestions?

[@andfanilo]

Hello! Well yeah, at least this video was sponsored by Airbyte so I did get compensated a little :). Also I'm hoping to paywall longer versions of this tutorial, and have this video act like a "teaser" for the longer version. That's my plan. From memory st-paywall does enable a Stripe or Buymeacoffee subscription model, though I haven't tried yet. I do want to try it ASAP because I really want to get more of my work monetized too 🤑 I've been playing more and more with Stripe recently so hopefully I'll have a better answer around a credits wallet workflow to share with you.

[@picklenickil]

​@@andfanilo I tried ST paywall, it's good I guess.. yet no chache so user must stay away from refreshes. However switching between pages seem to persist the creds. I had a separate question. Which I believe I have asked you elsewhere already but knowledge can be imported many times. How do I manage multi different chatbot interfaces without complete rerum of page or other Chat bots history (exptfrgmt) with session state as storage..or a db idc..

[@by_westy]

how did you open streamlit preview in VS code

[@andfanilo]

Actually it's just VSCode and a web browser side-by-side, but I configured VSCode margins so the topbar for both windows match for the screen recording ( gist.github.com/andfanilo/ae99aed9c454a46c1dbfe6a0b9fa0012 to check my config)

[@abdielgoni5690]

What is the difference beetwen pyairbyte and st gsheet connection?

[@andfanilo]

gsheets-connection is an official st.connection ( docs.streamlit.io/develop/api-reference/connections/st.connection ) wrapper around GSpread to connect to GSheets. The library puts the connection and data in Streamlit cache for you so you only have to query it. It's a Streamlit-specific solution. Pyairbyte is a Python library for Airbyte ( airbyte.com/ ) that manages the connection and data download to a lot of different databases like MySQL, GSheets (which I use in the video) or BigQuery. It comes with more features than gsheets-connection like caching data in DuckDB, or fetching datasets already managed in a Cloud instance of Airbyte. If you work in a company that uses Airbyte to connect and manage multiple data sources, chances are you can directly download or refresh data from their self-hosted Airbyte platform. It's a popular solution amongst data engineers, not just specifically Streamlit. What I did in this tutorial is basically recreate a st.connection gsheet pyairbyte, without explicitly calling it st pyairbyte gsheet connection...

[@abdielgoni5690]

Ahh i see. Thank you for the comprehensive explanation

[@thebudaxcorporate9763]

awesome

[@andfanilo]

Thanks for watching, see you on the next one :)

[@JuanSuarez-ei7hv]

Great!!!

[@andfanilo]

Thanks for watching! See you on the next one :)

[@ruidinis75]

that is great

[@andfanilo]

Thanks for watching, see you on the next one!

[@GodX36999]

I think streamlit is outdated, now taipy is at the edge of art and much more better