Streamlit & Google Sheets: The Easiest "Database"
Vložit
- čas přidán 11. 06. 2024
- 👉 DOWNLOAD MY EXCEL ADD-IN: pythonandvba.com/mytoolbelt
Easily connect your Streamlit apps to Google Sheets, both public and private! With just a few lines of code, your Google Sheet transforms into a quasi-database that supports CRUD operations. This is ideal for small projects where a comprehensive relational database might be overkill. Dive in to seamlessly integrate Google Sheets with your Streamlit apps! In this video, I'll show you how to set everything up to use the gsheets-connection package.
📑 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦:
gsheets-connection: github.com/streamlit/gsheets-...
Example code from the video: github.com/Sven-Bo/connect-st...
⭐ 𝗧𝗜𝗠𝗘𝗦𝗧𝗔𝗠𝗣𝗦:
0:00 - Introduction
0:45 - Connect to Public Spreadsheet
5:05 - Connect to Private Spreadsheet
9:19 - Practical Example
12:00 - Outro
◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘:
🌎 Website: pythonandvba.com
📝 GitHub: github.com/Sven-Bo
⭐ Discord: pythonandvba.com/discord
📷 Instagram: / codingisfun_official
▶️ Subscribe: czcams.com/users/CodingIsFun?sub...
📚 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝗳𝗼𝗿 𝗹𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗩𝗕𝗔 & 𝗣𝘆𝘁𝗵𝗼𝗻
Check out my recommendations: pythonandvba.com/resources
☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲❓
If you want to support this channel, you can buy me a coffee here:
pythonandvba.com/coffee-donation
*Yep, a spreadsheet isn't a database.* 🤓 But for small projects, this package is a game-changer: github.com/streamlit/gsheets-connection
(And, sneaky advertisement alert! 😉) Another game-changer is my Excel add-in, packed with features to make you an office superhero: pythonandvba.com/mytoolbelt
Ainda não sei falar a sua língua, mas a didática é perfeita, aula mais do que esperada, obrigado.
Fico feliz que tenha gostado. Obrigado por assistir! Cheers, Sven ✌️
Nice to see a structured solution to this challenge. I have worked with other libraries to achieve this but had some quirky issues with them. This is well documented and having GSheets be used to do public surveys and forms is where it is worth the effort to implement this approach. Thanks for sharing
Thanks for the positive feedback! Appreciate you taking the time to leave a comment. Cheers, Sven ✌️
This channel is always fun and a great source of creativity and amazing teacher! Thank you!
Thanks for checking out the video and for the kind words! ❤️
Really nice one and as always well explained! Continue like that.
Thanks a lot! I really appreciate all your comments and support! 🙏
Thanks so much for this! The steps were amazing, i liked how you showed how to resolve all of the errors.
Happy to hear that you enjoyed this video too! Thanks for the comments - I appreciate it! Cheers, Sven ✌️
Thank you very much it’s really needed
An absolute pleasure, very happy to hear that you found it useful! Cheers, Sven ✌️
Thank you for your video ! :) it is really helpful as usual 👍👍👍
Happy to hear that, Sujung. Thanks for watching! 👍
fun and useful and clear! Subscribed
Thanks for the kind words and welcome aboard! 🎉
Thank you for the video
My pleasure! Appreciate you taking the time to watch and leave a comment. ✌
This is great !
Make more ......
Thanks! Ok, will do! 🚀
Great! Tks so much
My pleasure! Appreciate you taking the time to watch and leave a comment.
You are amazing, thank you!!!!
Loved reading your comment! Thanks for the support!
this is awesome.
Glad you liked it. Thanks for watching and taking the time to leave a comment! 👍
@@CodingIsFun thanks for putting together the content.
Dam what a coincidence I was just working on a database and wondering if you can do that stuff on streamlit then found your vid 😂
Happy that you found my video! :)
Thank you very much Sven. I am always getting hung up on the environment requirements to run these. The version of python, streamlit and so on. Is there a way to list what version you have or a an in environment script creator to ensure that we don't get stuck before we get started? MUCH MUCH appreciate your videos!
Thanks for watching. In the GitHub repo for my example code (link in the description) you can find the requirements.txt file. Hope that helps!
Estava fazendo do zero algo que editasse a planilha dessa maneira dentro do Google sheets e dentro do streamlit junto com aggrid, mas esse seu pacote vai ser um mão na roda por conta das fórmulas! Vai ser de grande ajuda.
Thanks for watching! :)
@@CodingIsFun I tested it, and it worked perfectly. I combined the code with Streamlit, Ag-Grid, and this package. :)
This is great! I really loved it. How can I connect with other APIs?
Thank you for watching. Every API is unique, but generally speaking, you can use the 'requests' or 'httpx' package in your Streamlit app. I hope this helps! Happy coding!
Thank you so much. If you could make a video to show how to fill the spreadsheet directly from streamlit app, i really appreciate it. For example you can have a formula on streamlit app and use it to CRUD data to your spreadsheet.
Thanks for watching. That's exactly what I cover in the video. Check around the 9:19-minute mark. You'll just need to tailor those buttons to your requirements. Hope that helps. Happy Streamlit-ing!
Thank you for your answer. You're right. Very helpful video. Keep on bro.
Your website pythonandvba looks nice. How you build it, is it by streamlit or flask or ...?
Thanks. I created that website using WordPress
This is really great. Does this uses any kind of google sheet api key ?
Thank you for watching. The authentication method is explained in the video. Cheers, Sven ✌️
@CodingIsFun
Can I have a double connection (Streamlit-Excel + Streamlit-SQL server)?
I want to use Excel as entry form cause it's easy to use and sent the results to SQL server?
Can I connect Streamlit only to Google Sheets? Or Microsoft Excel is also an option?
Hi great tutorial, I am wondering if this can be done with Notion?
Thank you for watching and for all your comments. Yes, you could also use the Notion API, but you would need to check the Notion API documentation to see how to interact with the database:
developers.notion.com/reference/intro
Happy Coding! Cheers, Sven ✌️
Can I connect to two different spreadsheets?
You are God 🙏🙇♂️🙇♂️
I just needed to know this and you did it
Glad I could help! Thanks for watching and for the kind words.
Muito bom!
Obrigado por compartilhar.
É uma excelente alternativa para um problema que estou enfrentando em um app que desenvolvi.
O app gera um banco de dados (Sqlite) local no servidor, mas toda vez que um "push" é feito ele deleta o arquivo e cria o bando de dados do zero.
Talvez usar o Google Sheets resolva. Obrigado👍
Happy to hear that you liked the video! :)
Great video! One question, is it possible to have a column with public images links and see the images on the dashboard? If yes, how??
Thank you for watching. That sounds possible, but I have not tried it out. So, you might need to explore that yourself and give it a go. Happy coding! Cheers, Sven ✌️
@@CodingIsFun Thanks!
This video is awesome! Thank you so much! Do you know if there is any mean to share our streamlit dashboard And still get the "live" data from googlesheet?
Thanks for watching. As shown in the video, the connection is "live". Then you change the data in your spreadsheet, it will be reflected in your Streamlit app. Here is another (practical) example: czcams.com/video/_G5f7og_Dpo/video.html
Thanks@@CodingIsFun , it worked pretty well! 🎉
Amazing. You have a new subscriber! Is Pandas 2.x required? My repo is using pandas 1.4.2 and I get a serialization error.
Serialization of dataframe to Arrow table was unsuccessful due to: ("Could not convert '6H0' with typble.e str: tried to convert to int64", 'Conversion failed for column color_code with type object'). Applying automatic fixes for e str: tried to convert to int64", 'Conversion f
column types to make the dataframe Arrow-compatible.
I've even tried to recast that column as string on both the copy and a created dataframe whose rows I append to the copy, but the error persists.
Importantly to note, the code still runs
Solved:
The "error" was due to using "st.write(data.dtypes)", though I only added that because I had the error previously. I did at one point coerce the entire column in question to string in google sheets.
Well, the problem sort of persists if I want to view the data in streamlit. I have the error occur (more like a warning) and there are hundreds of (pads up to 999 or 1000) empty rows added. The google sheets is perfectly fine and updated though.
Thanks for watching and welcome aboard 🎉You might want to drop the null rows, using the following method: df.dropna(how='all')
I hope it helps! Happy Coding!
Hi, really nice bro, thank you for your video. Well, I have a question, how can I insert rows into my googlesheet? In your application you load information from googlesheets, but, in my case I would like to load google sheet and then I would like to insert new rows too.
Thanks for watching. You can append the new row to your dataframe and then update it. There might be more elegant ways, but this could be one solution. I hope it helps! Happy Coding!
@@CodingIsFun Great, that works!
How do I install the streamlit_gsheets package because it is showing error directly and also while installing from gihub link
Thanks for watching. Please create a separate virtual environment and try to install it again. Thanks!
Can it be possible to do the same procedure but with onedrive excel instead of google?
Thanks for watching. Currently, I am not aware, that there is package available for Excel online
Hi, it's working very well im my IDE But when I run streamlit its return http error 400 bad request !
Hey there, thanks for watching the video! I'm sorry I can't help you with your problem based on the information you provided. To give me a better idea of what's going on, it would be super helpful if you could write down which line of code is causing the error, let me know if you modified the code from the tutorial, and explain in more detail what you did to troubleshoot the problem. Don't forget to also give me some context about your setup and environment.
If you're having trouble figuring things out, another option is to join our Discord server at pyhtonandvba.com/discord. You can ask your question there and maybe someone in the community can help out.
Thanks for understanding.
is there a possiblity to use oauth to create a spreadsheet file for each user who uses the streamlit app? Meaning a user should be able to connect his own google drive via the streamlit app via oauth and than allow access to an empty spreadsheet in his or her own google drive and this database is populated and used only by the specific user. So there wouldnt be one database aka spreadsheet but the user has his own db in its own google drive. (I want to create a workout app like this)
Thanks for watching. That sounds possible. You might want to check out the st-authenticator package: github.com/mkhorasani/Streamlit-Authenticator
You can then store the user information in a database. Once logged in, you could retrieve the correct spreadsheet URL for the respective user.
Hope that gives you a starting point. Happy coding!
Danke dir für deinen Content, du bist echt schnell mit dem Antworten :D. Keep up the good work!@@CodingIsFun
Hi i love your videos but could you add something about data analysis process o do something with data frol logística?
Thank you for watching the video and for your suggestion. I already have a couple of data analysis videos on my channel. Not specifically about logistics data, but the approach will be similar. Here is one example using pandas to get you started: czcams.com/video/RXEP1R_ZNrs/video.htmlsi=QqH97mXS0jWo0gqw
@@CodingIsFun thank you i Will watch It because I'm now in to data analysis and will be really nice to have a way to do data analysis with streamlit
Woww! I am wondering if streamlit also support online Excel, Dataverse or Azure SQL?
Thanks for watching. Everything you could access via Python could potenially be also used in Streamlit. You might also want out to check out the Streamlit docs: docs.streamlit.io/knowledge-base/tutorials/databases
Happy Coding!
would chat with csv or pandasai be better solution for users who cannot write sql?
Thanks for watching. It depends on your usecase. This video is mainly focussed on how to connect Streamlit with Google Sheets
Is there an advantage of using google sheets vs Deta for example?
Thanks for watching. It really depends on your use case. Deta offers a NoSQL database, while Google Sheets is more like a relational database (even tho it's not a database 😉)
Thank you. Where can i learn sql
You are welcome. You can find many free SQL tutorials here on CZcams. Happy Learning! 🤓
Thank you for this interesting feature of streamlit.
However when I tried to run the sample, I experienced this error:
ImportError: cannot import name 'ValidationFailure' from 'validators.utils'
How to solve this problem?
Hey there, thanks for watching the video! I'm sorry I can't help you with your problem based on the information you provided. To give me a better idea of what's going on, it would be super helpful if you could write down which line of code is causing the error, let me know if you modified the code from the tutorial, and explain in more detail what you did to troubleshoot the problem. Don't forget to also give me some context about your setup and environment.
If you're having trouble figuring things out, another option is to join our Discord server at pyhtonandvba.com/discord. You can ask your question there and maybe someone in the community can help out.
Thanks for understanding.
@@CodingIsFun Thank you for your pointers. I joined Discord and post the complete error message in the "general" channel. I am not to familiar with Discord, I hope it is fine to ask my problem in that channel. Looking forward for the answer.
Hey brother happy new year quick question
Main issue is i am trying to connect my app without having to upload excel file - streamlit is not pulling the data unfortuanelty from g sheet
Hey brother. Hard to tell from a distance why you are facing an issue. Cheers, Sven
@@CodingIsFun yea video you made is more useful for just an excel sheet but when you have an advanced stream lit app that requires an excel sheet not so much
Thanks though brother
Hi, I'm getting the following error: ValueError: Spreadsheet must be specified. I did specify the url of the sheet in the secret file. Can anyone help me?
In any case, thank you for this video
Thanks for watching and for your question. Hard to tell from a distance why you are facing an error. Sorry that I cannot help.
how can i move to a new row, its always updating the first row
Please check out the example I put on GitHub. Cheers, Sven
Good Airtable replacement. Airtable just killed their free tier
Thanks for watching! 👍
hello, i dont know if someone can help me.
i got this error after running the app :
RefreshError: ('invalid_grant: Invalid JWT: Token must be a short-lived token (60 minutes) and in a reasonable timeframe. Check your iat and exp values in the JWT claim.', {'error': 'invalid_grant', 'error_description': 'Invalid JWT: Token must be a short-lived token (60 minutes) and in a reasonable timeframe. Check your iat and exp values in the JWT claim.'})
how can i resolve it ?
Hey there, thanks for watching the video! I'm sorry I can't help you with your problem based on the information you provided. To give me a better idea of what's going on, it would be super helpful if you could write down which line of code is causing the error, let me know if you modified the code from the tutorial, and explain in more detail what you did to troubleshoot the problem. Don't forget to also give me some context about your setup and environment.
If you're having trouble figuring things out, another option is to join our Discord server at pyhtonandvba.com/discord. You can ask your question there and maybe someone in the community can help out.
Thanks for understanding.
@@CodingIsFun Thank's for the reply,
in fact, i dont know exactly the line of code that causes this error, but i think it's about 'interaction with the google API".
And what's confusing is that, after i decided to try hosting the app (streamlit community cloud), it works perfectly.
i saw in some forums that it's related to an "non-synchronized time" between my machine and the google cloud.
Looks a bit lore complicated than easy hahah
Thanks for watching. I think setting up a database and connecting it to Streamlit might be a bit more of a hassle, though. 😅
@CodingIsFun streamlit is a but difficult concept for me. I think I had trouble getting the packages to work and then the placement of buttons wouldn't work unless I'm a certain spot 😅 but would love to see some more of these automated type videos!
ok spent 5 hours at this anyone else find an issue connecting a public google sheet? also the gid=0 on mine
With that limited information, I really can't assist you 😅. Providing the error message would be helpful. In any case, please set up a new, clean Python environment. Then, download the requirements.txt file from GitHub, install the libraries listed in that file into the new environment, and copy/paste the sample code from GitHub (as I demonstrated in the video). I'm confident it will work on your end too ;)
Hi Sven, Thank you for the video
We can use this for small projects that using GoogleSheets as Database. But is there any way to CRUD process within the Web UI ?
And also, could you make video that using RDBMS like PostgresDB for database ? and the CRUD Process within the Web UI, because business user that using this data.
Sorry for my english.
And thank you again for the great video, keep up the good work, and will wait for the next videos 🤩
Thanks for watching. I have shown you the CRUD operations in the video. :)
After following all the steps there is an unknown error 404🥺,how can i Fix it
Hey there, thanks for watching the video! I'm sorry I can't help you with your problem based on the information you provided. To give me a better idea of what's going on, it would be super helpful if you could write down which line of code is causing the error, let me know if you modified the code from the tutorial, and explain in more detail what you did to troubleshoot the problem. Don't forget to also give me some context about your setup and environment.
If you're having trouble figuring things out, another option is to join our Discord server at pyhtonandvba.com/discord. You can ask your question there and maybe someone in the community can help out.
Thanks for understanding.
Thank you so much for your reply, i was able to solve the problem,love your videos man.
@@CodingIsFun i have another question though, suppose I've link a gsheet and that gsheet contain multiple worksheet,i want to create a select box where i can select worksheet directly from web app and once new worksheet is added to my gsheet it will update automatically
Its not working: This is what my URL looks like "...edit#gid=0"
And what exactly is not working then?
@@CodingIsFun I am not using the spreadsheet you provided. I created mine and get this error "ValueError: Spreadsheet must be specified"
Now since my url dont have those digits, I couldn't use public option. my URL has a zero at the end. Could that be the problem? Please your help will be appreciated
@@ikesinachiajah8248 Did you share the technical email address with the spreadsheet as shown in the video?
Hi, getting this error:
from streamlit_gsheets.gsheets_connection import GSheetsConnection # noqa
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\AH\AppData\Local\Programs\Python\Python311\Lib\site-packages\streamlit_gsheets\gsheets_connection.py", line 39, in
from validators.utils import ValidationFailure
ImportError: cannot import name 'ValidationFailure' from 'validators.utils'
@CodingIsFun I love your videos, you are amazing! You explain things very clear and guide people through processes so easily!!! I admire you, please do more content like this, Please!!!
I'm having the same error as @blueierblue4499, for some reason i got this:
ImportError: cannot import name 'ValidationFailure' from 'validators.utils' (C:\Users\mikek\AppData\Local\Programs\Python\Python311\Lib\site-packages\validators\utils.py)
I hope you can help us with that!
Great video, many tks, i hope can help me, min 0:45 I try to install streamlit_gsheets from github bun doesn't work 😢 the error: Failed building wheel for duckdb 😢 ! Best Regadds
It is not working in my case. Error occurs.
df = conn.read()
^^^^^^^^^^^
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-Hi and thank you. I tried to use your method. However, the error is nowL
packages/streamlit_gsheets/gsheets_connection.py", line 570, in read
return self.client.read(
^^^^^^^^^^^^^^^^^
File “/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/streamlit_gsheets/gsheets_connection.py”, line 419, in read
raise ValueError(“Spreadsheet must be specified”)
ValueError: Spreadsheet must be specified
Ensure that you share the technical email address with your Spreadsheet as explained in the video. Cheers, Sven ✌️
I don't know why this error occurs.
-------------------------------------------------------------------------------------------------------------------------------
I solved it by googling.
---------------------------------------------------
import ssl
ssl._create_default_https_context = ssl._create_unverified_context
Hey there, thanks for watching the video! I'm sorry I can't help you with your problem based on the information you provided. To give me a better idea of what's going on, it would be super helpful if you could write down which line of code is causing the error, let me know if you modified the code from the tutorial, and explain in more detail what you did to troubleshoot the problem. Don't forget to also give me some context about your setup and environment.
If you're having trouble figuring things out, another option is to join our Discord server at pyhtonandvba.com/discord. You can ask your question there and maybe someone in the community can help out.
Thanks for understanding.