Get data from google sheet into HA

Third party integrations
1

Hi,

I’ve got some data in a google spreadsheet that I would like to use as a sensor or entity in HA. Googling made me find this: Reading & Writing Cell Values  |  Sheets API  |  Google Developers

But I get stuck there… What can I do?

2

Too may API’s in my life… that said, I think one of these two examples is the project (1st more likely) I used to get my start on reading from Google Sheets maybe 3 years ago. And I still have a python app running that uses this basic frame work to query a Google sheet 7 by 24 every 5 minutes in a docker container. Hope this helps, good hunting!

https://erikrood.com/Posts/py_gsheets.html

3

Did you ever figure this out? I’m trying to do the same thing.

4

I was also looking for a solution for this.

I decided to use a REST sensor in Home Assistant to fetch data from a spreadsheet using an Apps Script end-point (Webapp). Works quite well. Though it helps to be somewhat comfortable with Google Apps Script.

5

Can you show me how to make AppScript ? Thanks

6

I would love that too…

7

I find in youtube have some tutorials to create json url from google sheet.
Now I’m using appscript to create json url and make python script so that I can make sensor with data extract via command line platform

8

There seems to be a lot of info on how to write data to a google sheets spreadsheet using the Google sheets integration, but very little that is conclusive in how to read a value from a sheet.

Can anyone help here? Basically I just need to know how to read back a cell numeric value written in the Home Assistant sheet into an entity I can view.

1 Like
9

I think I’m getting somewhere, in the REST call, I’m using:

platform: rest
name: test
resource: "https://docs.google.com/v4/spreadsheets//values/a1?key=

The state output is:

{ “range”: “HomeAssistant!A1”, “majorDimension”: “ROWS”, “values”: [ [ “£0.00” ] ]}

then I tried value_template,

platform: rest
name: test
resource: "https://docs.google.com/v4/spreadsheets//values/a1?key=
value_template: “{{value_json.values }}”

but got the following state:

<built-in method values of dict object at 0x7f62573440>

I suspect it’s because it’s a pointer, and not the actual value