send sensor data from Home Assistant to a spreadsheet

The Home Assistant dashboard (aka ‘Lovelace’) is very good at displaying live sensor data and sensor data from the last several days. As you can see below you can display a ‘just now’ reading or a continuous line graph of a sensor’s value over say, the last few days. However, in the case of my oil tank level readings I wanted to record and keep the daily oil level over a longer period of time. Firstly I wanted to be able to predict when I’d need to order more oil. Secondly, I wanted to see whether my oil use could be related to the outside and inside temperatures. I decided the thing to do was to send daily readings to a spreadsheet and analyse them later.

Examples of sensor displays on the Home Assistant dashboard

1. Use IFTTT

Here’s how I recorded data from my Home Assistant sensor called ‘oil_level’ plus two temperature sensor readings, in a Google Sheet.

Go to IFTTT, sign in and add the Webhooks service and the Google Sheets service. For the official docs for Home Assistant IFTTT follow the link

2. Get your key from the Webhooks service

Go to IFTTT > My services > Webhooks > settings and copy the key from the webhooks URL.

# Add the key, the string of characters found in the URL, to your Home Assistant configuration.yaml

ifttt:
  key: 6XASTRINGOFCHARACTERS
# key: !secret iftttkey

3: Create an IFTTT applet ‘IF Webhook THEN Google Sheets’

In the Google Sheets section of the IFTTT applet set the name and folder for the spreadsheet where you’ll store the data

4: Write an ‘automation’

In Home Assistant create an automation that triggers IFTTT once a day and sends the sensor data to the spreadsheet. Don’t use the built-in automation editor – instead write it with a text editor: create a text file (eg ‘send_oil_level.yaml’) in the Hassio config folder called ‘automations’.

id: '12399999'
# the id must be unique - the alias and description are for your reference
alias: oil_level_spreadsheet
description: oil daily readings in oil_level.yaml
# I want a daily oil level reading so the trigger I use is a time
trigger:
at: 06:30:00
platform: time
# The condition offers ways to limit when we'll send oil sensor data. eg don't bother in summer
condition: []
# The ifttt trigger service requires a data template. "event" and "value1" etc ought to match what your IFTTT app is expecting to receive. I suggest you copy and paste this.
action:
data_template: { "event": "oil_level", "value1": "{{ states('sensor.oil_level')}}", "value2": "{{ states('sensor.weather_temperature')}}", "value3": "{{ states('sensor.loose_temperature')}}" }
service: ifttt.trigger

To recap this code is in a text file named ‘send_oil_level.yaml’ which is in the Hassio config folder called ‘automations’. Check also that your main configuration file (called ‘configuration.yaml’) has the following sections:

automation: !include automations.yaml
automation old: !include_dir_merge_list automations

Cross fingers and go see your spreadsheet

Remind me to update my progress here.

2 Responses

  1. Jose Petri says:

    Hi! I would like to do this in my Home Assistant, but I have tried this and doesn’t work. Google’s driver spreadsheet does not change. I think could be the file yaml, some spaces, or formatting issues. Could you share your file or send a print of your yaml files? Thank you for share.

    • roger says:

      Sorry to hear this Jose. It is frustrating not being able to locate the cause of the failure when there are so many places to fail.
      Getting the code below correct took ages – perhaps this is where it’s gone wrong. Setting up the webhooks part was hard but it was the easier bit!

      This code is the entire content of a plain text file called oil_level.yaml which resides in the automations folder. I found that this automation code would not work in the automations tool.

      – id: ‘12399999’
      alias: oil_level_spreadsheet
      description: daily readings in oil_level.yaml
      trigger:
      – at: “23:55:00”
      platform: time
      condition: []
      action:
      – data_template: { “event”: “oil_level”, “value1”: “{{ states(‘sensor.oil_level’)}}”, “value2”: “{{ states(‘sensor.temperature_weather’)}}”, “value3”: “{{ states(‘sensor.power_today’)}}” }
      service: ifttt.trigger

Leave a Reply

Your email address will not be published. Required fields are marked *