Reading values from a Google Sheet, plotting with matplotlib and displaying with the camera component
Objective (what)
The main objective is to visualize in the HA dashboard the data from a Google spreadsheet.
Disclaimer: I’m a computer hobbyist, self-learner, and with proficiency in copy-paste tool. What I describe here has worked for me but YMMV. As you may have guessed, my mother language is not English so sorry for any vocabulary inaccuracy, spelling mistakes, and so on.
Introduction (why)
These last days I was fully satisfied with the solution proposed by @herrkarlson and described here, where you can embed a plot created in a Google sheet in HA dashboard using the Camera component. However, I found that there are limits in the number of rows you can plot. In my case, I could only make a plot up to 1100 rows, which is not enough in my situation. So what I’ll explain in this post is the continuation of the process I described here
Methodology (how)
I’m still happy saving the information of my relevant sensors in a Google spreadsheet for the reasons detailed in my previous post. What I need to visualize this information in the HA dashboard are the following steps:
- Authentication: the process is similar to what I already detailed in my previous post.
- Retreive the data: I use the python package gspread, which you should previously install.
- Extract the data into a pandas dataframe, so again you need to install the python pandas package.
- Make the plot using the wonderful plotting library matplotlib. Thanks, John Hunter!
- Save the figure in a local file
- Display the file using the Camera component
I made a python script that I attach below. It is self-explanatory and you will have to change some values to suit your situation. I saved this script in /home/homeassistant/.homeassistant/includes/shell_scripts/gsheets_read_and_plot.py:
from googleapiclient import discovery
import httplib2
import os
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage
import datetime
import gspread
import pandas as pd
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'
def get_credentials():
"""Gets valid user credentials from storage.
If nothing has been stored, or if the stored credentials are invalid,
the OAuth2 flow is completed to obtain the new credentials.
Returns:
Credentials, the obtained credential.
"""
home_dir = os.path.expanduser('~')
credential_dir = os.path.join(home_dir, '.credentials')
if not os.path.exists(credential_dir):
os.makedirs(credential_dir)
credential_path = os.path.join(credential_dir,
'sheets.googleapis.com-python-quickstart.json')
store = Storage(credential_path)
credentials = store.get()
if not credentials or credentials.invalid:
flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
flow.user_agent = APPLICATION_NAME
if flags:
credentials = tools.run_flow(flow, store, flags)
else: # Needed only for compatibility with Python 2.6
credentials = tools.run(flow, store)
print('Storing credentials to ' + credential_path)
return credentials
credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
'version=v4')
service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=discoveryUrl)
gc = gspread.authorize(credentials)
# Open up the workbook based on the spreadsheet name
SPREADSHEET = "the_name_of_your_spreadsheet"
workbook = gc.open(SPREADSHEET)
# Get the first sheet
sheet = workbook.sheet1
# Extract all data into a dataframe
res = pd.DataFrame(sheet.get_all_records())
datelist = res.Time.tolist()
fmt = '%Y-%m-%d %H:%M:%S' # check the time format of the timestamp in the first column
x_axis = [datetime.datetime.strptime(dat, fmt) for dat in datelist]
vary1 = 'T outside [ºC]' # name of the column you want to plot, typically written in the first row
vary2 = 'T inside [ºC]'
plt.style.use('ggplot')
xFmt = mdates.DateFormatter('%d-%m')
fig = plt.figure()
ax = fig.add_subplot(111)
ax.plot(x_axis,res[vary1],'b', label=vary1)
ax.fill_between(x_axis, 0, res[vary1], facecolor='blue', alpha=0.25)
ax.plot(x_axis,res[vary2],'r', label=vary2)
ax.fill_between(x_axis, 0, res[vary2], facecolor='red', alpha=0.25)
ax.xaxis.set_major_formatter(xFmt)
fig.autofmt_xdate()
ax.legend(loc=2)
fig.savefig('/tmp/temperatures.png') # file name in your local system
I create the Shell Command file in /home/homeassistant/.homeassistant/includes/shell_commands/update_plots.yaml containing:
update_plots:
python /home/homeassistant/.homeassistant/includes/shell_scripts/gsheets_read_and_plot.py
Once a day (at 23:51) I execute the previous python script. This is automated in this file /home/homeassistant/.homeassistant/includes/automations/update_plots.yaml containing:
- alias: 'Update plots from GSheets'
trigger:
platform: time
after: '23:51:00'
action:
- service: shell_command.update_plots
The images can be displayed in the frontend using the Camera component. This is what I have in the /home/homeassistant/.homeassistant/includes/cameras/gsheets_plots.yaml file:
- platform: local_file
name: "Temperatures"
file_path: /tmp/temperatures.png
- platform: local_file
name: "Energy consumption"
file_path: /tmp/energytv.png
Finally, my configuration.yaml includes these lines:
automation: !include_dir_merge_list includes/automations
shell_command: !include_dir_merge_named includes/shell_commands
camera: !include_dir_merge_list includes/cameras
Results
Main results of the described process is having plots displayed in the HA dashboard. An example of this is shown is the next screenshot:
Conclusions
- I’ve managed to read values from a Google sheet and display them as a plot in the dashboard executing a python script with the use of python packages: gspread, pandas, and matplotlib.
- The authentication part of the process is still a bit obscure for me.
- With the described process the maximum row limit in the Google spreadsheet charts is overcome.
Final words
If I haven’t been clear enough, please ask. I’ll try to do my best to help.
It is very likely that this can be accomplished in a simpler way (remember the disclaimer). Anyway I enjoyed the travel and I’m ready to follow your hints (if any) and learn. Thanks for reading!
