Streamlit & Google Sheets: The Easiest "Database"
Introduction
In this post, I will demonstrate how to connect your Streamlit app with Google Sheets. This guide will cover how to link it to both a publicly shared spreadsheet and a private one. Using Google Sheets as a database can be especially useful for small projects, allowing you to store and retrieve data without setting up a full-fledged database system. With just a few lines of code, you can create new worksheets, query data using SQL, update your data, or even clear entire sheets directly through Streamlit.
Connect to Public Spreadsheet
To get started, I recommend visiting the provided GitHub repository for the necessary package. First, you will need to install the package. Open your terminal or command prompt, copy the installation command from the repository, and execute it.
Now, let’s connect Streamlit to a publicly shared spreadsheet. This will give you read-only access. All you need to do is copy the example code into a new Python file, which I’ve named streamlit_app.py. Once you save this file, run the app using streamlit run streamlit_app.py.
When the app is live, it will display a dataframe. After importing the necessary package, provide the URL to your publicly shared spreadsheet. This allows you to establish a connection and read the content of the spreadsheet.
You can specify which columns to use by setting the indices or using a list. For example, if you want to select column A and column B, you can do that easily. However, do note that when connecting to a public sheet, you should use the worksheet ID found in the URL, which comes after “gid”.
After making the necessary adjustments in your code and refreshing the app, you will see the data from the specified sheet. Each sheet can be viewed as a table in a relational database, providing structure to your data.
Connect to Private Spreadsheet
While using public sheets is straightforward, connecting to a private sheet requires a bit more setup. Start by visiting the Google Developers Console to create a new project. Enable both the Google Drive and Google Sheets APIs for your project.
Next, create credentials for your project by setting up a service account. Assign a name and the role of ‘Editor’ to this service account, which allows it to read and modify data.
Once you’ve created the service account, download the JSON file containing your keys. This file is sensitive information, so be careful not to share it. Create a folder named .streamlit in your app’s root directory and generate a secrets.toml file within it.
Populate the secrets.toml file with the values from your JSON file, along with the link to your spreadsheet. Make sure to share the spreadsheet with the service account’s email address to grant it access.
Practical Example
Now that the setup is complete, you can manipulate your Google Sheets data like you would with a typical database. For demonstration, I created a function to generate a dataframe called ‘orders’. This allows me to create a new sheet, query data, update it, and even clear the sheet using buttons in the app.
For instance, when I click the button to generate a new sheet, it will create a new tab in the spreadsheet with the specified data. You can also run SQL queries against this data to perform calculations, such as summing total prices.
It’s important to note that the data connector has a default Time-To-Live (TTL) of 60 minutes, meaning data is cached for that duration. For testing purposes, I set the TTL to 5 seconds to see real-time changes.
Outro
In conclusion, while Google Sheets may not replace a full database for larger projects, it serves as a great alternative for smaller applications. By creating a well-structured spreadsheet, you can effectively interact with it using Streamlit, utilizing the power of SQL for data manipulation. Thank you for following along!
