Writting sensors' values to Google Sheets


#21

Gsheets.py and client_secret.json are both in /home/homeassistant folder? It’s not some venv?


#22

Yes, both are in that folder and it’s not a venv (I just added the files again in a different folder, same error)


#23

And when you log in on your machine and run gsheets.py it’s the same user who runs homeassistant?


#24

How can I see which user runs homeassistant?


#25

How did you install homeassistant?


#26

Followed the instructions: https://www.home-assistant.io/hassio/installation/

Flashed it via etcher on the device. So nothing Special i would say.


#27

I see on forum that hassio have some problems with running shell_commands. Try to enable logger to get more verbose error log.

logger:
  default: info
  logs:
    homeassistant.components.shell_command: debug

But as i said i don’t use hassio, I’m sorry I can’t be of more help to you in this regard.


Shell_command: problems executing py
#28

You can try to run this with appdaemon. There is an option to load additional python_packages with appdaemon addon configuration.

  "python_packages": [
    "gspread",
    "oauth2client"
  ]

Once you set appdaemon make gsheets.py file

import appdaemon.plugins.hass.hassapi as hass
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import datetime

class GSheets(hass.Hass):
  def initialize(self):
     self.log("GSheets started")
     runtime = datetime.datetime.now()
     addseconds = (round((runtime.minute*60 + runtime.second)/300)+1)*300
     runtime = runtime.replace(minute=0, second=0, microsecond=0) + datetime.timedelta(seconds=addseconds)
     self.run_every(self.publish_to_gs,runtime,300)

  def publish_to_gs(self,kwargs):
     scope = ['https://spreadsheets.google.com/feeds',
              'https://www.googleapis.com/auth/drive']
     creds = ServiceAccountCredentials.from_json_keyfile_name('/config/full/path/to/client_secret.json', scope)
     client = gspread.authorize(creds)
     fmt = '%Y-%m-%d %H:%M:%S'
     d = datetime.datetime.now()
     d_string = d.strftime(fmt)
     state_temp = self.get_state("sensor.weather_temperature")
     state_humi = self.get_state("sensor.weather_humidity")

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

     row = [d_string,state_temp,state_humi]
     index = 6
     sheet.insert_row(row, index, value_input_option='USER_ENTERED')

And add this to your apps.yaml

google_sheets:
  module: gsheets
  class: GSheets

Or you could try this.


#29

Not sure it was entirely necessary, but I ended up having my shell command actually point to a bash script (.sh file) within which I point to my python environment and my .py file. I also had to explicitly point to where I was keeping the credentials and token files within my python code (see below) - I think this was really the key to my success.

In my config files:

shell_command:
  google_sheets_script: 'bash /home/homeassistant/.homeassistant/shell_scripts/google_sheets_script.sh'

In my google_sheets_script.sh bash script:

#!/srv/homeassistant/bin

srcdir="~"
source "/srv/homeassistant/bin/activate"
python3 /home/homeassistant/.homeassistant/python_scripts/google_sheets_script.py

Contents of python_scripts folder:

  • google_sheets_script.py
  • credentials.json
  • token.json

Below you can see where I put the actual path to my authentication files within the python file:

# The file token.json stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
store = file.Storage('/home/homeassistant/.homeassistant/python_scripts/token.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('/home/homeassistant/.homeassistant/python_scripts/credentials.json', SCOPES)
    creds = tools.run_flow(flow, store)
service = build('sheets', 'v4', http=creds.authorize(Http()))

#30

Did you try to run python script directly from shell_command with python binary from venv?

shell_command:
  /srv/homeassistant/bin/python3 /home/homeassistant/.homeassistant/python_scripts/google_sheets_script.py

#31

So, I think I found the error - I enabled logging and see the following error:
b’Traceback (most recent call last):\n File “/config/www/gsheets/gsheets.py”, line 1, in \n import gspread\nModuleNotFoundError: No module named ‘gspread’\n’

I already installed it (followed the instructions) but homeassistant didnt find the module? Still working when I execute it via console…


#32

Hassio is pretty restricted environment. You can try with appdaemon or to manualy download library like in that link in post before.


#33

I just installed appdaemon and I get the following error:

2018-12-21 23:19:27.440503 INFO AppDaemon: Initializing app google_sheets using class GSheets from module gsheets
2018-12-21 23:19:27.441477 WARNING AppDaemon: Unable to find module module gsheets - google_sheets is not initialized

I used the same setup like you posted. Are there additional steps? Is the following code in apps.yaml correct?

google_sheets:
module: gsheets
class: GSheets


#34

My mistake, the gsheets.py file must located in the apps folder.
But how can I call the gsheets.py / module?

When I use the shell_command like before, I get still an error:
b’Traceback (most recent call last):\n File “/config/appdaemon/apps/gsheets.py”, line 1, in \n import appdaemon.plugins.hass.hassapi as hass\nModuleNotFoundError: No module named ‘appdaemon’\n’


#35

Ok, just found the solution!
No need of running the script…my fault.

Thank you very much for your help! Great!


#36

@vladosam
One last question: I want to push the values of an input field to gsheets, but only when I have entered a text.
How can I define that in the gsheets.py?


#37

Great. I’m glad it works.
To publish only on change use this.

import appdaemon.plugins.hass.hassapi as hass
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import datetime

class GSheets(hass.Hass):
  def initialize(self):
     self.log("Hello from gsheets")
     self.listen_state(self.publish_to_gs, (self.args["entity_id"]))

  def publish_to_gs(self, entity, attribute, old, new, kwargs):
     scope = ['https://spreadsheets.google.com/feeds',
              'https://www.googleapis.com/auth/drive']
     creds = ServiceAccountCredentials.from_json_keyfile_name('/FULL/PATH/TO/client_secret.json', scope)
     client = gspread.authorize(creds)
     fmt = '%Y-%m-%d %H:%M:%S'
     d = datetime.datetime.now()
     d_string = d.strftime(fmt)
     state = self.get_state(self.args["entity_id"])

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

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

And in apps.yaml:

google_sheets:
  module: gsheets
  class: GSheets
  entity_id: INPUT_NUMBER.XXXXX

#38

It’s working fine and all of my problems are solved.

Another question: Is there a way to display a chart from sheets in HA? I already found this:

camera:

  • platform: generic
    still_image_url: url_to_published_chart
    name: “Some name for your sheet”

Or:

Or is there another way?


#39

I just saw this because you didn’t reply to me. :slight_smile: so what did you chose to display chart?


#40

Hey,

I write the values to gsheets and created a chart inside gsheets. All the calculations are done in the sheet (automatically without doing manual stuff).

I found a solution for displaying the charts from gsheets in HA: you can publish the chart and use the picture entity:

  • type: picture
    image: <URL_TO_CHART>

I have to use the following part at the end of the url: “/pubchart?oid=123456789&format=image”