Hello again
I’d like to make a sensor based on the value (1/0) of a cell in a Google Sheet, which changes potentially hourly. I’ve searched this forum, I promise, and tried everything I found without success.
I know my Sheets ID, my cell, my API key.
Can anyone who has successfully done this please help me out?
Many thanks in advance
Roger
Can you clarify where the API key came from? If you have a working API endpoint then you should only need to set up a REST sensor.
I actually just switched from using a Google AppScript to create an API with a REST sensor in HA. Now I’m using the, recently added, the google_sheets.get_sheet action from the Google Sheets integration.
1 Like
Oh my, that’s awesome, I hadn’t spotted the new functionality in the Google Sheets integration
So now I have the data I need coming in as a response variable. And now the dumb question - how do I ‘find’ a response variable and make it do something useful in an automation?
Thanks v much
The best thing to do is to use the Action tool to run the action. It will produce the response variable below, and there is a button to click that will copy the response in a form that you can use in the Template tool to figure out how to parse and/or modify the data to get what you need.
The structure of the data that is returned is a little weird… the response variable returns a dictionary (range) that contains a list of lists.
As an example, this sheet:
… Using the following action:
action: google_sheets.get_sheet
data:
config_entry: ***redacted***
worksheet: Sheet10
rows: 5
response_variable: sheet_data
Returns the following response in the Action tool:
range:
- - wheat
- 245,222,179
- "245"
- "222"
- "179"
- - white
- 255,255,255
- "255"
- "255"
- "255"
- - whitesmoke
- 245,245,245
- "245"
- "245"
- "245"
- - yellow
- 255,255,0
- "255"
- "255"
- "0"
- - yellowgreen
- 154,205,50
- "154"
- "205"
- "50"
From that, let’s say I wanted to get the “red” column value for yellowgreen. The template could be any of the following:
{{ sheet_data['range'][4][2] }}
{{ sheet_data['range'][-1][2] }}
{{ (sheet_data['range']|selectattr(0,'search','yellowgreen')|first)[2]}}
Sample Automation:
triggers:
- trigger: time
at: "7:00:00"
variables:
trigger_color: yellow
- trigger: time
at: "9:00:00"
variables:
trigger_color: aliceblue
- trigger: time
at: "17:00:00"
variables:
trigger_color: springgreen
conditions: []
actions:
- action: google_sheets.get_sheet
data:
config_entry: ***redacted***
worksheet: Sheet10
rows: 145
response_variable: sheet_data
- variables:
rgb: |
{{ (sheet_data['range']|selectattr(0,'search', trigger_color) | first)[1].split(',')|map('int')|list}}
- action: light.turn_on
data:
rgb: "{{ rgb }}"
target:
entity_id: light.colorful_light
# This example is kind of silly since you could just use the color name
# in the light action data, but it works with the experimental sheet I had... :)
2 Likes
You’re being really generous, and I think I’m nearly there…
So my template editor content delivers the correct output of 1 -
{% set action_response = {“range”:[[“1”,“1”,“0”,“0”,“1”]]} %}
{{ action_response[‘range’][0][1] }}
I’d like to use the fact of that 1 as the Condition in an automation. I have defined the variable
variables:
DucksNest: |
{{ action_response[‘range’][0][1] }}
What would that look like please? Thanks again
description: ""
triggers:
- trigger: time_pattern
hours: "21"
conditions: []
actions:
- action: google_sheets.get_sheet
metadata: {}
data:
config_entry: xxxxx
rows: 1
worksheet: Sheet3
response_variable: action_response
- variables:
DucksNest: |
{{ action_response['range'][0][1] }}
- action: automation.turn_on
metadata: {}
target:
entity_id: automation.dn_heater_on_0000
data: {}
mode: single
You just add a Template condition. There are a bunch of ways it could be templated, I’d just keep it simple with a comparison using ==:
description: ""
triggers:
- trigger: time_pattern
hours: "21"
conditions: []
actions:
- action: google_sheets.get_sheet
metadata: {}
data:
config_entry: xxxxx
rows: 1
worksheet: Sheet3
response_variable: action_response
- variables:
DucksNest: |
{{ action_response['range'][0][1] }}
- condition: template
value_template: "{{ DucksNest == 1 }}"
- action: automation.turn_on
metadata: {}
target:
entity_id: automation.dn_heater_on_0000
data: {}
mode: single
1 Like
Hmm. DucksNest is returning 1, but the condition value template is returning false…
{
"trace": {
"last_step": "action/2",
"run_id": "70c9605836f2cb524d2fd0a7a60cfddf",
"state": "stopped",
"script_execution": "aborted",
"timestamp": {
"start": "2025-12-25T07:04:18.813297+00:00",
"finish": "2025-12-25T07:04:21.114787+00:00"
},
"domain": "automation",
"item_id": "1766595220911",
"trigger": null,
"trace": {
"trigger": [
{
"path": "trigger",
"timestamp": "2025-12-25T07:04:18.813524+00:00",
"changed_variables": {
"this": {
"entity_id": "automation.innstyle_ha",
"state": "on",
"attributes": {
"id": "1766595220911",
"last_triggered": "2025-12-25T06:57:05.874664+00:00",
"mode": "single",
"current": 0,
"friendly_name": "InnStyle -> HA Ducks Nest"
},
"last_changed": "2025-12-25T06:57:03.469625+00:00",
"last_reported": "2025-12-25T06:57:08.301312+00:00",
"last_updated": "2025-12-25T06:57:08.301312+00:00",
"context": {
"id": "01KDA4R0AJ4N3H2TGYSPRMWZPZ",
"parent_id": "01KDA4R0AH9SZZES6MQEX0Y6D8",
"user_id": null
}
},
"trigger": {
"platform": null
}
}
}
],
"action/0": [
{
"path": "action/0",
"timestamp": "2025-12-25T07:04:18.814345+00:00",
"changed_variables": {
"context": {
"id": "01KDA5573X0JXJW5FYD503ADXZ",
"parent_id": "01KDA5573W1R8BJDH41317HQ9S",
"user_id": null
},
"action_response": {
"range": [
[
"1",
"1",
"1",
"0",
"1"
]
]
}
},
"result": {
"params": {
"domain": "google_sheets",
"service": "get_sheet",
"service_data": {
"config_entry": " *** redacted *** ",
"rows": 1,
"worksheet": "Sheet3"
},
"target": {}
},
"running_script": false
}
}
],
"action/1": [
{
"path": "action/1",
"timestamp": "2025-12-25T07:04:21.111021+00:00",
"changed_variables": {
"DucksNest": 1
}
}
],
"action/2": [
{
"path": "action/2",
"timestamp": "2025-12-25T07:04:21.111599+00:00",
"result": {
"result": false,
"entities": []
}
}
]
},
"config": {
"id": "1766595220911",
"alias": "InnStyle -> HA Ducks Nest",
"description": "",
"triggers": [
{
"trigger": "time_pattern",
"hours": "21"
}
],
"conditions": [],
"actions": [
{
"action": "google_sheets.get_sheet",
"metadata": {},
"data": {
"config_entry": " *** redacted *** ",
"rows": 1,
"worksheet": "Sheet3"
},
"response_variable": "action_response"
},
{
"variables": {
"DucksNest": "{{ action_response['range'][0][1] }}\n"
}
},
{
"condition": "template",
"value_template": "{{ DucksNest == '1' }}"
},
{
"action": "automation.turn_on",
"metadata": {},
"target": {
"entity_id": "automation.dn_heater_on_0000"
},
"data": {}
}
],
"mode": "single"
},
"blueprint_inputs": null,
"context": {
"id": "01KDA5573X0JXJW5FYD503ADXZ",
"parent_id": "01KDA5573W1R8BJDH41317HQ9S",
"user_id": null
}
},
"logbookEntries": [
{
"name": "InnStyle -> HA Ducks Nest",
"message": "triggered",
"source": null,
"entity_id": "automation.innstyle_ha",
"context_id": "01KDA5573X0JXJW5FYD503ADXZ",
"domain": "automation",
"when": 1766646258.8136961
}
]
}
The variable is an integer, and your condition template is checking if it is a string. Remove the single quotes from the template:
Should be:
value_template: "{{ DucksNest == 1 }}"
2 Likes
Awesome, it works! Thanks you so much @Didgeridrew and @mekaneck
What you’ve helped me do is very useful - I’ve made the heating in an airbnb hut come on the night before a booked guest arrives. Thanks again!