Streamlit & Google Sheets: The Easiest "Database"
👉 Explore All My Excel Solutions: pythonandvba.com/solutions
𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
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
𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🆓【𝗙𝗥𝗘𝗘】Excel Add-in (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): pythonandvba.com/mytoolbelt
📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): pythonandvba.com/grafly
🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): pythonandvba.com/cuteplots
🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): pythonandvba.com/emojify
📑 Excel Templates: pythonandvba.com/go/excel-tem...
🎓 My Courses: pythonandvba.com/go/courses
📚 Books, Tools, and More: pythonandvba.com/resources
𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🔗 LinkedIn: / sven-bosau
📸 Instagram: / codingisfun_official
💻 GitHub: github.com/Sven-Bo
💬 Discord: pythonandvba.com/discord
📬 Contact: pythonandvba.com/contact
☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
If you want to support this channel, you can buy me a coffee here:
▶ pythonandvba.com/coffee-donation
Пікірлер: 136
*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
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
@CodingIsFun
5 ай бұрын
Thanks for the positive feedback! Appreciate you taking the time to leave a comment. Cheers, Sven ✌️
Really nice one and as always well explained! Continue like that.
@CodingIsFun
11 ай бұрын
Thanks a lot! I really appreciate all your comments and support! 🙏
This channel is always fun and a great source of creativity and amazing teacher! Thank you!
@CodingIsFun
6 ай бұрын
Thanks for checking out the video and for the kind words! ❤️
Thanks so much for this! The steps were amazing, i liked how you showed how to resolve all of the errors.
@CodingIsFun
5 ай бұрын
Happy to hear that you enjoyed this video too! Thanks for the comments - I appreciate it! Cheers, Sven ✌️
Ainda não sei falar a sua língua, mas a didática é perfeita, aula mais do que esperada, obrigado.
@CodingIsFun
3 ай бұрын
Fico feliz que tenha gostado. Obrigado por assistir! Cheers, Sven ✌️
I have been watching many of your tutorials... they all are amazing.... Congrats for such a great content which are very well structured. Definitely you deserve a $ compensation , I have just sent via "Thanks"...first time. Thank you for sharing.
@CodingIsFun
28 күн бұрын
Glad you like them! Thanks a ton for the support-very much appreciated. Happy coding! Cheers, Sven ✌️
Thank you for your video ! :) it is really helpful as usual 👍👍👍
@CodingIsFun
11 ай бұрын
Happy to hear that, Sujung. Thanks for watching! 👍
Thank you very much it’s really needed
@CodingIsFun
4 ай бұрын
An absolute pleasure, very happy to hear that you found it useful! Cheers, Sven ✌️
This is great ! Make more ......
@CodingIsFun
11 ай бұрын
Thanks! Ok, will do! 🚀
fun and useful and clear! Subscribed
@CodingIsFun
10 ай бұрын
Thanks for the kind words and welcome aboard! 🎉
Thank you for the video
@CodingIsFun
11 ай бұрын
My pleasure! Appreciate you taking the time to watch and leave a comment. ✌
You are amazing, thank you!!!!
@CodingIsFun
6 ай бұрын
Loved reading your comment! Thanks for the support!
Great! Tks so much
@CodingIsFun
7 ай бұрын
My pleasure! Appreciate you taking the time to watch and leave a comment.
Thanks!
@CodingIsFun
28 күн бұрын
Thanks a lot for the support! Much appreciated! -Sven ✌️
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.
@CodingIsFun
11 ай бұрын
Thanks for watching! :)
@Jose-bt2kt
10 ай бұрын
@@CodingIsFun I tested it, and it worked perfectly. I combined the code with Streamlit, Ag-Grid, and this package. :)
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 😂
@CodingIsFun
11 ай бұрын
Happy that you found my video! :)
this is awesome.
@CodingIsFun
9 ай бұрын
Glad you liked it. Thanks for watching and taking the time to leave a comment! 👍
@tinylittleanj2
9 ай бұрын
@@CodingIsFun thanks for putting together the content.
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!
@CodingIsFun
11 ай бұрын
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!
Hi great tutorial, I am wondering if this can be done with Notion?
@CodingIsFun
4 ай бұрын
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 ✌️
This is great! I really loved it. How can I connect with other APIs?
@CodingIsFun
11 ай бұрын
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.
@CodingIsFun
11 ай бұрын
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!
@Beauchant
11 ай бұрын
Thank you for your answer. You're right. Very helpful video. Keep on bro.
Can I connect to two different spreadsheets?
This is really great. Does this uses any kind of google sheet api key ?
@CodingIsFun
4 ай бұрын
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?
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👍
@CodingIsFun
11 ай бұрын
Happy to hear that you liked the video! :)
How do I install the streamlit_gsheets package because it is showing error directly and also while installing from gihub link
@CodingIsFun
11 ай бұрын
Thanks for watching. Please create a separate virtual environment and try to install it again. Thanks!
Hi, it's working very well im my IDE But when I run streamlit its return http error 400 bad request !
@CodingIsFun
5 ай бұрын
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.
Can it be possible to do the same procedure but with onedrive excel instead of google?
@CodingIsFun
11 ай бұрын
Thanks for watching. Currently, I am not aware, that there is package available for Excel online
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?
@CodingIsFun
9 ай бұрын
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: kzread.info/dash/bejne/kXtpyJmol8KqoNI.html
@pedroaugustobb
9 ай бұрын
Thanks@@CodingIsFun , it worked pretty well! 🎉
would chat with csv or pandasai be better solution for users who cannot write sql?
@CodingIsFun
9 ай бұрын
Thanks for watching. It depends on your usecase. This video is mainly focussed on how to connect Streamlit with Google Sheets
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??
@CodingIsFun
3 ай бұрын
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 ✌️
@user-nm2qt5cw7o
2 ай бұрын
@@CodingIsFun Thanks!
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)
@CodingIsFun
11 ай бұрын
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!
@lorenz8347
11 ай бұрын
Danke dir für deinen Content, du bist echt schnell mit dem Antworten :D. Keep up the good work!@@CodingIsFun
Is there an advantage of using google sheets vs Deta for example?
@CodingIsFun
7 ай бұрын
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 😉)
Your website pythonandvba looks nice. How you build it, is it by streamlit or flask or ...?
@CodingIsFun
11 ай бұрын
Thanks. I created that website using WordPress
Woww! I am wondering if streamlit also support online Excel, Dataverse or Azure SQL?
@CodingIsFun
9 ай бұрын
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!
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.
@CodingIsFun
11 ай бұрын
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!
@InsideAlps
5 ай бұрын
@@CodingIsFun Great, that works!
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
@user-kq7tk1iq6u
11 ай бұрын
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.
@user-kq7tk1iq6u
11 ай бұрын
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.
@CodingIsFun
11 ай бұрын
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!
Thank you. Where can i learn sql
@CodingIsFun
11 ай бұрын
You are welcome. You can find many free SQL tutorials here on KZread. Happy Learning! 🤓
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
@CodingIsFun
6 ай бұрын
Thanks for watching and for your question. Hard to tell from a distance why you are facing an error. Sorry that I cannot help.
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 ?
@CodingIsFun
10 ай бұрын
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.
@ordaflash
10 ай бұрын
@@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.
how can i move to a new row, its always updating the first row
@CodingIsFun
6 ай бұрын
Please check out the example I put on GitHub. Cheers, Sven
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
@CodingIsFun
7 ай бұрын
Hey brother. Hard to tell from a distance why you are facing an issue. Cheers, Sven
@atangbingana283
7 ай бұрын
@@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
You are God 🙏🙇♂️🙇♂️ I just needed to know this and you did it
@CodingIsFun
11 ай бұрын
Glad I could help! Thanks for watching and for the kind words.
Hi i love your videos but could you add something about data analysis process o do something with data frol logística?
@CodingIsFun
11 ай бұрын
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: kzread.info/dash/bejne/hIx5spOLj720otY.htmlsi=QqH97mXS0jWo0gqw
@iotlalo
11 ай бұрын
@@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
can we replicate this project using microsoft 360 and one drive?
@CodingIsFun
24 күн бұрын
That should be possible, but I have not tried it. Feel free to give it a try. Happy Coding! Cheers, Sven ✌️
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?
@CodingIsFun
11 ай бұрын
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.
@AnungAriwibowo
11 ай бұрын
@@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.
Looks a bit lore complicated than easy hahah
@CodingIsFun
11 ай бұрын
Thanks for watching. I think setting up a database and connecting it to Streamlit might be a bit more of a hassle, though. 😅
@M_le-n4r
11 ай бұрын
@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!
Good Airtable replacement. Airtable just killed their free tier
@CodingIsFun
11 ай бұрын
Thanks for watching! 👍
ok spent 5 hours at this anyone else find an issue connecting a public google sheet? also the gid=0 on mine
@CodingIsFun
11 ай бұрын
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 ;)
After following all the steps there is an unknown error 404🥺,how can i Fix it
@CodingIsFun
2 ай бұрын
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.
@mebanpynshai1795
2 ай бұрын
Thank you so much for your reply, i was able to solve the problem,love your videos man.
@mebanpynshai1795
2 ай бұрын
@@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
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 🤩
@CodingIsFun
10 ай бұрын
Thanks for watching. I have shown you the CRUD operations in the video. :)
Its not working: This is what my URL looks like "...edit#gid=0"
@CodingIsFun
5 ай бұрын
And what exactly is not working then?
@ikesinachiajah8248
5 ай бұрын
@@CodingIsFun I am not using the spreadsheet you provided. I created mine and get this error "ValueError: Spreadsheet must be specified"
@ikesinachiajah8248
5 ай бұрын
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
@CodingIsFun
5 ай бұрын
@@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'
@mikekarim
10 ай бұрын
@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!
@edgartrejo689
7 ай бұрын
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
@CodingIsFun
4 ай бұрын
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. -------------------------------------------------------------------------------------------------------------------------------
@heback9
11 ай бұрын
I solved it by googling. --------------------------------------------------- import ssl ssl._create_default_https_context = ssl._create_unverified_context
@CodingIsFun
11 ай бұрын
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.