How to create a python web app to securely collect and store user information

Connect a Streamlit web app to a private Google sheet in 7 easy steps to create an interactive survey or dashboard

The excitement when psychology meets data science! Photo by Windows on Unsplash.

As a psychology researcher, I want to make surveys where participants can enter their private information. As a data scientist, I want to create great participant experiences with beautiful interactive visualizations and informative analyses. Unfortunately, these two worlds rarely overlap.

But recently, I discovered Streamlit, a tool that lets you use Python to create beautiful web applications. Streamlit has many widgets and forms for user input. And you can run all sorts of analyses and display custom visualizations.

The only thing is, to maintain participant privacy, I need to be able to store and retrieve private information securely. Solution: connect a private Google Sheet to a Streamlit app. Private Google Sheets seem to be a secure way to store participant data. And in my experience, many psychology labs use them.

With Streamlit and Google Sheets, you can easily and quickly build a web application to collect and store private information securely. Photo by Team Nocoloco on Unsplash.

You can connect to other databases like MongoDB (I’ll post a separate explainer for it), but Google Sheets seemed the easiest way to me at first.

But then I spent hours trying to figure out what I did wrong while following the official Streamlit tutorial. Turns out I did everything as instructed. The Streamlit tutorial is wrong. I couldn’t find any other tutorial that solved the problem. So here is a complete and simple guide to connecting Streamlit to a private Google sheet to make an interactive survey or dashboard.

The first five steps are similar to the official Streamlit tutorial (included here for completeness). Feel free to follow that one instead (it has screenshots). If you already have a Google sheet and a Google service account with a key, jump to Step 4.

Connecting Streamlit to a private Google sheet

Step 0: Create a Streamlit web app

This tutorial is for people who already know the basics of Streamlit. If you’re new to Streamlit, you can follow this simple guide to make your first app:

How to impress with your project: build a web app on Streamlit

I also created an example web app about how the US Republicans and Democrats view each other. Feel free to try it out. You can use the source code (with Google sheets or with MongoDB) as a template for your own app or to get inspired.

An example web app that collects user data. Screenshot by the author.

Step 1: Create a private Google sheet

• Go to sheets.google.com and log in if necessary.
• Click ‘New’ or the big plus at the top to create a new sheet.
• P.S. To verify that a sheet is private, hover over the ‘Share’ button in the top right of the sheet. It should say ‘Private to only me.’ You can learn more about Google Sheets here.

Step 2: Enable Google Sheets API

• Sign in or create an account on Google Cloud.
• Go to the API & Services dashboard. You may need to select a project or create a new one.
• Search for Google Sheets API and enable it.

Step 3: Create a service account and a key

• Go to Service Accounts.
• Press ‘Create Service Account’ and follow the instructions to create one. Grant it Viewer, Editor or Owner permissions, depending on what you want your app to do. A Viewer can access the data without changing it, an Editor and Owner can view and change the data.
• Important: Save the email address of the service account you just created.
• Press on the three dots under ‘Manage Actions’ for the account and select ‘Manage keys.’
• Press ‘Add key,’ ‘Create new key’ and select ‘JSON’ when prompted. Download the JSON file.

Step 4: Share the Google sheet with the service account

• Go to the Google sheet you want to use and share it with the email address of the service account. Give it the appropriate permissions.
• Copy the Google sheet’s shareable link and save it for the next step.

Step 5: Create secrets.toml and add the secrets

• Create or open .streamlit/secrets.toml on your local machine in the root directory of your app. Fill out the template below with your data using the sheets url and the data from the service account key file. Copy the resulting file.
• P.S. Don’t forget to add secrets.toml to .gitignore to keep your secrets private.
• P.P.S. To create .streamlit directory type mkdir .streamlit in your terminal while in the root directory of the app. Then type vim secrets.toml , click i on the keyboard, paste in your data. Click esc, type :wq and hit Enter to save and exit the file.

Template for .streamlit/secrets.toml

• Sign in to Streamlit.io. Click on the three dots next to the app you want to connect to the Google sheet.
• Press on ‘Settings,’ then ‘Secrets,’ and paste the contents of the secrets.toml file there. Save.

Step 6: Add shillelagh and google-auth to requirements

Here is where we deviate from the Streamlit documentation. The module that Streamlit suggests using is outdated and doesn’t support inserting or editing data. Instead, we’ll be using a newer version called shillelagh . We also need to add google-auth because, turns out, the developers of shillelagh forgot to.

• pip install shillelagh and pip install google-auth>=1.23.0,<2 in your terminal.
• Add shillelagh and google-auth>=1.23.0,<2 to your requirements.txt as below.

requirements.txt

Step 7: Connect to your app

• Add the code below to your app to connect to your private Google sheet.

Code to connect to your private Google sheet.

You’re done! Yay!

You can use the same service account with multiple Google sheets. Just share the sheet with the account and specify the correct sheet link.

Example Usage

Now you can query your data from the private Google sheet if you gave the service account Viewing permissions. If you gave it Editor or Owner permissions, you can also edit and insert into the sheet from your Streamlit app. Below are a few examples of how you can do it. If you want to see more examples, check out shillelagh’s documentation.

Create a data frame

This code will retrieve data from a private Google sheet and create a pandas data frame out of it. Don’t forget to add pandasto your requirements.txt and change the names of columns to match the Google sheet’s.

Turn a query into a pandas data frame.

Edit a value

This code will increment the value of a column by one. Change col1 to your column name.

Edit a column in a Google sheet.

Insert a new row

This code will insert a new row into a private Google sheet. Change col1, col2, col3 to your column names.

Add a new row to a private Google sheet.

Thank you for reading through this tutorial! I hope it was helpful. Please let me know if you have any questions or suggestions. You can leave a comment here or tweet at me @YaraKyrychenko.