Read from Google Sheets API

Configuration
1

Hi there, i’m an happy new user of HA on rpi3.
I spent some time for setup 5 dht11 on another rpi2 that write temp and humidity on google sheet and give back a web interface with graphs and button for control my home temperature, then i discovered HA!
I tried to setup dht sensor but seems not work, values are half then real.
So i want to read sensor from my Google Sheet from api, but i get this:

{
“range”: “Statistiche!J2”,
“majorDimension”: “ROWS”,
“values”: [
[
“18”
]
]
}

Values is in array format and in HA i get this:

Schermata 2021-02-27 alle 10.03.13
Schermata 2021-02-27 alle 10.03.131804×176 27.7 KB

This is my config, please help me!!

sensor:
#Aggiorna temperatura casa da GoogleDrive (temppi)

2

What value return when you put the resource URL directly in a browser?

By other hand, maybe can be better you learn to use mqtt, is perfect for this.

3

i found a workaround using command line!

Maybe can be helpful for other, i really love HA!!

1 Like
4

This worked for me as well. Had the exact same journey as you. Couldn’t get the rest stuff to work but curl is fine! Your json parsing was cleaner than my value template so thanks for that!

5

Hi!

Did anyone else got this working? I tried it but somehow I don’t get it to work.

What I did was in Google Sheets go to the cell I want the value from and select “get link to this cel”.

Then I copied the code from this post and only changed the link. (I put this in my sensor.yaml)

  - platform: command_line
    command: curl -sS https://docs.google.com/spreadsheets/d/1GijbFJyz2######wl9WxgYnNtq5Aulgi7e9_JrSQ/edit#gid=1164846508&range=N20 | jq -r ‘.values[]’ | jq -r ‘.[]’
    name: TESTTEMP2
    unit_of_measurement: “°C”

Am I doing something wrong?

6

What output or error do you get?

7

Sorry for the dumb question, but where can I find this?

8

If the sensor is producing an output, you can see it in Developer Tools → States by clicking on the sensor.

If it produces an error, you should be able to find that in Settings → System → Logs (assuming you’re on the most recent version of HA).

9

It gives a state “unknown” and in the Logs (yes running latest) I can’t find the sensor.

Do you have this command running?

10

For those trying and failing the method above.

If you are a Node Red user, you can also follow this: How to read and write data to a Google sheet in node-red (flow) - Node-RED

I’ve done this and it works.

11

If anyone got stuck with the Command Line solution, this is what worked for me. :grinning:

command_line:

1 Like
12

Hello, I need a hand, I am a beginner in Home assistant.
I just followed your tutorial. Thank you because I am in the same boat as you I have been looking for several days
how to do it and I see that you know how to do it and I think I am not far but I am missing something.

here is the result of my Json: so it works
{
“range”: “Alimentaire!B2”,
“majorDimension”: “ROWS”,
“values”: [
[
“48”
]
]
}
dans HA j’ai cela