Google Sheets integration - write slimmelezer data to column E or F - HowTo?

[ipanini (Erwin Panen)]

Hi,
I’m using slimmelezer from Marcel Zuidwijk.
Following the youtube guide of Smart Home Junkie, I have previously managed to write hourly values of my Gas Consumed Belgium (sensor) to a Google Sheet.
No problems up to here.

I now wanted to add the hourly values of my sensor.energy_consumed_tariff_1 and sensor.energy_consumed_tariff_2 to the same Google Sheet.

I have got this working now, but I had to use an additional worksheet in the same Google Spreadsheet, so in effect, I had to set up two automations that each write to their own worksheet.

Here’s the first automation:

alias: schrijf kwartier verbruik - tariff 1
description: PanE beschrijving
triggers:
  - trigger: time_pattern
    hours: /1
    minutes: /15
conditions: []
actions:
  - action: google_sheets.append_sheet
    metadata: {}
    data:
      worksheet: Elektriciteit
      config_entry: 01K_my_config_entry_GM3
      data-1:
        Device: Energy Consumed Tariff 1
        Entity ID: sensor.energy_consumed_tariff_1
        Energy consumed: "{{ states('sensor.energy_consumed_tariff_1') }}"
mode: single

And here’s the second automation:

alias: schrijf kwartier verbruik - tariff 2
description: PanE beschrijving-2
triggers:
  - trigger: time_pattern
    hours: /1
    minutes: /15
conditions: []
actions:
  - action: google_sheets.append_sheet
    data:
      worksheet: Energy consumed tariff-2
      config_entry: 01K_my_config_entry_GM3
      data:
        Device: Energy consumed Tariff 2
        Entity ID: sensor.energy_consumed_tariff_2
        Energy Consumed: "{{ states('sensor.energy_consumed_tariff_2') }}"
mode: single

Question:

1. How would I need to adapt my automation, in order to write sensor data to a particular column?
   So for example:
   sensor.energy_consumed_tariff_1 => write to => column E
   sensor.energy_consumed_tariff_2 => write to => column F

2. How would I go about to also do a worksheet calculation within the same sheet?
   Background:
   When I set up the Gas Consumed Belgium automation, the sensor data are written to column D (Gas consumed).
   I then wanted to do =(D4-D3) as a calculation in the same sheet.
   So I did this and copied the same cell formula down in the next 100 rows.
   But the result was that the Google Sheet automation skipped the next 100 rows and only started writing in row 101.

