Display Google Sheet data in a Dashboard

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:

  - 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
      - name: "Body Composition"
        json_attributes_path: "$.response.sheets"
        value_template: "OK"
          - "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 :blush: It’s my first post here :see_no_evil:


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

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.

1 Like

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') }}"
       - column1
       - column2	
       - column3
       - column4

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.

1 Like

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() }}"
        - data

You can also see examples here:

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

1 Like