Writting sensors' values to Google Sheets

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

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

How can I see which user runs homeassistant?

How did you install homeassistant?

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

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

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.

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.

1 Like

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()))

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

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…

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

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

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’

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

Thank you very much for your help! Great!

@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?

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

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?

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

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”

2 Likes

Hi.
Hassio user here with zero experience in Python :slight_smile:
Extremely helpful topic for people who needs to track sensors to google sheets. I tried google_sheets add on at first. But it’s not working because of some changes in scope. Then tried fork of @Wonkavator. It creates sheets, but does not fill them with data.
After finding this thread I merged google_sheets code / config with @vladosam solution when helping @taddeuz. I wanted a bit more separation between different sensor types and naming sensors in google sheets

Config after my merge /config/appdaemon/apps/apps.yaml:

google_sheets:
  module: gsheets
  class: GSheets
  SpreadSheetName: "Hass to google sheets"
  RunEverySec: 30
  upload: [
    {
      sheetName: "Humidity",
      nameInSheet: '1a.',
      entity: "sensor.humidity_1a"
    },
	# <....>
    {
      sheetName: "Temperature",
      nameInSheet: 'V kambarys',
      entity: "sensor.temperature_vijauskamb"
    },
    {
      sheetName: "Electricity",
      nameInSheet: 'Gyvatukas',
      entity: "sensor.consumption_gyvatukas"
    },
    {
      sheetName: "Electricity",
      nameInSheet: 'Boileris',
      entity: "sensor.consumption_boileris"
    }    
    ]  

Code itself:
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)/self.args["RunEverySec"])+1)*self.args["RunEverySec"]
     runtime = runtime.replace(minute=0, second=0, microsecond=0) + datetime.timedelta(seconds=addseconds)
     self.run_every(self.publish_to_gs,runtime,self.args["RunEverySec"]) 

  def publish_to_gs(self,kwargs):
     scope = ['https://spreadsheets.google.com/feeds',
              'https://www.googleapis.com/auth/drive']
     creds = ServiceAccountCredentials.from_json_keyfile_name('/share/googleDriveCredentials.json', scope)
     client = gspread.authorize(creds)
     fmt = '%Y-%m-%d %H:%M:%S'
     d = datetime.datetime.now()
     d_string = d.strftime(fmt)


     spreadSheet = client.open(self.args["SpreadSheetName"])
     uploadJson = self.args["upload"] #json string
     
     for option in uploadJson:
          if(self.__checkSheetsCreated(spreadSheet ,option["sheetName"]) == False):
               sheet = spreadSheet.add_worksheet(title=option["sheetName"], rows="100", cols="10")
          else:
               sheet = spreadSheet.worksheet(option["sheetName"])
          entityState = self.get_state(option["entity"])
          nameInSheet = option["nameInSheet"]
          
          
          row = [d_string,nameInSheet,entityState]
          index = 2
          sheet.insert_row(row, index, value_input_option='USER_ENTERED')
          
          

     

  def __checkSheetsCreated(self,spreadSheet,sheetName):
     #output a list of all the sheets in a document
     listWorkSheets = spreadSheet.worksheets()
     
     for workSheet in listWorkSheets:
          if(sheetName == workSheet.title):
               return True
     return False

Result of spreadsheet:
image

And here comes the question for Python / hass professionals : is it hard to put condition filling value to google sheets only if previous value (by name) differs? I don’t need listening to in my scenario. Every hour (or so) filling is o.k. for me. But for most of my electricity sensors there would be lots of redundant data.

I wounder why all of this cannot be converted to add on. As google sheets is pretty useful cloud storage.