Writting sensors' values to Google Sheets

vladosam · March 15, 2019, 8:13am

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

vukisz · March 15, 2019, 8:43am

I did a workaround from google sheets 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
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?

vladosam · March 15, 2019, 9:02am

I somehow edited post instead of new post.

vukisz · March 15, 2019, 9:46am

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

vladosam · March 15, 2019, 9:56am

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

vukisz · March 15, 2019, 10:46am

You are right! Thanks! And it works! You should definitely wrap it as separate plugin
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:

vladosam · March 15, 2019, 11:09am

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

vukisz · March 15, 2019, 12:30pm

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

Vanna · November 16, 2020, 4:08pm

Hello!
I was looking for writing sensors’ values to Google Sheets; can I install gspread from Appdaemon and run everything from it? I cannot enetr in the path home/.homeassistant

vladosam · November 16, 2020, 5:04pm

You can install gspread in appdaemon. How are you running appdaemon? Is it venv install or as addon in ex hassos? If you run appdaemon as a addon there is a post in this thread how to install gspread.

Vanna · November 16, 2020, 5:20pm

I installed Appdaemon as addon in HassOs; I also installed gspread and it looks everything works; I put googleDriveCredential.json in share.
But I need to create first the sheet in Google Sheet or not?
This is the log in AppDaemon

[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] 00-banner.sh: executing... 
-----------------------------------------------------------
 Add-on: AppDaemon 4
 Python Apps and Dashboard using AppDaemon 4.x for Home Assistant
-----------------------------------------------------------
 Add-on version: 0.3.1
 You are running the latest version of this add-on.
 System: HassOS 4.16  (armv7 / raspberrypi3)
 Home Assistant Core: 0.117.5
 Home Assistant Supervisor: 2020.11.0
-----------------------------------------------------------
 Please, share the above information when looking for help
 or support in, e.g., GitHub, forums or the Discord chat.
-----------------------------------------------------------
[cont-init.d] 00-banner.sh: exited 0.
[cont-init.d] 01-log-level.sh: executing... 
Log level is set to INFO
[cont-init.d] 01-log-level.sh: exited 0.
[cont-init.d] appdaemon.sh: executing... 
Looking in links: https://wheels.home-assistant.io/alpine-3.12/armv7/
Collecting gspread
  Downloading gspread-3.6.0-py3-none-any.whl (26 kB)
Requirement already satisfied: requests>=2.2.1 in /usr/lib/python3.8/site-packages (from gspread) (2.23.0)
Collecting google-auth-oauthlib>=0.4.1
  Downloading google_auth_oauthlib-0.4.2-py2.py3-none-any.whl (18 kB)
Collecting google-auth>=1.12.0
  Downloading google_auth-1.23.0-py2.py3-none-any.whl (114 kB)
Requirement already satisfied: chardet<4,>=3.0.2 in /usr/lib/python3.8/site-packages (from requests>=2.2.1->gspread) (3.0.4)
Requirement already satisfied: idna<3,>=2.5 in /usr/lib/python3.8/site-packages (from requests>=2.2.1->gspread) (2.9)
Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/lib/python3.8/site-packages (from requests>=2.2.1->gspread) (1.25.9)
Requirement already satisfied: certifi>=2017.4.17 in /usr/lib/python3.8/site-packages (from requests>=2.2.1->gspread) (2020.4.5.1)
Requirement already satisfied: requests-oauthlib>=0.7.0 in /usr/lib/python3.8/site-packages (from google-auth-oauthlib>=0.4.1->gspread) (1.3.0)
Requirement already satisfied: six>=1.9.0 in /usr/lib/python3.8/site-packages (from google-auth>=1.12.0->gspread) (1.15.0)
Collecting rsa<5,>=3.1.4; python_version >= "3.5"
  Downloading rsa-4.6-py3-none-any.whl (47 kB)
Requirement already satisfied: setuptools>=40.3.0 in /usr/lib/python3.8/site-packages (from google-auth>=1.12.0->gspread) (47.0.0)
Collecting cachetools<5.0,>=2.0.0
  Downloading cachetools-4.1.1-py3-none-any.whl (10 kB)
Collecting pyasn1-modules>=0.2.1
  Downloading pyasn1_modules-0.2.8-py2.py3-none-any.whl (155 kB)
Requirement already satisfied: oauthlib>=3.0.0 in /usr/lib/python3.8/site-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (3.1.0)
Collecting pyasn1>=0.1.3
  Downloading pyasn1-0.4.8-py2.py3-none-any.whl (77 kB)
