Writting sensors' values to Google Sheets


#1

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:

  1. 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.
  2. 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.
  3. 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!


Script to read cell in google sheets
Plotting data from a Google sheet using matplotlib et al. (Howto)
Publish some datas from HA on a website?
#2

Nice work! Will you wrap this up as a HASS component?

How are you going to monitor the electricity usage? I have ordered a current monitor and will build a micropython sensor


#3

Nice work! Will you wrap this up as a HASS component?

Thanks! I don’t have a clue how to develop a HASS component. I’ll check but, taking into account my limitations, for sure not in the short or medium term.

How are you going to monitor the electricity usage? I have ordered a current monitor and will build a micropython sensor

Electricity is not my main concern right now since I use a boiler to heat up my house. So I would need something like a gas meter, which I guess it is impossible (i.e. forbidden) to be installed by someone not qualified. So I’m thinking on placing a light sensor in my boiler to detect when it switches on (there is a red light in the boiler main panel). Later, I’ll need to callibrate the time it is on with gas consumption. I have all spring and summer to think about it :grin:
May I ask which current monitor have you ordered? I’m waiting for this. It should arrive by the end of April. I’ll use a Wemos D1 mini to count pulses and send them to HA via MQTT to monitor power consumption. However I still don’t know where I will place it.


#4

I have ordered https://www.amazon.co.uk/gp/product/B00J2N3GSY/ref=oh_aui_detailpage_o04_s00?ie=UTF8&psc=1 and intend to do something like http://www.instructables.com/id/Internet-connected-home-energy-monitor/

Your solution for the gas sounds promising, probably could stick a temperature sensor on there too, going to try this for my electric boiler :slight_smile:


#5

Of interest https://github.com/burnash/gspread


#6

I tired the above project, and it actually works when i write the command line:
python /home/homeassistant/.homeassistant/includes/shell_scripts/gsheets_write.py test

and it posts test in the spreadsheet, but when i add the command in command_line component or shell_commands it doesn’t work.

Can anyone help with that?

Thanks.


#7

Hi @shady282 ,
No idea why the script runs in the command line but not when called in HA. I would suggest:

  • Do you have any other script that runs?
  • Can you post the involved yaml files to check if there’s any issue with indentation, or the automation that calls the script,…?
  • Any entry in the HA log file to give us a clue?

#8

Hey!

  • I have another script that posts to tasker and runs good.
  • The script we are talking about already runs good when using putty: https://imgur.com/a/90tSBvG
  • I copied the same script you provided above, and since it is working and posting i guess there is no issue in indentation ?
  • Also i tried to run it from automation trigger by itself, and also i tried to call the service/shell_command manually, but still not working.
  • the config check, gives no error.

Config:

shell_command:
(2spaces) record_sensors: “python /home/homeassistant/.homeassistant/gdata/gsheets_write_auth.py on”

Thank you for trying to help !


#9

Wow, you’ve done all the homework. Sorry, I’m not smart enough. Let’s hope someone else gives us the solution. Good luck!


#10

Hey, just found an error in log when I trigger shell command, which is as follows:

2018-05-27 17:18:00 ERROR (MainThread) [homeassistant.components.shell_command] Error running command: python /home/homeassistant/.homeassistant/gdata/gsheets_write_auth.py on, return code: 1

Also an update to the above pic, of home assistant user command: https://imgur.com/a/FyJAtfh
Does that helps?

Thanks


#11

According to the source code it seems that the command did not run successfully; but we do not know why :frowning:
So I just saw some lines above in that python file that someone has added stdout and stderr to debug output from a shell_command. This means that if you enable debug mode for this component in the logger section in the configuration file we could have more detailed info of what the error is.
Since this addition was made 11 days ago, it is very likely that it is not implemented in your HA installation yet, so if I were you (beware that I’m a total noob), I would add the lines concerning to the debug output in the shell_command.py in your HA installation and would include a logger section to something like:

# Example configuration.yaml entry
logger:
  default: warning
  logs:
    homeassistant.components.shell_command: debug

and check the log when you trigger the shell command to see if we get more information.
Good luck!


#12

I’m trying to do something similar and getting this same error in my log. Wondering if you ever found a resolution?


#13

I tried to install the component but with no luck. After executing the script and opening the verification link from the output, I have to enter the displayed verification code from google - but where?


#14

@Brett_C @taddeuz I can share how i set gsheet. First of all thanks @timseebeck for inspiration :slight_smile: I’m using gspread library because it seems that it’s easier to use. Follow everything from this tutorial to set API for drive and sheets and client_secret.json. Once you set everything test it in venv.

python3 -m venv hatosheets
cd hatosheets/
source bin/activate
pip install gspread oauth2client

copy client_secret.json to hatosheets folder. Create gsheets.py file and paste this code in it.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import datetime
import sys

# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
fmt = '%Y-%m-%d %H:%M:%S'
d = datetime.datetime.now()
d_string = d.strftime(fmt)

val1 = sys.argv[1]
val2 = sys.argv[2]

sheet = client.open("CHANGE_TO_YOUR_SHEET_NAME").sheet1

row = [d_string,val1,val2]
index = 1
sheet.insert_row(row, index, value_input_option='USER_ENTERED')

Run it with
python3 gsheets.py 10 20

If everything is ok just install gspread and oauth2client in hass venv and copy gsheets.py there.

For those return code: 1 errors in shell_command i was getting those also. My hass is installed in venv and if i run this script with this shell_command it wont work.

shell_command:
  uptosheets: python3 /path/to/gsheets.py 10 20

You need to use python3 binary from venv.

shell_command:
  uptosheets: /path/to/hass_venv/bin/python3 /path/to/gsheets.py 10 20

#15

Thank you!
Works like a charm, very nice!


#16

I have only one problem: I would like to execute the script automatically by inserting a value into an input number field.

When I enter a number, the script is beeing executed but I get the following error:

Error running command: python3 /home/homeassistant/gsheets.py 125, return code: 2
NoneType: None

I think there is something wrong with my path to client_secret.json and gsheets.py. But I have no idea - I tried several options, but no success…running hassio on a tinker board.

configuration.yaml

shell_command:
uptosheets: python3 /home/homeassistant/gsheets.py 125

automations.yaml

  • id: push_power
    alias: push_power
    trigger:
    platform: state
    entity_id: input_number.strom
    action:
    service: shell_command.uptosheets

gsheets.py

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import datetime
import sys

scope = [‘https://spreadsheets.google.com/feeds’,
https://www.googleapis.com/auth/drive’]
creds = ServiceAccountCredentials.from_json_keyfile_name(’/home/homeassistant/client_secret.json’, scope)
client = gspread.authorize(creds)
fmt = ‘%Y-%m-%d %H:%M:%S’
d = datetime.datetime.utcnow()
d_string = d.strftime(fmt)

val1 = sys.argv[1]

sheet = client.open(“test”).sheet1

row = [d_string,val1]
index = 1
sheet.insert_row(row, index, value_input_option=‘USER_ENTERED’)


#17

If you run gsheets.py with python and not from homeassistant do you get any errors?


#18

When I execute the command via console (i‘m using the configurator addon of Hass.io), there are no errors and the value is pushed to the sheet.
So I think I am using the wrong path in the shell_command.


#19

I din’t use hassio but i think your files gsheets.py and client_secret.json should be in /config folder. And not in /home/homeassistant.


#20

I already tried that, with the same error.
Which path to the gsheets.py (in configuration.yaml) and which path to the client_secret.json( in gsheets.py) should I use? The full path?

Very annoying, becaue it’s working when I execute it via console, but not in the shell_command…