Writting sensors’ values to Google Sheets
Objective (what)
I wanted to send the values of some sensors (every 15 minutes) to a Google sheet in order to visualize data for long periods of time (months, years?). The underlying (real) objective is to correlate my energy consumption (gas and electricity) with climate data (outside temperature) and home confort (living room temperature).
Introduction (why)
Let’s answer some questions first:
- Why not using the built-in HA database? I didn’t want to have a huge HA database file. Maybe it is possible to store long periods of time and purge the sensors I don’t need but I’m not familiar working with SQL queries.
- Why not storing the data from specific sensors in a separate file locally? I’m running HASS in a Raspberry Pi so I wanted to limit the number of writtings to the SD card.
- Why Google sheets?
3.1 I’m not very concern about the information I would send to Google services: just outside and living room temperatures, and energy consumption. In addition, I think I can properly control the privacy settings of the sheet.
3.2 I can use the plotting tools in the sheet and embed them in HA dashboard as an image
3.3 It seems other people is interested on doing this requested by @fversteegen , so after months being an active reader in this forum, maybe it is time to share my findings with the community.
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, and so on.
Methodology (how)
@robmarkcole pointed me into the right direction. Google Sheets API. Three days ago I did not even know what an API was. My first step was to read here. However, I arrived to a dead end and I couldn’t get it working using the HA RESTful Command component since I didn’t know how to manage the authentication process. So my next step was to read here. As stated in the link: Complete the steps described in the rest of this page, and in about five minutes you’ll have a simple Python command-line application that makes requests to the Google Sheets API. It sounds good. If I succeed I could use the HA Shell Command component.
Step 1.- I made a new python script using the two previous links: from the former I used the information to append values to the spreadsheet and from the latter I used the information to manage the authetication process. You may observe that I’m hardcoding the sensor value in the script sensor01 = 123. You have to change the spreadsheet_id and range_ to suit your situation. I saved this script in /home/homeassistant/gsheets_write_auth.py:
"""
BEFORE RUNNING:
---------------
1. If not already done, enable the Google Sheets API
and check the quota for your project at
https://console.developers.google.com/apis/api/sheets
2. Install the Python client library for Google APIs by running
`pip install --upgrade google-api-python-client`
"""
from pprint import pprint
from googleapiclient import discovery
import sys
import httplib2
import os
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage
try:
import argparse
flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
flags = None
# TODO: Change placeholder below to generate authentication credentials. See
# https://developers.google.com/sheets/quickstart/python#step_3_set_up_the_sample
#
# Authorize using one of the following scopes:
# 'https://www.googleapis.com/auth/drive'
# 'https://www.googleapis.com/auth/spreadsheets'
# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'
sensor01 = 123
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)
# The ID of the spreadsheet to update.
spreadsheet_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' # TODO: Update placeholder value.
# The A1 notation of a range to search for a logical table of data.
# Values will be appended after the last row of the table.
range_ = 'Sheet 1!A2:E' # TODO: Update placeholder value.
value_input_option = 'USER_ENTERED' # TODO: Update placeholder value.
value_range_body = {"values": [[sensor01]]}
request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, body=value_range_body)
response = request.execute()
# TODO: Change code below to process the `response` dict:
pprint(response)
Step 2.- Activate the virtual environment (if needed):
source /srv/homeassistant/homeassistant_venv/bin/activate
Step 3.- Install new python libraries
pip install --upgrade google-api-python-client
Step 4.- Run the file. Since I’m running HASS in a Raspberry Pi, I have to include “noauth_local_webserver” (explained in the next step):
python gsheets_write_auth.py --noauth_local_webserver
Step 5.- Copy-paste the provided link in the browser of the computer you are using to access the Pi and allow authentication rights.
So with these steps I would say that we have managed to give authentication rights to the application stated in APPLICATION_NAME. If you open your spreadsheet you should detect that 123 value is appended every time you run the script.
Step 6.- Now I needed to pass the sensor value as an argument to the script and remove the hardcoded value. In order to do this I also had to comment some lines out of the script (to be honest I have no idea the future implications of what I’m doing here). In addition, I also wanted to include the timestamp in the first column of the spreadsheet so I imported the datetime module and added some lines below sensor01 = sys.argv[1]. My final script is saved in /home/homeassistant/.homeassistant/includes/shell_scripts/gsheets_write.py:
"""
BEFORE RUNNING:
---------------
1. If not already done, enable the Google Sheets API
and check the quota for your project at
https://console.developers.google.com/apis/api/sheets
2. Install the Python client library for Google APIs by running
`pip install --upgrade google-api-python-client`
"""
from pprint import pprint
from googleapiclient import discovery
import sys
import httplib2
import os
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage
import datetime
# I have to comment out these 5 lines to allow passing the sensor information as argument
#try:
# import argparse
# flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
#except ImportError:
# flags = None
# TODO: Change placeholder below to generate authentication credentials. See
# https://developers.google.com/sheets/quickstart/python#step_3_set_up_the_sample
#
# Authorize using one of the following scopes:
# 'https://www.googleapis.com/auth/drive'
# 'https://www.googleapis.com/auth/spreadsheets'
# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'
sensor01 = sys.argv[1]
#sensor01 = 123
fmt = '%Y-%m-%d %H:%M:%S'
d = datetime.datetime.now()
d_string = d.strftime(fmt)
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)
# The ID of the spreadsheet to update.
spreadsheet_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' # TODO: Update placeholder value.
# The A1 notation of a range to search for a logical table of data.
# Values will be appended after the last row of the table.
range_ = 'Sheet 1!A2:E' # TODO: Update placeholder value.
value_input_option = 'USER_ENTERED' # TODO: Update placeholder value.
insert_data_option = '' # TODO: Update placeholder value.
value_range_body = {"values": [[d_string, sensor01]]}
request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, body=value_range_body)
response = request.execute()
# TODO: Change code below to process the `response` dict:
pprint(response)
Step 7.- Maybe you want to run it from the command-line before automating it with HASS. There you go:
python gsheets_write.py 654
and you should see that 654 is appended in your spreadsheet together with the timestamp in the first column. If everything is ok, now it is time to integrate it with HASS.
Step 8.- Create the Shell Command file /home/homeassistant/.homeassistant/includes/shell_commands/record_sensors_sc.yaml containing:
record_sensors:
python /home/homeassistant/.homeassistant/includes/shell_scripts/gsheets_write.py {{sensor01}}
Step 9.- Create the automation file /home/homeassistant/.homeassistant/includes/automations/record_sensors_au.yaml containing (in this case this automation is run every 15 minutes and the sensor is just the temperature taken from the Dark Sky component; you should update to the sensor you want):
- alias: 'Record sensors on GSheets'
trigger:
platform: time
minutes: '/15'
seconds: 00
action:
- service: shell_command.record_sensors
data_template:
sensor01: '{{states.sensor.dark_sky_temperature.state | float}}'
Step 10.- Finally, my configuration.yaml includes these lines:
automation: !include_dir_merge_list includes/automations
shell_command: !include_dir_merge_named includes/shell_commands
Results
Main results of the described process is having a Google sheet automatically populated with your sensors values. An example of this is shown is the next screenshot:
And as @herrkarlson described here you can embed the plot in HA dashboard using the Camera component. This is my result:
Conclusions
- I’ve managed to upload values from sensors to a Google sheet.
- Some parts of the process are still a bit obscure for me.
- I wouldn’t follow this process if you don’t know what you are doing (although I do not follow this rule for myself!)
Final words
I think I’ve cited all the useful links I employed and the users that, anonymously, allowed me to implement this. Kudos to them!
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!
