Create a Google Sheets Data Entry Form with Python & Streamlit | Quick & Easy Tutorial
HTML-код
- Опубликовано: 2 авг 2024
- 👉 Explore All My Excel Solutions: pythonandvba.com/solutions
𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
In this project, I demonstrate how to design a data entry form that integrates seamlessly with Google Sheets using Streamlit and the streamlit-gsheets-connection library.
🌍 𝗟𝗜𝗡𝗞𝗦:
▶ Live demo: vendor-data-entry.streamlit.app/
▶ Source Code: github.com/Sven-Bo/google-she...
▶ Gsheets-connection package: github.com/streamlit/gsheets-...
⭐ 𝗧𝗜𝗠𝗘𝗦𝗧𝗔𝗠𝗣𝗦:
0:00 - Introduction
1:08 - Connect Google Sheets to Streamlit
5:19 - Code out the data entry form
11:00 - Deploy the app
13:32 - Enhance the app with more feature
14:24 -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
*Here are the links:* ⤵
Live demo: vendor-data-entry.streamlit.app/
Source Code: github.com/Sven-Bo/google-sheets-data-entry-form-with-streamlit
Gsheets-connection package: github.com/streamlit/gsheets-connection
Thank you for providing the neat demonstration, it helped me very much
Glad it helped! :)
That’s very practical, thank you!
Glad it was helpful, Florian. Thanks for watching and your comments! 👍
I was having difficulties because an error occurred in streamlit web distribution.
I was able to solve the problem by watching the video.
Among the streamlit content I am studying, your video is the best.
thank you.
Please continue to produce good streamlit content.
Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video! 👍
This was very helpful thank you!
Glad it was helpful! Thanks for watching and leaving a comment! :)
This video is a treasure. Thank you so much...
Glad to hear you liked it! Thank you for commenting and watching. Cheers, Sven ✌️
Great video as always. I would like the video about the login to see how you can control which user can see certain info!
Thanks for watching. Login (authentication) is already on 'videos-to-do' list! Stay tuned! ✌️
Excellent as always, a second video about login would be awesome!
As always, thanks a lot for your comments! I added the login feature on my "videos-to-do-list" ;)
I would be very interested also.
Great 👏👏
Thanks! 💖
Again an excellent informative video. Please make a 2nd video alsoa nd include also a login functionality.
Thanks for watching. Glad you liked this one too and your request is well noted! 👍
It is really amazing
Glad you liked it. Thanks for watching and taking the time to leave a comment! 👍
Superb
Thank you!🤗
Well explained, I would love a tutorial on the security bit
Thanks for watching. What exactly do you mean? Cheers, Sven ✌️
thank you, I really enjoyed learning this, please show me other exercises, like login session and others
Thanks for watching and your request is well noted! 👍
Thank you immensely for this invaluable content! Your effort is truly appreciated. 😊 Could you consider crafting another video demonstrating how to incorporate eye-catching statistics, just like Google does in their forms with pie charts, line graphs, or bar graphs? It'd be amazing to see this in action for data like gender ratios, age distribution, course preferences, and more. I'm curious if Streamlit alone can handle this or if you'd recommend building a separate admin dashboard. Could you share your insights on that process? Can't wait to see what's next! 👍
Thanks for watching and your suggestion! 👍
@@CodingIsFun As i'm walking on implementing something similar to this, I would want to know if you have done a video on this comment. I'm enjoying this so far
I always appreciate your content Sven. You would use this over google forms if the rest of your app is in streamlit correct?
Thank you for watching. I'm happy to hear that you like the content. I think it really depends on your use case. MS Forms is great and works right out of the box. However, if you need more customization options, the combination of Streamlit and Google Sheets could be another solution.
This is super interesting and I can already think of uses for something like this. Do you have any videos on pulling data from spreadsheets Excel or Google Sheets and using that data to fill in online forms... like reverse scraping?
great i will be using this in my next project wish i am working on now i needed a database for my collected info i think i can even add tableau to this.
Glad it was helpful and good luck with your project! 🤞
Thanks for the all the videos. Can you do a streamlit auth video with postgres?
Thank you for watching the videos! I will create another video to add authentication, but I cannot promise to use a PostgreSQL database for that. Nonetheless, the steps/code will be very similar.
Haha 😂 that woman diving into the water was so funny
🤿💦
Thanks very much for the video. Always enjoy and learn so much from your contents. Could you please integrate the authentication to protect this application?
Happy to hear that you enjoyed this one as well! Yes, in the coming weeks, I will create another video to show how to add the authentication.
Thank you for the lesson. Yeah now we need authentication and also how to let user update their own details.
Thanks for watching. Well noted! 👍
Love your contents, can you do a project similar but using sharepoint api to update list ?
Thanks for watching! I'm happy to hear that you are enjoying my videos, and thank you for your video suggestion. I can't make any promises, but your suggestion is well noted.
Thank you for your help, just a quick question, i am facing the following error: Spreadsheet must be specified
Hard to tell from a distance why you are facing an issue
can you please tell me what python and streamlit versions you are using in this video because i am facing a lot of compatibility issues between them and other libraries such as st-gsheets-connection
Também estou com o mesmo problema, instalei apenas o python e vs code para começar e esta dando erro. Estou usando atualmente o python na versão 3.12.3
Does this work if multiple users input data concurrently, or does it overwrite the google sheet with the last update?
Thanks for watching. Multiple users could use it at the same time.
Thanks for the video. Is there any limitation for using the Google API?
Thanks for watching. Have a look at the following Google Sheets rate limits: developers.google.com/sheets/api/limits
I hope it helps! :)
The Login screen would be super helpful, we'd need to keep the created login credentials somewhere else right?
Well noted! Yes, you would also need to set up a database to store/retrieve/update/delete the credentials.
can streamlit makes an option to choose time? i want to have a dropdown to select a data (can be from live or just from csv) ie Week over Week (such as last 7 days), or month over month (previous 30 days), or even a quarter (previous 90 days)? seems like i cant find the option to do so, as a dropdown
Thank you for watching! Unfortunately, the question you asked is not related to the video content. If you have any questions or need help, I suggest you join my Discord channel at pythonandvba.com/discord . Just make sure to read the forum rules before posting any questions.
Hey, thanks for the video. What color theme are you using on VSCode? Thanks.
Thanks for watching. I am using Cobalt3: marketplace.visualstudio.com/items?itemName=alex-pattison.theme-cobalt3
Cheers, Sven ✌️
@@CodingIsFun Thanks
This is great, but I have issue add new submission. It only update the previous submission instead of add in another new row.
And is it normal for the slow run?
Thank you for watching. Please check out my code:
github.com/Sven-Bo/google-sheets-data-entry-form-with-streamlit/blob/main/vendor_app.py
You will need to append the new data to your dataframe. I hope it helps! Happy coding! Cheers, Sven ✌️
Good. But what are benefits or advantages? Instead of entering data directly into gsheet?
Thanks for watching. It just an (practical) on how to connect Streamlit with Google Sheets.
how do you retrieve the data from the spreadsheet to the streamlit?
Thanks for watching. Feel free to check out the code I provided on GitHub. Happy Coding!
Does Streamlit connect to postgresql for a CRUD ui?
Thanks for watching. Yes, it does. Have a look here: docs.streamlit.io/knowledge-base/tutorials/databases/postgresql
Dear Sven! I am still getting this error when i try to st.dataframe(existing_data)
I have done everything what you said in your last video, however still stuck with it. Please help me out
The error message would be helpful
@@CodingIsFun I have attached the screenshot on ds
@@CodingIsFun Traceback:
ValueError("Spreadsheet must be specified")
Is it possible to delete a row? I am trying using the st.data_editor but is not working. Ty so much
Thanks for watching. Yes, that is possible. Have a look here: github.com/Sven-Bo/google-sheets-data-entry-form-with-streamlit/blob/main/vendor_app.py#L156
Happy Coding! Cheers, Sven ✌️
@@CodingIsFun ty so much!
i want to add image upload in this form can u help?
Did you get it working?
Hello, thank you for the video, I get the following errors when deploying my app:
streamlit.runtime.caching.storage.cache_storage_protocol.CacheStorageKeyNotFoundError: Key not found in mem cache, &
streamlit.runtime.caching.storage.cache_storage_protocol.CacheStorageKeyNotFoundError: Local disk cache storage is disabled (persist=None)
Thanks for watching and for your question. Hard to tell from a distance why you are facing an error. Sorry that I cannot help.
First comment 😊
🚀🚀🚀
Hello sir, i have the problem when using input worksheet as mine, and the problem is
"ValueError: Spreadsheet must be specified"
and my error in coding was
"existing_data = conn.read(worksheet="Data", usecols=list(range(6)), ttl=5)"
Do u know how to fix it?
Thanks a lot
Thanks for watching. Did you share the technical user email address in Google Sheets as shown in the video? Cheers, Sven ✌️
Hi, I got "No module named 'streamlit_gsheets'" error when running the app (7:01), I ran the app on my local computer and the st-gsheets-connection version is 0.0.4, can you help me to figure out the solution?
Thanks for watching and for your question. Hard to tell from a distance why you are facing an error. Sorry that I cannot help.
really would like to see how to make authentication with firebase.
Thanks for watching and your suggestion! 👍
Thanks a lot sir but i m encountering with an error
My app is working well when i locally host my app and it is even appending the data in my sheets but when i deploy it i m getting this as error
ModuleNotFoundError: This app has encountered an error. The original error message is redacted to prevent data leaks. Full error details have been recorded in the logs (if you're on Streamlit Cloud, click on 'Manage app' in the lower right of your app).
Traceback:
File "/home/adminuser/venv/lib/python3.9/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 534, in _run_script
exec(code, module.__dict__)
File "/mount/src/appointments/appointments.py", line 4, in
from streamlit_gsheets import GSheetsConnection
Thanks for watching. Have you included in the requirements.txt file in your GitHub repo?
Dear Sir, I cannot create .streamlit folder in my mac. so I got this error "ValueError: Spreadsheet must be specified"
Please have a look here: chat.openai.com/share/b587e0d4-aa17-43eb-acd7-ccc7278e98b0
I hope it helps! Happy Coding! Cheers, Sven ✌️
Dear Sven, thanks a lot for this video, this really helps for a project I need to implement.
when following your steps, I got an error when testing the dataframe with st.dataframe(existing_data) at 6min.40 in the video.
it returns the error "Spreadsheet must be specified" whereas you explicitly said it was not required.
Do you know what I'm doing wrong ?
thanks a lot
Julien
Check your secrets.toml if named correctly
Hi Julien. Thanks for watching. Kindly double check if you have set up the secrets.toml file correctly.
Hi I followed all
The output scren is showng
ValueError: Spreadsheet must be specified
Thanks for watching. Did you share your technical email address with the spreadsheet as shown in the video? Cheers, Sven ✌️
@@CodingIsFun my bad
I wrote the forlder name without ' . ' before streamlit
It's working fine now
I'm also developed a similar project. But instead of Google sheets, I've used MySQL database.
After successfully deploying my streamlit app in Community cloud, my app was not rendering. It was taking at least 15 min to render my web page. Could you please tell me a solution? 😢
Thanks for watching. Hard to tell from a distance. Sorry, that I cannot help!
Please let me know when you finally are able to deploy a streamlit app with mysql successfuly. I am having similar challenge
Hi! i follow your step. but, i found an error while installing gsheets-connection like this
note: This error originates from a subprocess, and is likely not a problem with pip.
ERROR: Failed building wheel for pandas
Failed to build pandas
ERROR: Could not build wheels for pandas, which is required to install pyproject.toml-based projects
I've already search on the internet to solve, but its still the same.
Thanks for watching and for your question. Hard to tell from a distance why you are facing an error. Sorry that I cannot help.
from streamlit_gsheets import GsheetsConnection Ln 2 Col 6
?
where is the entry_form file sheet ?
Thanks for watching. You can easily create the spreadsheet yourself. I just used it as an example. And since it is private (as shown in the video), you will not have permission to access the sheet. Happy Coding! Cheers, Sven ✌
Auto ML UI
Can i share it to whatsapp?
I don't know what you mean
not sure why but i keep getting this error!
ValueError: numpy.dtype size changed, may indicate binary incompatibility. Expected 96 from C header, got 88 from PyObject
and it points to "from streamlit_gsheets import GSheetsConnection"
Thanks for watching. Please clone the repo and try again. Cheers, Sven ✌️
data = worksheet.spreadsheet.values_get(
AttributeError: 'Worksheet' object has no attribute 'spreadsheet'
this the error i get, but this isnt even in my python script, it worked fine before for like a week and now suddenly i get this error.
Intersting. When I check my live demo app it still works as expected: vendor-data-entry.streamlit.app/