Installing collected packages: pyasn1, rsa, cachetools, pyasn1-modules, google-auth, google-auth-oauthlib, gspread
Successfully installed cachetools-4.1.1 google-auth-1.23.0 google-auth-oauthlib-0.4.2 gspread-3.6.0 pyasn1-0.4.8 pyasn1-modules-0.2.8 rsa-4.6
Looking in links: https://wheels.home-assistant.io/alpine-3.12/armv7/
Collecting oauth2client
  Downloading oauth2client-4.1.3-py2.py3-none-any.whl (98 kB)
Requirement already satisfied: pyasn1-modules>=0.0.5 in /usr/lib/python3.8/site-packages (from oauth2client) (0.2.8)
Requirement already satisfied: six>=1.6.1 in /usr/lib/python3.8/site-packages (from oauth2client) (1.15.0)
Requirement already satisfied: pyasn1>=0.1.7 in /usr/lib/python3.8/site-packages (from oauth2client) (0.4.8)
Collecting httplib2>=0.9.1
  Downloading httplib2-0.18.1-py3-none-any.whl (95 kB)
Requirement already satisfied: rsa>=3.1.4 in /usr/lib/python3.8/site-packages (from oauth2client) (4.6)
Installing collected packages: httplib2, oauth2client
Successfully installed httplib2-0.18.1 oauth2client-4.1.3
[cont-init.d] appdaemon.sh: exited 0.
[cont-init.d] done.
[services.d] starting services
[services.d] done.
[18:02:28] INFO: Starting AppDaemon...
2020-11-16 18:02:33.925256 INFO AppDaemon: AppDaemon Version 4.0.5 starting
2020-11-16 18:02:33.928239 INFO AppDaemon: Python version is 3.8.5
2020-11-16 18:02:33.930655 INFO AppDaemon: Configuration read from: /config/appdaemon/appdaemon.yaml
2020-11-16 18:02:33.933244 INFO AppDaemon: Added log: AppDaemon
2020-11-16 18:02:33.935767 INFO AppDaemon: Added log: Error
2020-11-16 18:02:33.938391 INFO AppDaemon: Added log: Access
2020-11-16 18:02:33.940680 INFO AppDaemon: Added log: Diag
2020-11-16 18:02:34.114600 INFO AppDaemon: Loading Plugin HASS using class HassPlugin from module hassplugin
2020-11-16 18:02:34.328450 INFO HASS: HASS Plugin Initializing
2020-11-16 18:02:34.330406 INFO HASS: HASS Plugin initialization complete
2020-11-16 18:02:34.334346 INFO AppDaemon: Initializing HTTP
2020-11-16 18:02:34.337684 INFO AppDaemon: Using 'ws' for event stream
2020-11-16 18:02:34.362164 INFO AppDaemon: Starting API
2020-11-16 18:02:34.402087 INFO AppDaemon: Starting Admin Interface
2020-11-16 18:02:34.407183 INFO AppDaemon: Starting Dashboards
2020-11-16 18:02:34.877017 INFO HASS: Connected to Home Assistant 0.117.5
2020-11-16 18:02:35.194047 INFO AppDaemon: App 'google_sheets' added
2020-11-16 18:02:35.197758 INFO AppDaemon: App 'hello_world' added
2020-11-16 18:02:35.204908 INFO AppDaemon: Found 2 total apps
2020-11-16 18:02:35.208591 INFO AppDaemon: Starting Apps with 2 workers and 2 pins
2020-11-16 18:02:35.218791 INFO AppDaemon: Running on port 5050
2020-11-16 18:02:35.429716 INFO HASS: Evaluating startup conditions
2020-11-16 18:02:35.500963 INFO HASS: Startup condition met: hass state=RUNNING
2020-11-16 18:02:35.503367 INFO HASS: All startup conditions met
2020-11-16 18:02:35.745687 INFO AppDaemon: Got initial state from namespace default
2020-11-16 18:02:37.269541 INFO AppDaemon: Scheduler running in realtime
2020-11-16 18:02:37.339132 INFO AppDaemon: Adding /config/appdaemon/apps to module import path
2020-11-16 18:02:37.348412 INFO AppDaemon: Loading App Module: /config/appdaemon/apps/hello.py
2020-11-16 18:02:37.429413 INFO AppDaemon: Loading App Module: /config/appdaemon/apps/gsheets.py
2020-11-16 18:02:39.998215 INFO AppDaemon: Initializing app google_sheets using class GSheets from module gsheets
2020-11-16 18:02:40.009431 INFO AppDaemon: Initializing app hello_world using class HelloWorld from module hello
2020-11-16 18:02:40.882875 INFO google_sheets: GSheets started
2020-11-16 18:02:40.923710 INFO hello_world: Hello from AppDaemon
2020-11-16 18:02:40.932602 INFO hello_world: You are now ready to run Apps!
2020-11-16 18:02:40.946724 INFO AppDaemon: App initialization complete
2020-11-16 18:08:51.850132 INFO AppDaemon: New client Admin Client connected

