This was written for appdaemon 3. Maybe you need to change something in code. Are there errors in log?
I’ve found it:
2020-11-16 18:30:01.573275 WARNING google_sheets: ------------------------------------------------------------
2020-11-16 18:30:01.578745 WARNING google_sheets: Unexpected error in worker for App google_sheets:
2020-11-16 18:30:01.583195 WARNING google_sheets: Worker Ags: {'id': '43df027614f34f9e9141de6719dce810', 'name': 'google_sheets', 'objectid': '914eea8300a04a0a86734b2b5f30b1f0', 'type': 'scheduler', 'function': <bound method GSheets.publish_to_gs of <gsheets.GSheets object at 0x74cfc538>>, 'pin_app': True, 'pin_thread': 0, 'kwargs': {'interval': 1800, '__thread_id': 'thread-0'}}
2020-11-16 18:30:01.588400 WARNING google_sheets: ------------------------------------------------------------
2020-11-16 18:30:01.626543 WARNING google_sheets: Traceback (most recent call last):
File "/usr/lib/python3.8/site-packages/gspread/client.py", line 117, in open
properties = finditem(
File "/usr/lib/python3.8/site-packages/gspread/utils.py", line 97, in finditem
return next((item for item in seq if func(item)))
StopIteration
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/lib/python3.8/site-packages/appdaemon/threading.py", line 887, in worker
funcref(self.AD.sched.sanitize_timer_kwargs(app, args["kwargs"]))
File "/config/appdaemon/apps/gsheets.py", line 25, in publish_to_gs
spreadSheet = client.open(self.args["SpreadSheetName"])
File "/usr/lib/python3.8/site-packages/gspread/client.py", line 127, in open
raise SpreadsheetNotFound
gspread.exceptions.SpreadsheetNotFound
2020-11-16 18:30:01.633394 WARNING google_sheets: ------------------------------------------------------------
It seems like it didn’t create sheet. Share code for gsheet.py and apps.yaml.
What do you mean?
Share code for gsheet.py and apps.yaml.
Share code in /config/appdaemon/apps/gsheets.py and apps.yaml for gsheets.
You need to point googledrivecredentials.json to right location. You can’t just blindly copy code. He put it in /share folder.
creds = ServiceAccountCredentials.from_json_keyfile_name(’/share/googleDriveCredentials.json
Sorry, maybe I didn’t explain. I’ve done it as first think, I followed that tutorial https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html
and
https://github.com/brendena/hassio-addons/tree/master/google_sheets
The json file is in the share folder before doing other things.
This is not that hassio addon. You probably mix something in process. Did you try to run that addon without messing with python and appdaemon?
It is not possible to install that addon in 0.117.5. I only followed it for the Google API
Ok. Then share code you copyed and edited from vukisz post.
configuration in appdaemon
system_packages: []
python_packages:
- gspread
- oauth2client
init_commands: []
log_level: info
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: "Produzioni",
nameInSheet: 'Prod',
entity: "sensor.daily_energy_fv"
},
{
sheetName: "Consumi",
nameInSheet: 'Cons',
entity: "sensor.daily_energy_totale"
}
]
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
Ok. I think i know where is the problem. There was a part in twilio tutorial that you probably missed. Create spreadsheet and share it.
Find the client_email inside client_secret.json. Back in your spreadsheet, click the Share button in the top right, and paste the client email into the People field to give it edit rights. Hit Send.
If you skip this step, you’ll get a gspread.exceptions.SpreadsheetNotFound error when you try to access the spreadsheet from Python.
Don’t forget to change spreadsheet name to one you created in apps.yaml
But produzioni is name of a worksheet? In you apps.yaml name of spreadsheet is SpreadSheetName: “Hass to google sheets”. So did you created “Hass to google sheets” in gsheets?
Yes, I do.
It is shown an error on API, I want to try again from the start and verify all the passages.
I found the problem; I got a API for Google Drive, as shown in the guide; but I needed a API for Google Sheets…
Now it works!
is there a repo for this modification so as to add the addon ?
Hi, I’m having trouble that after seven days it seems, the writing to the Google sheets stop working, and I get this error:
2021-04-14 09:11:27 DEBUG (MainThread) [homeassistant.components.shell_command] Stderr of command: python3 /config/includes/shell_scripts/gsheets_write_endtrip.py {{sensor01}} {{sensor02}} {{sensor03}} {{sensor04}} {{sensor05}} {{sensor06}} {{sensor07}}, return code: 1:
b’Traceback (most recent call last):\n File “/config/includes/shell_scripts/gsheets_write_endtrip.py”, line 76, in \n credentials = get_credentials()\n File “/config/includes/shell_scripts/gsheets_write_endtrip.py”, line 69, in get_credentials\n if flags:\nNameError: name ‘flags’ is not defined\n’
Where can I start to dig why credentials don’t work longer?
Anyone have any ideas why access goes away after 7 days?
I’ve solved this by publishing a web hook with pipedream, creating an automation in pipedream to connect the webhook to a spreadsheet and creating an automation in home assistant.
Requirements:
- pipe dream
- RESTful Command - Home Assistant
- automation
took me 15 min to setup and test. works fine, and maybe a bit faster than IFTTT, and less restrictions on the free plan.