So how can I go about to have this formula (=D4-D3) also added by the Google Sheet automation? (and of course, have the automation incrementally adjust (=D4-D3) / (=D5-D4) etc.

Thanks for helping out!

[Didgeridrew]

@ipanini

How would I need to adapt my automation, in order to write sensor data to a particular column?

The column the data is placed in is based on the key you are using for the data in the action. So, the value for Device goes in the column with “Device” in the first row. You need unique keys like Device 1 and Device 2 in the first row of the sheet at whatever column you want them to fill.

@ipanini

So I did this and copied the same cell formula down in the next 100 rows.
But the result was that the Google Sheet automation skipped the next 100 rows and only started writing in row 101.

Instead of copying the formula down, only place the formula in the top data cell and use the MAP function. Since you are subtracting data that won’t be there for the last row, you’ll need to include either default values or something like IFERROR

[ipanini (Erwin Panen)]

Thanks for replying;
I’ll need to dig into this, and will reply if I get it to work or need further help!

[ipanini (Erwin Panen)]

@Didgeridrew

The column the data is placed in is based on the key you are using for the data in the action. So, the value for Device goes in the column with “Device” in the first row. You need unique keys like Device 1 and Device 2 in the first row of the sheet at whatever column you want them to fill.

First of all thanks for helping out!

Not entirely sure if I understand this.
Nevertheless I found some example somewhere (but that was rather incomplete) that pointed in the same direction.

I will need to do more learning, and will report back here.

If by any chance you have a good pointer to relevant example or learning code, that would be most helpful!

[Didgeridrew]

@ipanini

So how can I go about to have this formula (=D4-D3) also added by the Google Sheet automation?

Just to clarify, the formula does not go in the automation, you just put it in the sheet in row 2 of whatever column you want. Here’s an example where I get the delta from 2 columns:

The formula is as follows:

=MAP(D2:D, B2:B, LAMBDA(high, low, IF(ISBLANK(high), IFERROR(0/0,),  high-low  )))

@ipanini

Not entirely sure if I inderstand this.

If you look at the screenshot above, you will see that row 1 contains headings like “Lowest Temp” and “Highest Temp”:

Those correspond to the data keys in this action

action: google_sheets.append_sheet
data:
  config_entry: *******
  worksheet: Temp/Hum Statistics
  data:
    Average Temp: |
      {{ states('sensor.average_non_basement_temp') }}
    Coolest Room: >
      {{ area_name(state_attr('sensor.non_basement_minimum_temperature','min_entity_id')) }}
    Lowest Temp: |
      {{ states('sensor.non_basement_minimum_temperature') }}
    Hottest Room: >
      {{ area_name(state_attr('sensor.non_basement_max_temperature','max_entity_id')) }}
    Highest Temp: |
      {{ states('sensor.non_basement_max_temperature') }}
    Lowest Humidity: |
      {{ states('sensor.non_basement_minimum_humidity') }}
    Highest Humidity: |
      {{ states('sensor.non_basement_max_humidity') }}
    Outside Temp: |
      {{ states('sensor.outdoor_thermometer_group') }}
    Outside Humidity: |
      {{ states('sensor.outdoor_humidity_sensors') }}

The action doesn’t care what order they’re in or what column they’re in, it just fills the new row matching the data key to the column heading. So if you want the 2 tariff consumption values posted to the same row of the same sheet, they need to have unique names…

alias: schrijf kwartier verbruik - tariff 1 and 2
description: PanE beschrijving
triggers:
  - trigger: time_pattern
    hours: /1
    minutes: /15
conditions: []
actions:
  - action: google_sheets.append_sheet
    metadata: {}
    data:
      worksheet: Elektriciteit
      config_entry: 01K_my_config_entry_GM3
      data:
        Energy Consumed T1: "{{ states('sensor.energy_consumed_tariff_1') }}"
        Energy Consumed T2: "{{ states('sensor.energy_consumed_tariff_2') }}"
mode: single

… and you need to write those names in your sheet in row 1 of the column you want each value to be posted into.

[ipanini (Erwin Panen)]

@Didgeridrew

The column the data is placed in is based on the key you are using for the data in the action. So, the value for Device goes in the column with “Device” in the first row. You need unique keys like Device 1 and Device 2 in the first row of the sheet at whatever column you want them to fill.

Not entirely sure if this was what I saw while searching, but at least this seems very relevant. This is from the Google Sheets integration’s example page ACTION: APPEND SHEET (I can’t seem to link to the code part specifically, that’s why I copy/paste the code below)

# Example action with multiple rows
action: google_sheets.append_sheet
data:
  config_entry: 1b4a46c6cba0677bbfb5a8c53e8618b0
  worksheet: "Car Charging"
  data:
    - Item: "Car 1 cost"
      Cost: "{{ states('input_number.car_1_charging_cost')|float(0) }}"
    - Item: "Car 2 cost"
      Cost: "{{ states('input_number.car_2_charging_cost')|float(0) }}"

I assume I was thinking of Item “Car 1 cost” as similar to “sensor.energy_consumed_tariff_1”

[Didgeridrew]

@ipanini

I assume I was thinking of Item “Car 1 cost” as similar to “sensor.energy_consumed_tariff_1”

Okay… I would just have them both on the same row, but if that’s how you want the data structured, it should work… but it might make doing calculations on the same sheet more complicated.

[ipanini (Erwin Panen)]

@Didgeridrew

and you need to write those names in row 1 of the proper columns.

Hmmm, thanks for sticking with me!
I’m not sure if we were cross posting or that I just missed the last part of your answer.

I just noticed now that you in fact adjusted my code so that it would appear in a single sheet.
In the mean time I have set up an additional new automation and copy / pasted your code in it + adjusted my config_entry code. I also adjusted the code to write to an additional sheet named Didgeridrew (I like your alias )
And I can report that this totally works!! Thanks a ton!!

Now to be totally clear:
It was my understanding that it is/was the HA automation that also did the writing / naming of the Google Sheet column header, not even sure they are called column headers, but they shape the content of row 1.

It was not totally clear whether the automation writes the first row or whether it would be me to adjust the first row to contain the content corresponding tot the automation.

I will now further focus on your MAP + formula explanation and see if I can get that going.

Not specifically for this automation, but my idea would be to get HA sensor data in to influxDB and Grafana. This would then be more focused on registring solar PV data. I’d like to set up a test using a couple of PV panels and just put them at manually picked locations on our property to then see if this can help determine the best locations to install more PV (but not on the roof of the house).

Let me know where and how I can buy you a beer

[Didgeridrew]

@ipanini

It was not totally clear whether the automation writes the first row or whether it would be me to adjust the first row to contain the content corresponding tot the automation.

It does on the first run of the action, if you haven’t already manually added the headers, or if there are already other headers but none that match the data keys used in the action.

As long as the data keys have a column with a matching header, they just add the values to the first empty row.

[ipanini (Erwin Panen)]

@Didgeridrew

It does on the first run of the action, if you haven’t already manually added the headers, or if there are already other headers but none that match the data keys used in the action.

As long as the data keys have a column with a matching header, they just add the values to the first empty row.

Ah! Thanks a ton for explaining!

[ipanini (Erwin Panen)]

@Didgeridrew

Okay… I would just have them both on the same row, but if that’s how you want the data structured, it should work… but it might make doing calculations on the same sheet more complicated.

Yes, indeed, I want them both on the same row, but since I had something wrong in my config, and could not get it to work correctly, I divided the code in two different automations (but those can now be eliminated since you helped to make it work in a single automation)

[IOT7712]

My head hurts!
Are you sure that using home automation to find power meter data (already in HomeAssistant format), insert it into a remote spreadsheet being used as a database, and then running macros and formulas on that spreadsheet may be the most effective way to accomplish your goal?
HomeAssistant already has a database and you can manipulate and calculate derivative values as the data arrives, and then use that for statistical and display purposes.
Not sure why Google sheets comes into it at all, other than for the extended and convoluted mental gymnastics.

[Didgeridrew]

@IOT7712

My head hurts!..

Not sure why Google sheets comes into it at all, other than for the extended and convoluted mental gymnastics.

Not sure why OP’s question merited such a histrionic response…

There are a number of integrations that do essentially the same thing, passing data from HA to some other form of database. If OP wants to try Google Sheets or InfluxDB instead of doing something in HA, there’s no reason to chastise them.

I don’t know OP’s reason for using the Google Sheets integration, but I set it up on my instance because other people in my household are experienced and comfortable using Sheets. Letting them access and use the data for their purposes, in a way they already understand, is an easy way show the utility of HA and to get buy-in. And, it has the benefit that they can delete or otherwise modify data without having any effect on the HA database.