DEV Community

Cover image for Effortlessly Link Streamlit to Google Sheets for Real-time Analysis
Akan
Akan

Posted on

Effortlessly Link Streamlit to Google Sheets for Real-time Analysis

#streamlit #googlesheet #tutorial #lifecycle

As a researcher one major headache is the cost of data gathering, simple solutions can be quite manual or very expensive, however we can create a solution that solves this, that is both simple and free, using your free Google Account and Streamlit, a UI framework that “turns data scripts into shareable web apps in minutes all in pure Python, no front‑end experience required”. In short, with this tutorial we will build one together. As mentioned, we will need two things you Google Apps Sheet Keys and some knowledge of Streamlit. We will be creating a real-time interface between these two.

1 Set Up Google Credentials

Please read this or the Google Documentation on how to setup your Google Sheet data using the Python API.

2 Build a web-app with Streamlit

There are so many templates from Streamlit [insert st link] that we will just pick one and modify, please compliment what you learn with their beautiful documentation [Insert doc]. First things first - create a directory, where all our codes will live. In this dir., create a [inster secrets instruction] secrets.toml file, and store your secrets.

Now create a directory that contains app.py file that would have our code:

  1. Create a requirements.txt file and add the following: 
gspread numpy oauth2client pandas scikit_learn streamlit plotly
Enter fullscreen mode Exit fullscreen mode

Now, from your directory navigate to your terminal and run pip install -r requirements.txt to install these dependencies.

  1. Import our dependencies 
import streamlit as st import gspread from oauth2client.service_account import ServiceAccountCredentials
Enter fullscreen mode Exit fullscreen mode
  1. Retrieve your credentials 3.1. Create an .env file that will store your secrets

3.3. Retrieve your credentials, this includes your scope, and clients, like so.

## CREDENTIALS - Custom DEPENDENCIES CREDENTIALS = { "type": "service_account", "project_id": "<YOUR-PROJECT-ID>", "private_key_id": os.environ["PRIVATE_KEY_ID"], "private_key": os.environ["PRIVATE_KEY"], "client_email": os.environ["CLIENT_EMAIL"], "client_id": os.environ["CLIENT_ID"], "auth_url": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_x509_cert_url": os.environ["CLIENT_X509_CERT_URL"] } scope = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'] credentials = ServiceAccountCredentials.from_json_keyfile_dict(CREDENTIALS, scope) client = gspread.authorize(credentials)
Enter fullscreen mode Exit fullscreen mode

CREDENTIALS simply retrieves the secrets from your .env as well as confirm the json key file we downloaded, then client provided the neccessary authorization.

Remember, to change your to your actual Google Project ID.

  1. Create an Update Sheet function To connect to your Google sheet and insert a new row 
def update_sheet(client): sheet = client.open(<SHEETNAME>) sheet_insurance = sheet.get_worksheet(0) sheet_insurance.insert_rows(df.values.tolist())
Enter fullscreen mode Exit fullscreen mode

What this function does is, open with the , gets the first [0,1,...] sheet and insert new values into it.

# => Retrieving Values def get_value(val, my_dict): for key, value in my_dict.items(): if val == key: return value # => Retrieving Values II def get_fvalue(val): feature_dict = {"yes": 1, "no": 0} for key, value in feature_dict.items(): if val == key: return value
Enter fullscreen mode Exit fullscreen mode

A pre-set of dictionary assigned values for certain attributes.

### Dictionary/Labels sex_map = {'male': 1.0, 'female': 0.0} smoker_map = {'yes': 1.0, 'no': 0.0} region_map = {'southeast': 3.0, 'southwest': 4.0, 'northwest': 2.0, 'northeast': 1.0}
Enter fullscreen mode Exit fullscreen mode

These functions will now enable us to retrieve the assigned values into our form, which we build next.

  1. We will then a create a form like page, to retrieve basic demographic details, like so: 
## Page Title st.title('Real-time Monitoring Streamlit and Google Sheets') ## Attributes # Age age = st.sidebar.number_input("Age", 1, 100, 18, 1) # Sex sex = st.sidebar.radio("Sex", tuple(sex_map.keys())) # Region region = st.sidebar.radio("Region", tuple(region_map.keys())) # Body Mass Index bmi = st.sidebar.number_input("Body Mass Index", 10.00, 100.00, 15.96, 0.10) # Children children = st.sidebar.number_input("Number of children", min_value=0, value=1, step=1) # Smoker smoker = st.sidebar.radio("Do you Smoke?",tuple(smoker_map.keys())) # Time entry_date = datetime.now().strftime("%d-%m-%Y") entry_dir = 'contrib' + '_' + entry_date
Enter fullscreen mode Exit fullscreen mode

Now, after we have entered these values, the following will collect all these features and wrangle them into a simple array, like so:

## Feature Values feature_values = [age, get_value(sex, sex_map), bmi, children, get_fvalue(smoker), get_value(region, region_map)] ### Collect pretty_results = {'age': [age], 'sex': [sex], 'bmi': [bmi], 'children': [children], 'smoker': [smoker], 'region': [region]} data_entry_table = pd.DataFrame(pretty_results) ## Google Sheet Version df = data_entry_table.copy() df['entry_date'] = entry_dir ## Update the Google Sheet update_sheet(client)
Enter fullscreen mode Exit fullscreen mode

The feature_values list simply uses the initial get_(f)value functions to retrieve assigned values, pretty_results simplies beautifies by converting into a dictionary.

st.caption("Data Entry Table") st.table(data_entry_table)
Enter fullscreen mode Exit fullscreen mode

Optional. To download a copy of the entered details, add the following to your code: 

if st.sidebar.checkbox("Save a Copy"): ## Download Copy with open(csv_path, "rb") as file: btn = st.download_button( label="Download a copy", data=file, file_name="my-contrib.csv", mime="application/octet-stream" ) st.write('Thanks for contributing!')
Enter fullscreen mode Exit fullscreen mode 
st.sidebar.write("Built with ❤️| AfrologicInsect")
Enter fullscreen mode Exit fullscreen mode

Phew! Now we are done!

Simply navigate from your directory, to launch your terminal and execute streamlit run app.py [Insert Image] it's 🔥 but it gets better. There are so many possible applications for this, especially Lifecycles - Survey/Data Gathering, Customer Churn, Quantitative Analysis, etc.

3 That's it!

Create a Github Repository, if you do not have one, follow the instructions to push to an online repository.
Push your code repository to GitHub and deploy your application, remember to share.

While you think of what to build we this, the next tutorial would show to link this with Dash, a low-code framework for rapidly building data apps in Python, to create dashboards you would be proud of.

Sample Application:
Medical Charges

Top comments (0)