Creating a Google Sheet as Your Web App Database using Streamlit Cloud
Google Spreadsheet | Streamlit Cloud | Database
Do you want to build a web app that requires a database but don’t want to deal with the hassle of setting up a dedicated database server? Look no further than Google Sheets! In this article, we’ll show you how to create a web app database using Google Sheets, and how to read and write to it directly from the web using Streamlit Cloud.
In an earlier discussion, we covered the use of Google Sheets as a database, albeit with the caveat that the CRUD (create, read, update, delete) functions were only performed directly on the Google Sheets and not through a web app. If this method is of interest to you, you can find more details in the following article:
But if you’re looking to perform CRUD functions directly from a web app, then keep reading.
Step 1: Getting a Google Key To use Google Sheets as your web app database,
These steps are already covered by the streamlit document in following this article Connect Streamlit to a private Google Sheet — Streamlit Docs. But, at the end of the documentation it uses gsheetsdb instead of gspread, which I found more difficult to implement.
you’ll need to get a Google API key. Follow these steps to get one:
- Go to the Google Cloud Console and create a new project.
- Navigate to the “APIs & Services” tab and enable the “Google Sheets API”
3. Create a service account with “Editor” permissions and download the private key file in JSON format.
4. Save the private key file to a secure location.
5. Share the Google Sheet with the service account
By default, the service account you just created cannot access your Google Sheets. To give it access, click on the Share button in the Google Sheet, add the email of the service account (noted down in step 2), and choose the correct permission (if you want to read the data, Viewer is enough):
Step2: Copy and paste that JSON file into streamlit cloud secret on apps · Streamlit
Step 2: Setting up the Python Environment We’ll be using the gspread library to interact with Google Sheets, and the google-auth library to authenticate with the Google API. Here's the code to set up the Python environment:
import streamlit as st
import pandas as pd #if you will
import gspread
from google.oauth2 import service_account# Create a connection object.
credentials = service_account.Credentials.from_service_account_info(
st.secrets["gcp_service_account"],
scopes=[
"https://www.googleapis.com/auth/spreadsheets","https://www.googleapis.com/auth/drive"
],
)
conn = connect(credentials=credentials)
client=gspread.authorize(credentials)make sure that you already pasted JSON file onto the streamlit secretst.secrets["gcp_service_account"]
Step 3: Reading from Google Sheets To read from a Google Sheet, we first need to get the sheet ID and convert it to a CSV file. Here’s the code to do that
sheet_id = 'YOUR_SHEET_ID'
csv_url = f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=csv"
database_df = pd.read_csv(csv_url, on_bad_lines='skip')Replace YOUR_SHEET_ID with the ID of your Google Sheets. You can find the ID in the URL of your Google Sheets.
Step 4: Writing to Google Sheets To write to a Google Sheet, we first need to convert our data to a list of lists, where the first list is the header and the rest of the lists are the data rows. Here’s the code to do that:
database_df = database_df.astype(str)
sheet_url = st.secrets["private_gsheets_url"] #this information should be included in streamlit secret
sheet = client.open_by_url(sheet_url).sheet1
sheet.update([database_df.columns.values.tolist()] + database_df.values.tolist())
st.success('Data has been written to Google Sheets')Step 5: Deploying to Streamlit Cloud Now that we’ve set up our Python code to read and write to a Google Sheet, we can deploy it to Streamlit Cloud. Simply create a requirements.txt file with the required libraries (gspread, google-auth, and any other libraries you need), and also the Py file on GitHub. Once all files required are already stored on GitHub. the rest is easy, you could follow this instruction: Deploy an app — Streamlit Docs. It basically requires you to sign in, and click the new app — from the existing repo — address to your GitHub repo-and build!
By following these steps, you can easily create a Google Sheet as your web app database and read and write to it directly from the web using Streamlit Cloud. This approach is especially useful for small-scale projects where a dedicated database server may be overkill. Keep in mind, however, that Google Sheets has its limitations as a database, such as the number of rows and columns it can handle, so it may not be suitable for larger projects. Nevertheless, it’s a quick and easy solution that can get your web app up and running in no time.
In this article, we’ve shown you the basic steps for setting up a Google Sheet as your web app database, but there are many more things you can do with it, such as querying the data, filtering, and sorting. With the gspread library, you can do pretty much anything you can do with a regular Google Sheet. So, give it a try, and see what you can do with Google Sheets as your web app database.
New to trading? Try crypto trading bots or copy trading on best crypto exchanges
Join Coinmonks Telegram Channel and Youtube Channel get daily Crypto News
