Display Google Sheet data in a Dashboard

[Laurynas]

Hello! I am trying to pull data from Google Sheets and display it in a Dashboard. For this, I am trying to set up a new restful integration to the Google Sheets api v4. However, I really don’t understand how the restful integration works, especially with Google’s OAuth2 authentication, maybe someone could help? Here’s what I have in my configuration.yaml so far:

rest:
  - authentication: basic
    username: !secret sheets_cliend_id
    password: !secret sheets_client_secret
    scan_interval: 60
    resource: https://sheets.googleapis.com/v4/spreadsheets/[google_sheets_id]?ranges=A2%3AG5
    sensor:
      - name: "Body Composition"
        json_attributes_path: "$.response.sheets"
        value_template: "OK"
        json_attributes:
          - "timestamp"
          - "weight"
          - "body fat %"

But I suspect not even my authentication is correct here. So a couple quuestions:

1. How can I test and debug this (see some kind of error in some console?)
2. Does restful integration support OAuth2?
3. What else can I improve in the yaml sample I provided to get this data extraction from Google Sheets working? Or is there a better way to do this?

Thanks for reading, and please be patient It’s my first post here

[buzzeddesign]

I’m trying to do what you’re doing as well! This post might contain what we need, but haven’t attempted to make this work for OAuth with Google Sheets API yet

Rest sensor - Dynamic Bearer Token (Using Sensor)

So actually found a workaround by not using secrets. The issue is the token bearer is being refresh every time the home assistant starts, and when running the query to get the prices, the “!secret api_key_bearer_static” is equals to “unknown” My solution is: Create helpers as input text where you will store your API key (static) and you bearer key (dynamic) and in my case some static authentication header Store your bearer token from the API in a sensor Create an automation that every time t…

[Laurynas]

I wondered if I can solve this challenge by creating a “python sensor” - a python script would connect to the google sheet and do the authentication, extract and transform the data I need. Then simply produce the needed values and HA would pick it up as a sensor. I will post the script when I’ve had time to try it out, or feel free to do the same if you get to it first.

[Aleksey-nt]

My solution for analyzing scv file data. I have a google spreadsheet with access control data. I upload data in csv format, separated by commas, to the home as server. Next, I search for data in the csv file using input_text.data_search. The command line sensor searches for a term with the required data and places the result in the attributes. Perhaps this will be useful to someone.

 - sensor: 
     name: BD_scv
     command: |
      grep {{ states("input_text.data_search") }} /config/www/downloads/bd.csv | tail -n 1 | jq -Rsn ' inputs | . / "\n" | (.[] | select(length > 0) | . / ",") as $input | {"column1": $input[0],"column2": $input[1],"column3":$input[2],"column3":$input[3],"column4":$input[4]}'
     scan_interval: 2
     value_template: "{{ state_attr('sensor.bts_polnye_dannye', 'column1') }}"
     json_attributes:
       - column1
       - column2	
       - column3
       - column4

[Aleksey-nt]

And in addition, you can download a csv file from google sheets to any desired directory:

curl -L ‘https://docs.google.com/spreadsheets/d/ABCDosapdamncdssddsdaKKladclasl/export?gid=0&range=A5:W5000&exportFormat=csv’ -o config/www/downloads/bd.csv

This command works in the terminal if the source table is open for viewing.
If closed, you need to add an API access key to the link. Haven’t tried it yet.
Something like that:

curl --silent --header “Authorization: GOOGLE_LOGIN auth=AUTH_KEY” --L ‘https://docs.google.com/spreadsheets/d/ABCDosapdamncdssddsdaKKladclasl/export?gid=0&range=A5:W5000&exportFormat=csv’ -o config/www/downloads/bd.csv

You can also add feedback. Write a script in Google sheets (GAS). Which will monitor changes in the table and send a post request to the Home Assistant server. This way Home Assistant will understand whether database changes have occurred. You can write some kind of automation to re-upload csv when updating information in table cells.
Sorry for my English, I’m Russian.

[Aleksey-nt]

You can try this comand_line from google sheets

  - sensor: 
      scan_interval: 30
      name: bd sheets
      command: "curl -L 'https://docs.google.com/spreadsheets/d/jhwenfw92901203123nnm31902130/export?gid=816578980&range=A5:W5000&exportFormat=csv' | jq --raw-input '{data: [inputs | split(\",\") | {Date_of_delivery: .[0], Installation_date: .[1], Model: .[2], Power: .[4], Serial_number: .[5], MAC: .[6], IP: .[7] , Status: .[8]| gsub(\"[\\r]\"; \"\")  }] }'"
      value_template: "{{ now() }}"
      json_attributes:
        - data

You can also see examples here:

Create graph/automation based on river level

@joshualeone, I have mostly finished I will post in a separate thread and post link here. NOTE: you say “River Rat” … are you here in Guerneville or local? That is great if so.

River Flood Data from NOAA to Table and Graphical formats

I created a solution for folks whom may want river flood information in their dashboards. It all came from a question posted here: I decided that the best solution for my needs was to extract the data from NOAA posted CSV files using curl to grab the data piped to jq raw to create targeted JSON with the information I need. The below example uses my specific river monitor, you can easily change to yours. The sensor is: ## ## water.weather.gov CSV ## - platform: command_line scan_interval: 3…

You can use these examples. And adapt it to your needs.

[Dariasolv]

For the OAuth2 setup, you’ll need to create credentials in the Google Cloud Console. Make sure to set the redirect URI correctly, or it won’t work. The rest integration can support OAuth2, but you might find using a library like google-auth or google-api-python-client in Python simplifies the process, especially for handling the authentication flow.When I set up something similar, I hit a wall with the OAuth part too. I found My Excel Online super helpful for brushing up on how to format data before pulling it into my project. To debug, check your logs for any error messages, and you can also use Postman to test the API calls independently. It can really help you figure out if the issue lies with the code or the API itself.