Create a Google Sheets Data Entry Form with Python & Streamlit | Quick & Easy Tutorial
- November 19, 2023
- Posted by: MainInstructor
- Category: BASIC Go JavaScript Python VSCode
![](https://i0.wp.com/allprowebdesigns.com/wp-content/uploads/2023/11/1700415895_maxresdefault.jpg?resize=840%2C430&ssl=1)
Video Title: Create a Google Sheets Data Entry Form with Python & Streamlit | Quick & Easy Tutorial
Today I’m going to show you how to create an online data input form for your Google spreadsheet using streamlit with streamlit you can build entire web applications in Python without needing to know HTML CSS or JavaScript I have already set up a spreadsheet for tracking vendor data and
I will guide you through integrating it in a custom web application this website will feature various input Fields including select boxes multi select boxes sliders date inputs and standard text fields and the best part once you hit the submit button the data goes straight into your spreadsheet we will
Go step by step to build this form using streamlet and I will also show you how to tailor the code to your needs not only that but we are also going to deploy our web application to the internet and if you want to check out
The web app I will leave the link in the description below by the end of this video you will not only have the input form up in run in but I will also give you the code to enhance your web application whether it’s to update an existing entry delete one or display
Play all entries all it takes to follow along is a basic understanding of python so without further Ado let us dive in and get started as a first step we need to get access to our Google spreadsheet and streamlet to do that we can use the following python package I will also
Leave the link to this GitHub repository in the description below the first thing you want to do is to install the package to do so copy the following command to your clipboard and open a terminal or command prompt paste the command in and press enter if you haven’t installed streamlet yet
You will also need to do that as well and in case you already have it installed I suggest updating it to the latest version you can do that by running pip install streamlet D- upgrade okay and with that in place head back to the GitHub repository here you will find
All the steps to connect Google Sheets with streamlet first head over to the Google developers console once there you will want to create a new project simply click up here and select new project name your project and then click on create after the project has been created make sure to select it by
Clicking on the button up here and then choosing your project great now in this project we will need to activate two apis Google Drive and Google cheats let’s start with the Google Drive API search for it and once you found it select it and then click the enable
Button okay and now repeat these steps by but this time for the Google Sheets API just search for it select it and hit the enable button again all right with that done we need to create a technical user who can then use those apis we just enabled in our project click on
Credentials and then choose create credentials from the drop- down list select service account and then give this service account a name I will name mine Google cheats python access since this technical use user should have weed and right access to your spreadsheet I will assign the
Editor R all right and with that we have set up our technical user now select that user by clicking on it we will need the credentials for this user to obtain them click on Keys then add Keys create new keys and choose Json before hitting the create button okay and with that let
Me quickly navigate to my downloads folder and copy this Json file to my clipboard all right now let’s see how we can use this technical user in our streamlet app first I will navigate to my project folder inside I will create a new folder and name it streamlet within this folder I will
Create a new file named secrets. toml and right next to this file I will paste the Json file we just downloaded okay great now let’s open this folder in vs code and position the two files side by side so now we have the mty sequence. terminal file and the
Json file next to each other with that in place return to the get repo and scroll down a bit and copy the provided snippet to your clipboard back in vs code I will paste this snippet into the sequence. TL file all right the next step is filling out
The details I will begin with type simply copy it from the Json and paste it into the tumml continue with project ID and so on and so forth once you have completed this your file should look like mine okay and as a Next Step copy the client
ID from the tumble file this is essentially the email address for our technical user and we now Grant this technical user access to our spreadsheet to do that go back to Google cheats in your spreadsheet click on share paste the email address and hit send okay and additionally I will also copy the
Spreadsheets link to my clipboard and paste it into the secrets. toml file the only field you do not need to edit is the worksheet you can just leave it as it is all right and after saving this file we can delete the downloaded Json file okay and with all those
Preparations complete we are now ready to create the actual web app in my root directory I will create a new python file and name it streamnet app py as I did earlier I will launch this directory in vs code and now it’s time to do some coding first I will import the necessary
Libraries I actually overlooked the fact that we also need pandas for our app if you haven’t installed it yet do so by running pip install pendas in your command prompt or terminal next I will configure the title and subtitle the interesting part begins now we will establish a connection to our spreadsheet
It’s as simple as writing st. experimental connection followed by the section name and the connection type with this we can utilize the connection object to fetch all existing data from our worksheet you can do this with the weat method and specifying the worksheet in my case I named my sheet vendors and
I will be using the first six columns from it by default the data retrieved via the read method will be cached for 60 minutes how however for our application I set the time to live so TTL to 5 Seconds okay and this line of code will return the spreadsheet data in
A penas data frame to ensure that we do not have any empty rows I will use drop and a on the data frame okay and before we go much further Let’s test things out to do so I will display our data frame using st. datf frame after saving the
File I will navigate back to my terminal and launch a streamnet app using streamnet 1 followed by the the python file name okay and now we can see the spreadsheet data within our app to verify let me quickly check the spreadsheets entries great so this seems
To be working as a Next Step let’s create the data entry form for that I will remove the data frame display and instead I will Define options for business types and products which I will be using later in my selection boxes and drop down lists so first let me type
Some business types and then also a list of of products with that in place let’s create the user form I will quickly code out the entire form and then I will explain it so within my form I have included different elements uh text input field selection box multi- select
Box slider date input field and a text area so Al together I have six input fields which are exactly also the numbers of columns in my spreadsheet you might notice that for the business type I have set index to none this means there is no default options so user must
Take a selection from the business types in the moment you will see exactly what I mean and both the business type and company names are essential Fields I have indicated this with an sck I have also included a markdown field at the bottom to explain the mandatory nature
Of these inputs lastly we have a submit button when that button gets clicked a simple user message will be displayed all right so now let’s see how our form looks like I will head over to my app and give it a refresh now I can see our form with the
Different selection options and currently if I’m just pressing the submit button it only displays a message that’s not exactly what we want but it’s a great start so in the next step let’s actually add this data to our spreadsheet before we do that I want to ensure that all the mandatary fields are
Filled out if either the company name or business type is missing I will display a warning message and stop the app next I also want to ensure there is no existing entry with the same company name in our spreadsheet remember our existing data is stored in a penas data
Frame using penas I can quickly check if the column company name matches the input from the company name field if it does I will display another warning and stop the app again okay and once I’ve passed those checks I will create a new WoW for the vendor data essentially I’m
Just constructing a new data frame using the submitted data and it’s important that the column names in this data frame are the same like in your spreadsheet and for the products multi selection I will convert the list into a string and separate each entry with a comma
Additionally I will also format the date into a string now with this new data frame prepared I will combine it with our existing data to do so I will use the pendas concatenate method inputting both our existing data and the new vendor data and I will name this new
Data frame updated DF okay and the next step is pretty straightforward we need to push this new data frame to our Google cheats we can do this by using our connection object and running the update method on it along with the updated data and once that is done I
Will show the user a success message all right and that’s basically it so before I actually submit new data let me first see if my checks are working when I hit the submit button it will be prompted to fill out all mandatory Fields okay so
This is already working and if I use a name that already exist and try to submit the data again I will get another warning so those checks seems to be working next I will actually submit a new valid entry after I have filled out all the fields and hit the submit button
Again we will get a success message and of course we can also validate this by going back to our spreadsheet all right and as you can see we now have our new entry here great now that this is up and running the next step is to deploy this
App online we need to let the server know which libraries it should install and we will do that using the requirements.txt file within this file you will list all the libraries necessary to run your app in my case it’s streamlit and the streamlit connection for Google cheats also it is
Super important not to push the credentials of your technical user to GitHub so therefore I’m going to set up a get ignore file in this file I will specify the path to my secrets. TL which is in my streamit folder all right and with that sorted let’s push now this
Code to GitHub and from there we can then deploy it later to the streate community cloud and to do so I will use vs code first I will initialize a new git repository in the project folder once that is done I can publish it to GitHub under the following name and make
It a public repository in this repository I will now commit and push my changes okay and moving on let’s deploy this GitHub repository to the streamnet community Cloud just Google streamnet Community Cloud sign in into your account and click on new app on the next screen select your GitHub repository the
Branch which is most likely main by default and the name of your python file optionally you could also modify the app URL if you want to after inputting those details you can click on the deploy button it might take a short time and when it’s finished you will most likely
Encounter an error message but don’t worry this is totally expected since we didn’t provide the login credentials for our technical user we can sort this out by heading to manage app and diving into the settings here you will find a Secrets tab this is where you will need
To drop the contents from our secrets. TL file so let me let me just hop back to my project folder grab everything from that file and then paste it in here great and with that done I will click on the save button it might take a moment but your app should reload automatically
And as we can see the error now disappeared now for our final test I’m going to input some data into our form and then hit the submit button so when I switch back to my spreadsheet we can now see our new entry here all right guys
And this is how you can create an online data entry form for Google cheats what we have covered is really just the basic entry form of course you can enhance this app with more functionalities just like I have done here for example I have included a selection box at the very top
Of the app in there you can either select to enter a new entry or to update an existing one and if you ever want to delete an entry that is also possible too if you are interested in the code it’s available in myetta repo and and if
You would like to have a separate video on this let me know in the comments below also it is probably a good idea to secure this web app so that not just anyone can inut random data into your spreadsheet a way to do this would be to use a streamed authenticator package
Which lets you add an logging screen for an additional layer of protection again if you’re interested in a tutorial on that let me know in the comments below okay and as always thanks for watching and I will see you in the next video
Video Keywords: Python, streamlit,google sheets,google sheets data entry,online data entry form,input form streamlit,gsheets input form,online data input form,python web app,python entry form,python online spreadsheet input form,python online entry form,python web tutorial,excel online input form,excel data entry form,excel data entry form streamlit,python excel data entry form,python excel data input
-
Sale!
Wireless WIFI Repeater Extender Amplifier Booster 300Mbps
$29.99$14.99 Add to cartWireless WIFI Repeater Extender Amplifier Booster 300Mbps
Categories: Electronics, Wi-Fi Router, Wireless Wi-Fi Extender Tags: 300Mbps, 802.11N, Amplifier, Booster, Extender, mobile wi-fi booster, Remote, WIFI, Wireless, Wireless WIFI, Wireless WIFI Repeater, Wireless WIFI Repeater Extender, Wireless WIFI Repeater Extender Amplifier, Wireless WIFI Repeater Extender Amplifier Booster, Wireless WIFI Repeater Extender Amplifier Booster 300Mbps$29.99$14.99 -
Sale!
Full RGB Light Design Gaming Headset Headphones with Mic
$24.99$14.99 Add to cartFull RGB Light Design Gaming Headset Headphones with Mic
Categories: Electronics, Gaming, Gaming Headsets Tags: Design, Full, Full RGB Light Design Gaming Headset, Full RGB Light Design Gaming Headset Headphones, Full RGB Light Design Gaming Headset Headphones with Mic, Gamer, Gaming, Gaming Headset Headphones, gaming headset wireless, Headphone, Headphones, Headset, Light, Mic, Package, RGB$24.99$14.99 -
Sale!
Wireless BlueTooth Multi-Device Keyboard Mouse Combo
$39.99$19.99 Add to cartWireless BlueTooth Multi-Device Keyboard Mouse Combo
Categories: Electronics, Gaming, Gaming Keyboards, Keyboard Mouse Combos Tags: Combo, Keyboard, keyboard mouse combos, Mouse, MultiDevice, Set, WireKeyboard Mouse Combo, Wireless, Wireless BlueTooth Keyboard Mouse Combo, Wireless BlueTooth Keyboard Mouse Combos, Wireless BlueTooth Multi-Device Keyboard Mouse Combo, Wireless BlueTooth Multi-Device Keyboard Mouse Combos$39.99$19.99 -
Sale!
High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
$199.99$139.99 Add to cartHigh Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
Categories: Gaming, Gaming Chairs Tags: Adjustable, Chair, computer chairs, Desk, Executive, Gaming, Girl, Headrest, High, High Back Leather Executive Adjustable Swivel Gaming Chair, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar, High Back Leather Executive Adjustable Swivel Gaming Chairs, Leather, Lumbar, Office, Racing, Swivel$199.99$139.99 -
Sale!
Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
$29.99$19.99 Select optionsProfessional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
SKU: N/A Categories: Electronics, Gaming, Gaming Headsets Tags: Cancelling, Gaming, Gaming Headphones with Noise Cancelling Microphone, gaming headset, Headphones, Headset, LED, Light, Mic, Microphone, Noise, Professional, Professional LED Light Wired Gaming Headphones, Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone, Wired, Wired Gaming Headphones, Wired Gaming Headphones with Noise Cancelling Microphone$29.99$19.99 -
Sale!
Gaming Desk with LED Lights USB Power Outlets and Charging Ports
$349.99$249.99 Select optionsGaming Desk with LED Lights USB Power Outlets and Charging Ports
SKU: N/A Categories: Computer Desk, Gaming, Gaming Desk Tags: and Charging Ports, Charging, Desk, Desks, Gaming, gaming desk with led lights, Gaming Desks with LED Lights, Home, LED, Lights, Monitor, Office, Outlets, Port, Power, Room, Stand, USB, USB Power Outlets, White, Workstation$349.99$249.99 -
Sale!
Wired Mixed Backlit Anti-Ghosting Gaming Keyboard
$99.99$79.99 Add to cartWired Mixed Backlit Anti-Ghosting Gaming Keyboard
Categories: Electronics, Gaming, Gaming Keyboards Tags: Antighosting, Backlit, Blue, brown, Gaming, Gaming Keyboard, gaming keyboards, gaming keyboards and mouse, Keyboard, Laptop, Switch, Wired, Wired Mixed Backlit Anti-Ghosting Gaming Keyboard, Wired Mixed Backlit Anti-Ghosting Gaming Keyboards, Wired Mixed Backlit Gaming Keyboard$99.99$79.99 -
Sale!
Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
$119.99$59.99 Add to cartWireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
Categories: Electronics, Gaming, Gaming Headsets Tags: 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, ANC, Audio, Bluetooth, Cancellation, Ear, Earphone, gaming headset, Headphones, Headset, Hi-Res Over the Ear Headphones Headset, HiRes, Noise, Wireless, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Headphones, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headsets$119.99$59.99 -
Sale!
Wired Sports Gaming Headset Earbuds with Microphone
$19.99$9.99 Select optionsWired Sports Gaming Headset Earbuds with Microphone
SKU: N/A Categories: Gaming, Gaming Headsets Tags: Accessories, Earbud, Earphone, Earphones, Gaming, gaming headset with microphone, Headphones, Headset, IOS, Microphone, Sports, Wired, Wired Sports Gaming Headset Earbuds, Wired Sports Gaming Headset Earbuds with Microphone, Wired Sports Headset Earbuds$19.99$9.99 -
Sale!
150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
$49.99$29.99 Add to cart150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
Categories: Charging Stations, Electronics Tags: 150W, 150W Charging Station, 150W Universal Multi USB Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Stations, 150W Universal Multi USB MAX Charging Station, 16 Port MAX Charging Station, 3.5A, Charger, Charging, Fast, laptop charging stations, Max, Multi, Port, Stand, Station, Universal, USB$49.99$29.99
Here are the links: ⤵
Live demo: https://vendor-data-entry.streamlit.app/
Source Code: https://github.com/Sven-Bo/google-sheets-data-entry-form-with-streamlit
Gsheets-connection package: https://github.com/streamlit/gsheets-connection
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
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.
Good. But what are benefits or advantages? Instead of entering data directly into gsheet?
how do you retrieve the data from the spreadsheet to the streamlit?
Thank you for providing the neat demonstration, it helped me very much
Thank you for this video!
thank you, I really enjoyed learning this, please show me other exercises, like login session and others
I dont think its a good idea to use Google Sheets, data is not getting refreshed as per new data. In your app, "Another Company" isnt showing after choosing 'view all company'. So dynamically, its not feasible to use Google Sheets.
I always appreciate your content Sven. You would use this over google forms if the rest of your app is in streamlit correct?
This was very helpful thank you!
Haha 😂 that woman diving into the water was so funny
really would like to see how to make authentication with firebase.
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
That’s very practical, thank you!
The Login screen would be super helpful, we'd need to keep the created login credentials somewhere else right?
Thank you for the lesson. Yeah now we need authentication and also how to let user update their own details.
Does this work if multiple users input data concurrently, or does it overwrite the google sheet with the last update?
Again an excellent informative video. Please make a 2nd video alsoa nd include also a login functionality.
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?
Thanks for the all the videos. Can you do a streamlit auth video with postgres?
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! 👍
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? 😢
Superb
Great 👏👏
First comment 😊