Writting sensors' values to Google Sheets


#41

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.


#42

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


#43

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:


#44

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.


#45

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


#46

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

#47

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?


#48

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


#49

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


#50

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


#51

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


#52

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


#53

It’s not mine. I borrowed that part from google sheets addon :slight_smile: