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.XXXXXIt’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. 
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
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:
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. 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
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 
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. 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 
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 
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?
I somehow edited post instead of new post. 
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
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:
1 Like