Gsheets.py and client_secret.json are both in /home/homeassistant folder? It’s not some venv?
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.
Shell_command: problems executing py
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.
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:
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.py
So, 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?
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!
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.XXXXX
It’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:
- platform: generic
name: “Some name for your sheet”
Or is there another way?
I just saw this because you didn’t reply to me. so what did you chose to display chart?
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
I have to use the following part at the end of the url: “/pubchart?oid=123456789&format=image”