I aslo opened the 192.168.1.xxx:5050 and the GSheet app is there:

Name	State	Callbacks (instance)	Callbacks (AD Lifetime)	Arguments
google_sheets	idle	0	0	{“module”:“gsheets”,“class”:“GSheets”,“SpreadSheetName”:“Hass to google sheets”,“shelveFile”:"/config/appdaemon/apps/ShelveState.dat",“RunEverySec”:1800,“upload”:[{“sheetName”:“Produzioni”,“nameInSheet”:“Prod”,“entity”:“sensor.daily_energy_fv”},{“sheetName”:“Consumi”,“nameInSheet”:“Cons”,“entity”:“sensor.daily_energy_totale”}]}
hello_world	idle	0	0	{“module”:“hello”,“class”:“HelloWorld”}

…and now?

vladosam · November 16, 2020, 5:25pm

It depends what code you used. There is multiple examples in this thread. Some expect that sheet is already created.

Vanna · November 16, 2020, 5:27pm

I used @ vukisz code, message #51

vladosam · November 16, 2020, 5:31pm

Then sheet should autocreate and updated every 30 minutes if threre isa change.

Vanna · November 16, 2020, 5:33pm

The app google_sheets just fired; but I cannot find the file

App	Execution Time	Repeat	Callback	Fired	Executed	Pinned	Thread	Kwargs
google_sheets	2020-11-16T19:00:00+01:00	0:30:00	publish_to_gs	1	1	true	0	{“interval”:1800}

vladosam · November 16, 2020, 5:36pm

This was written for appdaemon 3. Maybe you need to change something in code. Are there errors in log?

Vanna · November 16, 2020, 5:41pm

I’ve found it:

2020-11-16 18:30:01.573275 WARNING google_sheets: ------------------------------------------------------------
2020-11-16 18:30:01.578745 WARNING google_sheets: Unexpected error in worker for App google_sheets:
2020-11-16 18:30:01.583195 WARNING google_sheets: Worker Ags: {'id': '43df027614f34f9e9141de6719dce810', 'name': 'google_sheets', 'objectid': '914eea8300a04a0a86734b2b5f30b1f0', 'type': 'scheduler', 'function': <bound method GSheets.publish_to_gs of <gsheets.GSheets object at 0x74cfc538>>, 'pin_app': True, 'pin_thread': 0, 'kwargs': {'interval': 1800, '__thread_id': 'thread-0'}}
2020-11-16 18:30:01.588400 WARNING google_sheets: ------------------------------------------------------------
2020-11-16 18:30:01.626543 WARNING google_sheets: Traceback (most recent call last):
  File "/usr/lib/python3.8/site-packages/gspread/client.py", line 117, in open
    properties = finditem(
  File "/usr/lib/python3.8/site-packages/gspread/utils.py", line 97, in finditem
    return next((item for item in seq if func(item)))
StopIteration
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/usr/lib/python3.8/site-packages/appdaemon/threading.py", line 887, in worker
    funcref(self.AD.sched.sanitize_timer_kwargs(app, args["kwargs"]))
  File "/config/appdaemon/apps/gsheets.py", line 25, in publish_to_gs
    spreadSheet = client.open(self.args["SpreadSheetName"])
  File "/usr/lib/python3.8/site-packages/gspread/client.py", line 127, in open
    raise SpreadsheetNotFound
gspread.exceptions.SpreadsheetNotFound
2020-11-16 18:30:01.633394 WARNING google_sheets: ------------------------------------------------------------

vladosam · November 16, 2020, 5:55pm

It seems like it didn’t create sheet. Share code for gsheet.py and apps.yaml.

Vanna · November 16, 2020, 5:58pm

What do you mean?

Share code for gsheet.py and apps.yaml.

vladosam · November 16, 2020, 6:00pm

Share code in /config/appdaemon/apps/gsheets.py and apps.yaml for gsheets.