Writting sensors' values to Google Sheets

Hi @shady282 ,
No idea why the script runs in the command line but not when called in HA. I would suggest:

  • Do you have any other script that runs?
  • Can you post the involved yaml files to check if there’s any issue with indentation, or the automation that calls the script,…?
  • Any entry in the HA log file to give us a clue?

Hey!

  • I have another script that posts to tasker and runs good.
  • The script we are talking about already runs good when using putty: https://imgur.com/a/90tSBvG
  • I copied the same script you provided above, and since it is working and posting i guess there is no issue in indentation ?
  • Also i tried to run it from automation trigger by itself, and also i tried to call the service/shell_command manually, but still not working.
  • the config check, gives no error.

Config:

shell_command:
(2spaces) record_sensors: “python /home/homeassistant/.homeassistant/gdata/gsheets_write_auth.py on”

Thank you for trying to help !

Wow, you’ve done all the homework. Sorry, I’m not smart enough. Let’s hope someone else gives us the solution. Good luck!

1 Like

Hey, just found an error in log when I trigger shell command, which is as follows:

2018-05-27 17:18:00 ERROR (MainThread) [homeassistant.components.shell_command] Error running command: python /home/homeassistant/.homeassistant/gdata/gsheets_write_auth.py on, return code: 1

Also an update to the above pic, of home assistant user command: https://imgur.com/a/FyJAtfh
Does that helps?

Thanks

According to the source code it seems that the command did not run successfully; but we do not know why :frowning:
So I just saw some lines above in that python file that someone has added stdout and stderr to debug output from a shell_command. This means that if you enable debug mode for this component in the logger section in the configuration file we could have more detailed info of what the error is.
Since this addition was made 11 days ago, it is very likely that it is not implemented in your HA installation yet, so if I were you (beware that I’m a total noob), I would add the lines concerning to the debug output in the shell_command.py in your HA installation and would include a logger section to something like:

# Example configuration.yaml entry
logger:
  default: warning
  logs:
    homeassistant.components.shell_command: debug

and check the log when you trigger the shell command to see if we get more information.
Good luck!

1 Like

I’m trying to do something similar and getting this same error in my log. Wondering if you ever found a resolution?

I tried to install the component but with no luck. After executing the script and opening the verification link from the output, I have to enter the displayed verification code from google - but where?

@Brett_C @taddeuz I can share how i set gsheet. First of all thanks @timseebeck for inspiration :slight_smile: I’m using gspread library because it seems that it’s easier to use. Follow everything from this tutorial to set API for drive and sheets and client_secret.json. Once you set everything test it in venv.

python3 -m venv hatosheets
cd hatosheets/
source bin/activate
pip install gspread oauth2client

copy client_secret.json to hatosheets folder. Create gsheets.py file and paste this code in it.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import datetime
import sys

# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
fmt = '%Y-%m-%d %H:%M:%S'
d = datetime.datetime.now()
d_string = d.strftime(fmt)

val1 = sys.argv[1]
val2 = sys.argv[2]

sheet = client.open("CHANGE_TO_YOUR_SHEET_NAME").sheet1

row = [d_string,val1,val2]
index = 1
sheet.insert_row(row, index, value_input_option='USER_ENTERED')

Run it with
python3 gsheets.py 10 20

If everything is ok just install gspread and oauth2client in hass venv and copy gsheets.py there.

For those return code: 1 errors in shell_command i was getting those also. My hass is installed in venv and if i run this script with this shell_command it wont work.

shell_command:
  uptosheets: python3 /path/to/gsheets.py 10 20

You need to use python3 binary from venv.

shell_command:
  uptosheets: /path/to/hass_venv/bin/python3 /path/to/gsheets.py 10 20
3 Likes

Thank you!
Works like a charm, very nice!

I have only one problem: I would like to execute the script automatically by inserting a value into an input number field.

When I enter a number, the script is beeing executed but I get the following error:

Error running command: python3 /home/homeassistant/gsheets.py 125, return code: 2
NoneType: None

I think there is something wrong with my path to client_secret.json and gsheets.py. But I have no idea - I tried several options, but no success…running hassio on a tinker board.

configuration.yaml

shell_command:
uptosheets: python3 /home/homeassistant/gsheets.py 125

automations.yaml

  • id: push_power
    alias: push_power
    trigger:
    platform: state
    entity_id: input_number.strom
    action:
    service: shell_command.uptosheets

gsheets.py

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import datetime
import sys

scope = [‘https://spreadsheets.google.com/feeds’,
https://www.googleapis.com/auth/drive’]
creds = ServiceAccountCredentials.from_json_keyfile_name(‘/home/homeassistant/client_secret.json’, scope)
client = gspread.authorize(creds)
fmt = ‘%Y-%m-%d %H:%M:%S’
d = datetime.datetime.utcnow()
d_string = d.strftime(fmt)

val1 = sys.argv[1]

sheet = client.open(“test”).sheet1

row = [d_string,val1]
index = 1
sheet.insert_row(row, index, value_input_option=‘USER_ENTERED’)

If you run gsheets.py with python and not from homeassistant do you get any errors?

When I execute the command via console (i‘m using the configurator addon of Hass.io), there are no errors and the value is pushed to the sheet.
So I think I am using the wrong path in the shell_command.

I din’t use hassio but i think your files gsheets.py and client_secret.json should be in /config folder. And not in /home/homeassistant.

I already tried that, with the same error.
Which path to the gsheets.py (in configuration.yaml) and which path to the client_secret.json( in gsheets.py) should I use? The full path?

Very annoying, becaue it’s working when I execute it via console, but not in the shell_command…

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.