Google Sheets can be used as a powerful tool to store data, analyze it, and provide meaningful organizational insights. It has commensurate capabilities to Excel but has the benefit of being able to much more easily share a sheet over the cloud inside or outside of your organization. Google Sheets allows everyone to see real-time updates.
However, sharing access to a Google Sheet can cause difficulty as you may want to limit editing access to a particular section within a sheet while still allowing individuals to be able to read all the data. Google Sheets does allow a sheet to be in "Read-Only" mode, but this prohibits them from adding any information at all. Additionally data validation can be critical. If information needs to be formatted in a particular way to be analyzed by a Google Sheets formula or Python script, you need to be sure that information is entered correctly.
To tackle the above issues and many others in a similar vein, creating a simple web application to read from and write to a Google Sheet can be a powerful organizational tool.
Some key features this web application could allow include:
Only allowing users to input information to a designated section of the Google Sheet
Formatting the information a user submits before adding it to the Google Sheet
Analyzing data from a Google Sheet and displaying pertinent insights that allows the user to interact with data. For example, creating an application that allows a user to upload information (Excel document, information in another Google Sheet, SQL data, etc.) and create different visuals from the data that by be desired variables (month, product, etc.)
Allowing users to input information into a Google Sheet without seeing what other information users are adding
Add an additional layer of security by password protecting access to the application that is connected to the Google Sheet
To build the web application, we are going to use Streamlit- a very straight forward and adaptable Python application framework that easily allows users to read and write to a Google Sheet in a customized format. Though primarily geared toward data science applications, Streamlit can be used to build various types of data applications - here is a link to some awesome apps created by the Streamlit community.
Overview: This is a guide on how to read and write to a Google Sheet to allow users to view and edit only the data you want them to using Streamlit and Google Sheets API.
Road Map For Streamlit App with Private Google Sheet
We will go into more detail for each step, but here is an overview of the process:
If you have a Google account, click this link to go to the Google Cloud platform to create a project and enable the Google Sheets API. Google Cloud provides you with the necessary infrastructure and tools to manage your project, handle authentication securely, monitor usage, and scale your application as needed. If you do not have a Google account, you need to create one first to use the Google Sheets API then follow the previous link.
Create a Service Account (definition -"an identity that an instance or application can use to run API requests on your behalf") and generate your authentication key to securely allow your app to communicate with your Google Sheets.
Share your Google Sheet with the Service Account email.
Create your repository with your file structure similar to the one below and paste your service account key in a .toml file within your secrets folder.
Write your Streamlit app, test it, and deploy on your local network. By doing this, only those who are connected to the same network can access the app.
If you want to deploy it so it can be accessed with a url from anywhere, these are the steps to host your app on Streamlit's community cloud for free and here is how to safely transfer your secrets to the web application without having to deploy it to the Github repo (additional resource for adding API secrets to web app.)
Note: Streamlit has an awesome guide on how to connect a Streamlit app to a private Google Sheet here. Their guide deals only with viewing data and not reading/writing to a sheet and it does not walk through how to create a Google Cloud project. There are a several steps that are the same and it can be a helpful resource in addition to this guide.
Note: If the Google Sheet is public here is Streamlit's guide on how to connect them.
Step-by-step guide using an example
To illustrate how to read and write to a Google Sheet in a Streamlit App, I have created an app for my community group at church. We have a weekly potluck, so I made a multipage Google Sheet that has the items needed to bring that week. In the Streamlit app, they can view all the items that people are bringing for a particular week and they are only able to sign up for the items still needed.
Here's what the app looks like:
Let's walk through each step:
Create a Google Cloud Project:
To create a new project, follow the below steps from the Google Cloud platform guide:
Click this link to go to the Manage Resources page in the Google Cloud console. The remaining steps will appear automatically in the Google Cloud console.
On the Select Organization drop-down list at the top of the page, select the organization resource in which you want to create a project. If you are a free trial user, skip this step, as this list does not appear
Click Create Project.
In the New Project window that appears, enter a project name and select a billing account as applicable. A project name can contain only letters, numbers, single quotes, hyphens, spaces, or exclamation points, and must be between 4 and 30 characters.
Enter the parent organization or folder resource in the Location box. That resource will be the hierarchical parent of the new project. If No organization is an option, you can select it to create your new project as the top level of its own resource hierarchy.
When you're finished entering new project details, click Create.
Enable Google Sheets API:
Search "google sheets API" in Google Cloud and click "enable".
Create Service Account:
Go to the "APIs & Services" tab on the left-hand side followed by the "Credentials" section, click "Create Credentials" on the top bar, and fill out initial fields for the Service Account. You do not need to fill out any of the Optional sections.
This is the email address that will be used in the next section so be sure to save it. Save it then click on it to move to next step.
Generate API Key and Save JSON File:
Go to the "APIs & Services" tab on the left-hand side, click "Keys" on the top ribbon, and create a new JSON key.
Connect Google Sheet with Service Account Email:
Go to the Google Sheet that is going to be used by the application and click on the "Share" button on the top right. Paste the service account email.
Create Your Repository and Save Your Key Json file
Your JSON key should be saved in this format (from Streamlit guide):
Make sure to add this file to .gitignore so you don't commit it to GitHub!
Following this file structure (also from Streamlit guide):
Create Your Streamlit App
Finally, you get to code! If you haven't worked with Streamlit before, here is a link that shows you how to download the Python package and get familiar with the fundamentals.
Note: streamlit>=1.28 and st-gsheets-connection are needed in your Python environment
Reading from a Google Sheet
Simple use case: to connect to your Google Sheet and display the first 2 columns and first 10 rows:
import pandas as pd
import streamlit as st
from streamlit_gsheets import GSheetsConnection
conn = st.connection("gsheets", type=GSheetsConnection)
df = conn.read(worksheet="2", usecols=[0, 1], nrows = 10)
st.dataframe(new_df, height=500, width=500)Complex use case: this is what the Google Sheet that we're working with looks like:
On my app, I display what food items are available to sign up for as well as what the theme is for the next week. To do this, I read in 2 dataframes from different sections of the sheet:
#Connect to Google Sheet
conn = st.connection("gsheets", type=GSheetsConnection)
# Read in first two columns ignoring the header row
food_name_df = conn.read(worksheet="3", usecols=[0, 1], header=1)
# Get the length of the Food column that contains values
filtered_column_length = len(food_name_df['Food/Drink Item'].loc[food_name_df['Food/Drink Item'].notna() & (food_name_df['Food/Drink Item'] != '')])
# Clean df to only have rows with data
food_name_df_cleaned = food_name_df.iloc[:filtered_column_length]
# Read in next week's meal in column E
next_week_meal_df = conn.read(worksheet = "3",usecols=[4], nrows=1)
next_week_meal = next_week_meal_df.iloc[0, 0]To write to the Streamlit app, I can easily display the cleaned data frame and meal next week:
st.write("For those who meal plan, next week's theme is: ", next_week_meal)
st.write("")
st.subheader("Food/Drink Items Still Needed")
# Display the "Food/Drink Item" and "Name" columns
st.dataframe(food_name_df_cleaned , height=500, width=500)
st.write("")Writing to a Blank Google Sheet
Simple use case:Â For this example, we are writing to a Google Sheet that does not contain any data. If it did contain data, this code would write over it so be careful to check if there is any information that could be deleted. How to write to a Google Sheet that already contains information is in the next section.
Writing to an empty Google Sheet is simple - just make sure your data is formatted in either a dataframe, list, dictionary, or numpy array. After connecting to the spreadsheet specified in the secrets file, the below code takes the dataframe "df" and writes its contents to the worksheet named "worksheet_name":
import pandas as pd
import streamlit as st
from streamlit_gsheets import GSheetsConnection
# Connect to your Google Sheet
conn = st.connection("gsheets", type=GSheetsConnection)
# Create a sample Pandas DataFrame
df = pd.DataFrame({'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 60, 45],
'City': ['New York', 'Los Angeles', 'Chicago']})
# Update GS using sample df
df = conn.update(
worksheet="worksheet_name",
data=df,
)Writing to a Google Sheet with data and maintaining the same format
Complex use case: Updating Google Sheets that already have data in it usually require reading in the current data, editing the respective sections, and writing back out to the sheet. Depending on how your Google Sheet is organized, there can be difficulty writing out the data while maintaining the desired format. For my app, I intentionally organized the data in a way to introduce difficulty in order to demonstrate one approach to handling a few potential issues. However, the beauty of programming is you can always tackle issues in different ways so this might not be the best solution for your use case.
The flow to write to a Google Sheet that contains multiple disparate sections while maintaining its current format:
Read in the entire sheet as a data frame
Being careful - edit just the section of the dataframe of interest to change. This may require understanding column names may be different if there are headers and that rewriting a dataframe back to a Google Sheet will label all empty column headers as "unnamed".
Write the entire sheet dataframe back to Google Sheets
Unfortunately, the conn.update method doesn't allow writing to a specified cell location in a Google Sheet. Therefore, depending on your use case, there will need to be careful attention to detail to ensure you are writing data to the correct location in the entire sheet dataframe. Making sure your sheet is easy to convert to a dataframe before begining to connect it to a Streamlit app will save you a lot of time (again, I obviously did not do this for the sake of the example).
The code below reads in the entire Sheet and edits a value in one column based on the value in the adjacent column. The values are in columns that have a header - because I want to maintain the header when I write back the data frame back to the Google Sheet, I do not call the columns by name but keep them as variables and edit the column of interest based on its number in the sheet:
import pandas as pd
import streamlit as st
from streamlit_gsheets import GSheetsConnection
# Connect to your Google Sheet
conn = st.connection("gsheets", type=GSheetsConnection)
# Read in the whole sheet as a dataframe
sheet_df = conn.read(worksheet="3")
# Convert all column names to a list to be able to use without referencing # their specific name
column_names = sheet_df.columns.tolist()
# Item in column 1
item = 'Drink '
# Value in column 2 to add in the same row as item
name = 'Kenzie'
# Edit the value in column 2 based based on the value in column 1
# Note - 0 indexing
sheet_df.loc[sheet_df[column_names[0]] == item, column_names[1]] =name
# Write the entire sheet back to Google Sheets
sheet_df = conn.update(
worksheet="33",
data=sheet_df,
)To explore additional ways to have Google Sheets and Streamlit work together, here is one more guide that explores applications and is written by Streamlit.
We love helping organizations visualize, organize, and effectively leverage their data. If you have any questions or need help with a project, send us a message or click the button on the top of the page to setup time with us directly.