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
I like your approach to this more. Auto creating sheets is a nice touch.
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
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.
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?
It depends what code you used. There is multiple examples in this thread. Some expect that sheet is already created.