Using Google Sheets to create live updating charts in Python

An interesting and useful thing to know is how to connect Google Sheets in your Python notebooks. On top of that, another very interesting thing is to be able to use the Google Sheets to dynamically update your charts. In this article, we will learn to do both of them together.

Let's start by understanding how to connect Google Sheets with your Python notebook:

1. Visit the Google APIs Console
2. Click on '+ New Project' to create a new project
3. Click on the 'Enable API' button. Then, search for 'Google Drive API' in the search bar and enable it
4. Click on the Credentials section in the sidebar, then click 'Create Credentials' and finally click 'Service Account Key'. Here, a new dialog will appear. Keep the default JSON option selected. and click 'Create'
5. Move this JSON file to the same directory where your notebook exists
6. Open this JSON file and copy the email that looks like '<xxx>-compute@developer.gserviceaccount.com' and share your Google Sheet with this email. You'll get a failed delivery email, but don't worry about it

The difficult part is now over! Let get to the Python notebook.

Import the following libraries:

import gspread 
import pygsheets 
from oauth2client.service_account import ServiceAccountCredentials

*NOTE: you might get an error saying that these libraries don't exist in your environment. In that case, use the following commands to install these libraries:

pip install gspread 
pip install pygsheets

Now, we will import 'pandas' and 'matplotlib (pyplot and animation)'

%matplotlib notebook 
import matplotlib.pyplot as plt 
import matplotlib.animation as animation 
import pandas as pd 
#this is to add style to my chart 
style.use('fivethirtyeight') 

After that, we will now authorize our notebook to access the Google Sheet using the JSON file that we downloaded and stored in the current directory

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
gc = pygsheets.authorize(service_file='/users/pooja/downloads/metal-hope-142010-a2c9214abd86.json')

Now, the following function basically creates a bar plot using the 2 columns in the Google Sheet i.e. 'Id' and 'Count'.

animate() function reads the GoogleSheet named 'TestTest' and then creates a bar plot

animation.FuncAnimation() takes the following parameters:

1. the figure which we are going to plot in
2. the function that is going to plot the chart
3. the interval at which the figure should be refreshed

fig = plt.figure() 
ax = fig.add_subplot(1,1,1) 

def animate(i): 
    wks = gc.open("TestTest").sheet1 
    data = wks.get_values(start=(1,1), end=(20,2))
    headers = data.pop(0) 
    df = pd.DataFrame(data, columns=headers) 
    df.Id = df.Id.astype('int') 
    df.Count = df.Count.astype('int') 
    ids = df.Id 
    counts = df.Count 
    ax.clear() 
    ax.bar(ids,counts) 

ani = animation.FuncAnimation(fig, animate, interval=1000) 
plt.show()

An important thing to note here is that, after every 1000ms, the old chart is cleared using ax.clear() and then a new chart is created. So it looks like an animation, but in reality, we are just creating a new chart every 1 second.

Following is a video showing a live update on the graph when I change data in the Google Sheet!

VIDEO LINK

I'm still struggling to use Google Colab notebook to be able to update the charts there. The one I did was in my local Jupyter notebook and it works fine there. I'd appreciate if anyone has any clue about it.

Cheers!