send sensor data from Home Assistant to a spreadsheet

The Home Assistant dashboard (aka ‘Lovelace’) is very good at displaying live sensor data as well as historic sensor data. The display below shows a ‘just now’ reading of the sun and also a continuous line graph of ‘sun azimuth’ over the last few days.

However, to understand my heating oil usage, I wanted to record the daily oil level over a longer period of time. (My sensor hardware is described here: oil tank level sensor)

Firstly I wanted to be able to predict when I’d need to order more heating oil. Secondly, I wondered whether my oil use could be related to the outside temperatures. So I decided that the thing to do was to send daily level and temperature readings to a spreadsheet to analyse later. This page describes how I sent data from my Home Assistant sensors called ‘oil_level’ and ‘weather temperature’ in a Google Sheet.

Examples of sensor displays on the Home Assistant dashboard

1. Get IFTTT to put the data I send into a Google sheet

Go to IFTTT, sign in and add the Webhooks service and the Google Sheets service. Follow this link if you need the official docs for Home Assistant IFTTT

2. Get a ‘key’ from the Webhooks service

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

3. Add the key (the string of characters found in the URL) to your Home Assistant configuration.yaml

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

4: 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 as follows:

5: Write an ‘automation’ as a yaml file

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 use a text editor to create a file called send_oil_level.yaml and saved in the Hassio config folder called ‘automations’. I suggest you copy and paste this editing just the bold text. The event name below must match the name above at IFTTT

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 configuration file named configuration.yaml has the following sections:

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

6. Cross fingers and go see your spreadsheet

8. Find a way to split the date time March 4, 2020 at 07:15AM

Columns A to E of the spreadsheet need to be left for IFTTT and Google Sheets to update whenever the automation runs. You can format them with colours or whatever – the bottom line stores the incoming data.

Use Column E rightwards when you want to do your calculations. Below I’ve put a split function in cell H2 and copied this down the column. The split function will break up a string of text that has a obvious separator. The obvious separator in ‘March 4, 2020 at 07:15AM’ is a space.

That’s why cell H2 has =split(A2, ” “) The formula sucks out the spaces from what’s in A2 and puts the individual elements in columns to the right of it. Pretty cool function that one.

6 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

  2. Daniel Cagarrinho says:

    Hello Roger, thanks allot for this tutorial 🙂
    Runs perfect for me, the only thing I’m missing is already on the spreadsheet is formatting the date as you have in date split and if its possible the time in 24h

  3. Luca says:

    Hi,
    thank you for your work. Is it possible to retrieve data from Google Home to Google Sheets without Raspberry or similar? If I have undertand correctly your method require hardware. Am I wrong?

    Best regards
    Luca

    • roger says:

      Thank you for writing in. I wouldn’t say the data in Google Home isn’t retrievable as ‘developers.google.com’ offers a way that perhaps someone is trying. Please lets know.
      But yes there is a ‘non-hardware’ way for you. It requires installing Home Assistant on a virtual machine on your PC, even an old PC. In this way any activity or temperature reading (etc) is recorded by HA or sent to a spreadsheet as on this page – instead of disappearing into Google Home.

      The link below, and some Youtubes will guide you through that. Almost all the data I’m using now comes from a Home Assistant. My version runs on a Raspberry Pi connected by ethernet to the broadband router. Yours can run on a PC just as well or better … with the downside of it being ‘off’ when you PC is ‘off’.

      All best,

      RF

      https://www.home-assistant.io/installation/

Leave a Reply

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