Create a Google Sheets Data Entry Form with Python & Streamlit
Introduction
I’m excited 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’ve already set up a spreadsheet for tracking vendor data, and I’ll guide you through integrating it into a custom web application.
This web app will feature various input fields, including select boxes, multi-select boxes, sliders, date inputs, and standard text fields. The best part? Once you hit the submit button, the data goes straight into your spreadsheet. By the end, you’ll not only have the input form running, but I’ll also provide code to enhance your web application.
Connect Google Sheets to Streamlit
To get started, we need access to our Google spreadsheet in Streamlit. For this, we will use a specific Python package. First, install the package by copying the command from the GitHub repository and running it in your terminal or command prompt. If you haven’t installed Streamlit yet, you’ll need to do that as well.
Next, head over to the Google Developers Console to create a new project. After creating the project, you’ll need to activate the Google Drive and Google Sheets APIs. Once that’s done, create a service account with editor rights to your spreadsheet.
To obtain the credentials for this user, navigate to the Keys section and create a new JSON key. Save this file as you’ll need it to connect your Streamlit app to Google Sheets.
Code out the data entry form
Now it’s time to create the actual web app. In your project folder, create a new Python file named streamlit_app.py. Start by importing the necessary libraries, including pandas, as you’ll need it for data manipulation.
Next, establish a connection to your Google Sheets. You can do this using the st.experimental_connection method. Once connected, fetch existing data from your worksheet using the read method.
Now let’s create the data entry form. This form will include various input fields that correspond to the columns in your spreadsheet. Ensure to include mandatory fields like business type and company names, and add a submit button at the end.
Deploy the app
With the form ready, it’s time to deploy the app online. Create a requirements.txt file to list all the necessary libraries. It’s crucial not to push your credentials to GitHub, so set up a .gitignore file to exclude your secrets file.
Once everything is set, push your code to GitHub. Next, go to the Streamlit Community Cloud, sign in, and click on “New App”. Select your GitHub repository and deploy the app. If you encounter errors, don’t worry; you just need to input the contents of your secrets file in the app’s settings under the Secrets tab.
Enhance the app with more features
Now that the basic entry form is up and running, you can enhance the app with additional functionalities. For example, you can include options to update existing entries or delete them. You might also consider securing your web app to prevent random data submissions by using an authenticator package that adds a login screen.
Outro
In this tutorial, I’ve shown you how to create an online data entry form for Google Sheets using Streamlit. We covered connecting Google Sheets, coding the data entry form, deploying the app, and enhancing it with more features. If you’re interested in more advanced functionalities or security options, let me know in the comments!
