EPIC Google Sheets to Interactive Dashboard in Python ft. Streamlit / CSS
Vložit
- čas přidán 29. 07. 2024
- Looking for an end to end implementation of a live stocks dashboard from Google Sheets to a Python Web App? In this tutorial, you'll discover my comprehensive workflow to build my Streamlit web dashboard:
- How I build a live Stocks tracker in a Google Sheets workbook
- How I connect to Google Sheets from Python using @AirbyteHQ ( airbyte.com/product/pyairbyte )
- How I build the Streamlit app with horizontal layout columns, Plotly Candlestick and a grid of indicators for easy reading
- How I add custom styling to the Streamlit app with heavy use of HTML inspection, targeting widgets to add colors, box shadows and new Google fonts
By the end of this video, you'll be equipped with the skills to quickly recreate Excel dashboards within Python web apps. No more struggling with complex VBA macros - empower yourself to generate interactive and visually appealing dashboards for financial data analysis.
---
📧 Want a free, slower paced live coding of this tutorial? Sign up to my email list to be alerted first, and keep up to date with the latest news, tutorials and resources about Streamlit & friends ➡ andfanilo-newsletter.streamli...
💰 Buy me a coffee to stay awake while editing www.buymeacoffee.com/andfanilo
🗣️ Find my socials on andfanilo.com
🔗 Links
- Source code + Sample Data: github.com/andfanilo/social-m...
- Or fork this Google Sheets: docs.google.com/spreadsheets/...
🔧 My tools (Affiliate links to support me!)
- 📕 Streamlit Official Book - amzn.to/3QuRPwl
- 📷 Main Camera - amzn.to/3QvTosB
- 📷 Main Lens - amzn.to/3svYCwq
- 🎙️ Microphone - amzn.to/47l5ewA
- 🎵 Music & Sound Effects - www.epidemicsound.com/referra...
00:00 Dashboard Mockup
01:08 Build live stocks tracker in Google Sheets
02:40 Create a service account for Python
05:14 Connect to Google Sheets with Airbyte
09:17 Download Google Sheets data to Python with Airbyte
10:31 Display a Dataframe with inline sparkline
12:14 Track data with an interactive Candlestick plot
14:42 Create a grid of indicators
16:41 CSS Styling Prerequisites: inspect HTML
19:04 Create a grid of company watchcards
20:54 Inject a Google font into the Streamlit title
22:51 CSS Styling of indicators into cards
25:09 CSS Styling of company watch cards
26:59 Enjoy the dashboard!
---
🪶 A segment of this video was sponsored by @AirbyteHQ . It was very easy to setup, go build Streamlit apps over new data sources with Airbyte!
⚠️ Links included in this description might be affiliate links. If you purchase a product or service with the links that I provide I may receive a small commission. Thank you for supporting my channel so I can continue providing you with free content!
#streamlit #python #datascience #dataapps
Great work, Fanilo. This video is going to be a goldmine for all streamlit users!
💪 let's hope so! thanks for the support :)
Great tutorial Fanilo.
@@MuhammadQasim-92 thanks for the support ^^
Amazing, stuff man!
Thanks for watching, see you on the next one :)
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 ❤
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.
You finally did it
It was a long ride! Hope you enjoyed it :) see you on the next one!
@@andfanilo just finish watching, its awesome
This is the game changer.
Thanks for watching, see you on the next one :)
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
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?
@@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!
I love new trick with CSS :D
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 🙃
Thank you @Fanilo❤
Thanks for watching, see you on the next one :)
Great!!!
Thanks for watching! See you on the next one :)
awesome
Thanks for watching, see you on the next one :)
that is great
Thanks for watching, see you on the next one!
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
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 :)
Great tutorial, how would you go about making this reaponsive so it also looks nice on mobile devices?
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
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.
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!
@@andfanilo omg it works!! 😭😭😭 thank you soo much fam, like literally been frustrated to find a solution. Thanks alot life saver
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.
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
can we use libraries like daisyui to change the css using the invisible span element? it would be amazing
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
@@andfanilo if i use components.html(), instead of st.html(), would work?
@@hiddenni 🤔 yeah I think it should, keep us updated if you try it out
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.
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 🤔
@@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!
3 minutes after release, new record!
Ahah you're the first :D congrats!
This is reallly amazing stuff can we use google sheets without using google cloud ?
Thank you!
I'm not sure I understood the question though, you mean without creating a Google Cloud Service account?
@@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
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?
Hello, that's strange, which streamlit/python/OS are you running?
@@andfanilo turns out just need to update my stremlit version because in older version only can use barchart
how did you open streamlit preview in VS code
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)
Can you make a video of controlling IoT device like with esp32 with streamlit ?
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?
What is the difference beetwen pyairbyte and st gsheet connection?
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...
Ahh i see. Thank you for the comprehensive explanation
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?
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.
@@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..