Yes, both are in that folder and it’s not a venv (I just added the files again in a different folder, same error)
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.