Creating Images of Graph Data From Home Assistant Sensors

Creating Images of Graph Data From Home Assistant Sensors

Sometimes you have a sensor that makes measurements, in my case it was measuring the temperature in the fridge using a Zigbee thermometer and you want to create an image from the historical sensor data, so you can send it as an attachment to a notification. Obviously, this technique can be applied to any sensor and for any purpose. It will create an image file of a graph containing past measurements of that sensor.

Step 1: Install Google Sheets Integration

In Home Assistant, you install the Google Sheets integration. Follow the guide here to set it up.

Step 2: Create an Automation

You create an automation to run on a timer. I set up mine to repeat every 10 minutes, but this can be any trigger. What this automation does is it sends the value of the sensor to a Google Sheets sheet.

service: google_sheets.append_sheet
data:
  config_entry: [filled in the UI of the automation]
  worksheet: Sheet1 (the name of your sheet in Google Sheets)
  data:
    Temperature: "{{states('sensor.fridge_temperature_temperature')}}"
enabled: true
    

Now you have data filling up your Google Sheets sheet. You then create a graph in Google Sheets. Any graph you want (regular Google Sheet chart using “Insert”->”Chart”).

This is my graph. Seems like the temperature is a little too high in the fridge today.

After the graph is customized to your liking, go to the top-right corner of the graph in Google Sheets, click on the options icon and select “Publish The Chart”. Select “Image” option and copy the URL it gives you. Make sure the checkbox at the bottom for “Automatically republish” is checked.

That’s it! This URL you copied is basically a link to an image. You can use that URL in notifications. I’m using it to send me a WhatsApp notification when the temperature is too high. It works great.

Important Tip: Automatically Trim Google Sheets Data

We’re copying values to a Google Sheet, and after some time it’ll fill up. There’s no need to have so many values for a graph, so decide on the maximum number of lines you want to have in your Google Sheet. To automatically trim the number of lines, go to Extensions > Apps Script, and paste the following code into Code.gs that comes by default empty:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  
  if (sheet.getLastRow() > 20) {
    sheet.deleteRow(2);
    Logger.log("deleted");
  } else {
    Logger.log("not deleted");
  }
}
    

I set mine to have 20 lines max, change that number to whatever you want. Run it once for the authorization of Google Sheets App Script (with the Run button). Then go to Triggers (on the left menu), Add Trigger, and run your myFunction function with event source = “Time Driven” and decide how often you want this function to run. Ideally, it should be the same amount of time you defined in your Home Assistant automation. So, if you transfer a value to Google Sheets every 10 minutes, you should also check this Sheet every 10 minutes to remove excess lines.

Summary

By following these steps, you can effectively log your Home Assistant sensor data to Google Sheets and create dynamic graphs to monitor your data over time. Automating the data logging and graph updating process ensures that you always have up-to-date information at your fingertips, making it easier to keep an eye on important metrics like temperatures, humidity, electricity usage and more. Additionally, setting up automatic data trimming helps maintain a clean and manageable dataset.

Found a way to do this more elegantly? Let me know!


1 Comment

Leave a Reply