Run automation on specific dates that exists in gooogle spreadsheet

Configuration
1

I am trying to set up light switches that depend on if there is an event or not. I have a Google Spreadsheet with info about events and dates. This is an example:
image
If there is an event today, then light 1 should be turned on at sunset. And if Count > 50, so should all lights.
I have sorted out this manually today by entering if there is an event today and entered a number, but my goal is to automate further.

I made a python script that reads the spreadsheet, but how should I use this to populate variables in HA? And how should I use the variables in my templates?

This is my automation today that works manually:

- id: '1600676065786'
  alias: Turn on at sunset if event
  description: ''
  trigger:
  - event: sunset
    platform: sun
  condition:
  - condition: state
    entity_id: input_boolean.event_today
    state: 'on'
  action:
  - service: switch.turn_on
    data_template:
      entity_id: "{% if states('input_number.event_count') | int > 50 %}\n  group.alll\n\
        {% else %}\n  switch.switch1\n{% endif %}      \n"
  mode: single

I also noted that running my python script is not that fast and with poor CPU I get a timeout. It might be good to run a CRON-job reading the spreadsheet and either save it as CSV or enter the values with HA API.

Does anyone have any ideas?

2

Now I have managed to get this to work.

I thought of/tested python_scripts and pyscript, but I realized the Google API I needed was not loaded correctly in pyscript.

So, what I did was this:

  • Created a python script to run as command_line. All of the logic is in this script. I loop through rows, first column, until i find today’s date. Then I get the values I need from the other columns. I also filled a variable for next event to show whats upcoming. The output from the script I dump as JSON.

  • Created a command_line sensor in HA that runs the python script and receives the JSON. I have json_attributes for all of the parameters i need.

  • Created template sensors from the command_line sensor attributes to be able to use them with fewer lines.

  • In the original automation above, I changed the input_boolean to the sensors i just created.

Works like a charm :slight_smile: But took me a month of figuring out because I’m new to python.