And when you log in on your machine and run gsheets.py it’s the same user who runs homeassistant?
How can I see which user runs homeassistant?
How did you install homeassistant?
Followed the instructions: https://www.home-assistant.io/hassio/installation/
Flashed it via etcher on the device. So nothing Special i would say.
I see on forum that hassio have some problems with running shell_commands. Try to enable logger to get more verbose error log.
logger:
default: info
logs:
homeassistant.components.shell_command: debug
But as i said i don’t use hassio, I’m sorry I can’t be of more help to you in this regard.
You can try to run this with appdaemon. There is an option to load additional python_packages with appdaemon addon configuration.
"python_packages": [
"gspread",
"oauth2client"
]
Once you set appdaemon make gsheets.py file
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)/300)+1)*300
runtime = runtime.replace(minute=0, second=0, microsecond=0) + datetime.timedelta(seconds=addseconds)
self.run_every(self.publish_to_gs,runtime,300)
def publish_to_gs(self,kwargs):
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('/config/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_temp = self.get_state("sensor.weather_temperature")
state_humi = self.get_state("sensor.weather_humidity")
sheet = client.open("CHANGE_TO_YOUR_SHEET_NAME").sheet1
row = [d_string,state_temp,state_humi]
index = 6
sheet.insert_row(row, index, value_input_option='USER_ENTERED')
And add this to your apps.yaml
google_sheets:
module: gsheets
class: GSheets
Or you could try this.
1 Like
Not sure it was entirely necessary, but I ended up having my shell command actually point to a bash script (.sh file) within which I point to my python environment and my .py file. I also had to explicitly point to where I was keeping the credentials and token files within my python code (see below) - I think this was really the key to my success.
In my config files:
shell_command:
google_sheets_script: 'bash /home/homeassistant/.homeassistant/shell_scripts/google_sheets_script.sh'
In my google_sheets_script.sh bash script:
#!/srv/homeassistant/bin
srcdir="~"
source "/srv/homeassistant/bin/activate"
python3 /home/homeassistant/.homeassistant/python_scripts/google_sheets_script.py
Contents of python_scripts folder:
- google_sheets_script.py
- credentials.json
- token.json
Below you can see where I put the actual path to my authentication files within the python file:
# The file token.json stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
store = file.Storage('/home/homeassistant/.homeassistant/python_scripts/token.json')
creds = store.get()
if not creds or creds.invalid:
flow = client.flow_from_clientsecrets('/home/homeassistant/.homeassistant/python_scripts/credentials.json', SCOPES)
creds = tools.run_flow(flow, store)
service = build('sheets', 'v4', http=creds.authorize(Http()))Did you try to run python script directly from shell_command with python binary from venv?
shell_command:
/srv/homeassistant/bin/python3 /home/homeassistant/.homeassistant/python_scripts/google_sheets_script.pySo, I think I found the error - I enabled logging and see the following error:
b’Traceback (most recent call last):\n File “/config/www/gsheets/gsheets.py”, line 1, in \n import gspread\nModuleNotFoundError: No module named 'gspread'\n’
I already installed it (followed the instructions) but homeassistant didnt find the module? Still working when I execute it via console…
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