Json templating

Configuration
1

Hi

I’m trying to import some data from Google sheets into homeassistant. I can use the Google API to get a JSON output that looks like this:

{
  "range": "Sheet1!A1:I23",
  "majorDimension": "ROWS",
  "values": [
[
  "Air temperature (°C)",
  "–",
  "–",
  "–",
  "–",
  "32",
  "33",
  "27",
  "21"
],
[
  "Feels like (°C)",
  "–",
  "–",
  "–",
  "–",
  "30",
  "29",
  "25",
  "20"
],
]
}

I’m using a command line sensor to try and pull this:

  - platform: command_line
    command: python3 -c "import requests; print(requests.get('https://sheets.googleapis.com/v4/spreadsheets/1y1YwKwlSvIf4XkqrKkTGsA-6m4DgdVHub91m9ySIzro/values/Sheet1!A7:I7?key=!SECRETAPIKEY).json()['values'])"
    name: File value

and I successfully get the whole string as a sensor.

What I’m struggling with (due to my lack of json knowledge) is how to template this just to get a specific value. E.g. if i wanted the last temp value ‘21’ how would I break this apart?

Any help would be awesome.

thanks

2

Hi. Could you try formatting the JSON again. (If you select the text and click on the “Preformatted text” button like it shows at the top of the page, that only works if you have a blank line before the selected test. As you can see, it did not format correctly, which makes it a bit hard to read.

So I think “values” contains a list of lists. If you want the last item in the last list, then you could change:

...).json()['values'])"

to:

...).json()['values'][-1][-1])"

Or if you want the last item in the first list, then:

...).json()['values'][0][-1])"
3

Thanks that worked perfectly! For my own knowledge/learning is there some kind of doc/help manual that explains how you should work out the numbers in square bracket relative to where it is in the list?

4

Well, this is just the Python language, so:

https://docs.python.org/3/

But basically, indexing starts at zero, so [0] references the first element, [1] references the second element, etc. Negative indices mean from the end, where [-1] is the last element, [-2] is the next to last, etc.

5

Thanks, super helpful