Writting sensors' values to Google Sheets

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.

You want to write to gsheet only if value is changed and not every 15-30 minutes? There is post in this thread that solves just that. :slight_smile: Writting sensors' values to Google Sheets

Not exactly. I want to write every 60 minutes, but only if it has been changed from previous value. A check of previous value from google sheet maybe? listen_state with electricity consumption would write too many data for me :slight_smile:

Mhm. I don’t think you need to read value form gsheet because in appdaemon you have old and new. So something like

if new!=old
  some code ...

Like in this post AppDaemon state machines
I’m like you with zero experience in python or any other language :smiley: but sometimes i can copy from smarter people and make it work. But i didn’t sleep for 3 months because of my newborn baby so i am useless. :smiley: Ask at AppDaemon thread. Smart and well rested people there will help you.

Ok thanks for suggestion. I will investigate it. And congrats on newborn! It will give your more sleep over time :wink:

1 Like

I get notification when you reply to me. I added shelve as db. This could be done without it and just with simple text file but i found shelve so i done it that way. Add shelve in hassio appdaemon config.

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

apps.yaml

google_sheets:
  module: gsheets
  class: GSheets
  file: /home/.homeassistant/conf/state.dat

gsheets.py

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

class GSheets(hass.Hass):
  def initialize(self):
     self.log("GSheets started")
     runtime = datetime.datetime.now()
     addseconds = (round((runtime.minute*60 + runtime.second)/3600)+1)*3600
     runtime = runtime.replace(minute=0, second=0, microsecond=0) + datetime.timedelta(seconds=addseconds)
     self.run_every(self.publish_to_gs,runtime,3600)
     self.listen_state(self.state_change, "input_number.slider2")
       
  def state_change(self, entity, attribute, old, new, kwargs):
    fmt = '%Y-%m-%d %H:%M:%S'
    d = datetime.datetime.now()
    d_string = d.strftime(fmt)
    data= int(round(float(self.get_state("input_number.slider2"))))
 
    device_db = shelve.open(self.args["file"])
    device_db["slider2"] = data
    device_db.close()

  def publish_to_gs(self, kwargs):
    scope = ['https://spreadsheets.google.com/feeds',
              'https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('/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)
    data= int(round(float(self.get_state("input_number.slider2"))))
     
    sheet = client.open("test").sheet1
    val = int(sheet.acell('B6').value)
    self.log(("Gsheet value is {}").format(val))

    device_db = shelve.open(self.args["file"])
    old_data = int(device_db["slider2"])
    device_db.close()
    self.log(("DB value is {}").format(old_data))

    if (old_data != val):
        row = [d_string,data]
        index = 6
        sheet.insert_row(row, index, value_input_option='RAW')
        self.log(("Updating gsheets. New value is {}").format(data))
    else:
        self.log("No need to update gsheets. Value didn't change")
1 Like

I did a workaround from google sheets :frowning: Created new sheet called ElectricitySelect with following query in A1 cell:
=query(Electricity!A:C,“select min(A),B,C where B <> ‘’ group by B,C order by Min(A) desc”,1)

But Electricity sheet still have lot’s of redundant data and google sheets does not have remove duplicates. Was thinking to implement google script that would clear duplicates every week or so.

But python approach would be more elegant. Pity, that I am over hasio and don’t have dev environment to quickly play with python code with trials and errors :slight_smile:
Where did you made that change?

P.S. do I have to mention you @vladosam to ping you, or automatic reply to your post pings you already?

I somehow edited post instead of new post. :crazy_face:

Got some error on shelve package import:

You should consider upgrading via the 'pip install --upgrade pip' command.
Collecting shelve
  Could not find a version that satisfies the requirement shelve (from versions: )
No matching distribution found for shelve
You are using pip version 18.1, however version 19.0.3 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
FATAL: Failed installing package shelve

I would be afraid to do that -pip install -upgrade pip thing. And would not even know how to do it in hassio

Maybe there is no need to install anything? Delete shelve from appdaemon config.

You are right! Thanks! And it works! You should definitely wrap it as separate plugin :slight_smile:
P.S. my approach with using shelve logic:

/config/appdaemon/apps/gsheets.py

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

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
     
     shelveDevice_db = shelve.open(self.args["shelveFile"])
     
     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"]
          nameInShelve = option["sheetName"]+'_'+nameInSheet
          
          if nameInShelve in shelveDevice_db:
               old_data = shelveDevice_db[nameInShelve]
          else:
               old_data = 0
               
               
          if (old_data != entityState):
               row = [d_string,nameInSheet,entityState]
               index = 2
               sheet.insert_row(row, index, value_input_option='USER_ENTERED')
               shelveDevice_db[nameInShelve] = entityState
          
     shelveDevice_db.close()

     

  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

/config/appdaemon/apps/apps.yaml

google_sheets:
  module: gsheets
  class: GSheets
  SpreadSheetName: "Hass to google sheets"
  shelveFile: /config/appdaemon/apps/ShelveState.dat
  #RunEverySec: 30
  #RunEverySec: 3600 # every 60 mins
  RunEverySec: 1800 # every 30 mins
  upload: [
    {
      sheetName: "Humidity",
      nameInSheet: 'LivingRoom',
      entity: "sensor.humidity_1a"
    },
     # <....>
    {
      sheetName: "Electricity",
      nameInSheet: 'Rekuperatorius',
      entity: "sensor.consumption_rekuperatorius"
    }
    ]  

Final test result:
image

1 Like

I like your approach to this more. :smiley: Auto creating sheets is a nice touch.