Google sheets attributes string/text issue?

Configuration
1

ok, maybe I have found a bug or maybe I am missing something and would appreciate in that case to help find my error… or otherwise document a solution(?)

issue:
I am trying to get a long text attribute to write into the google sheets integration and not having any luck

solution?
to get my attribute which is a list into google sheets I had to escape it by including a
'"' on either side of the code. Presumably this is to escape the ' within the attribute

==========================================

background and more info
I am testing vs the template editor and for example this works there but NOT in google sheets integration, eg:

       {{ states.sensor.sensor_alarm_consolidation_via_trigger_template.attributes.critical_short }}

whereas google sheets yaml:

action: google_sheets.append_sheet
metadata: {}
data:
  config_entry: 01J... redacted.... R
  data:
    - detail3: >-
        {{ states.sensor.sensor_alarm_consolidation_via_trigger_template.attributes.critical_short }}

in trying to sort this what was most frustrating was the fact that if I configure instead this:

- detail: "{{ states.sensor.sensor_alarm_consolidation_via_trigger_template}}"
  detail2: "{{ states.sensor.sensor_alarm_consolidation_via_trigger_template.attributes.all_count}}"

it outputs, whereas “detail” gives EVERYTHING!? including the attribute I want… :

<template TemplateState(<state sensor.sensor_alarm_consolidation_via_trigger_template=2; detail=[('sensor.temperature_humidity_1_basement_humidity', 66.4, 'critical', 'ity_1_basement_humid'), ('binary_sensor.pir_motion_sensor_1_basement_motion', 'on', 'critical', 'ensor_1_basement_mot'), ('sensor.mold_indicator_basement', 68.0, 'warning', 'mold_indicator_basem')], critical=['sensor.temperature_humidity_1_basement_humidity', 'binary_sensor.pir_motion_sensor_1_basement_motion'], warning=['sensor.mold_indicator_basement'], all_count=3, critical_short=['ity_1_basement_humid', 'ensor_1_basement_mot'], friendly_name=sensor alarm consolidation via trigger template @ 2025-02-26T11:10:00.295297+01:00>)>

“detail2” gives a result meaning one can get attributes! :

2

great… … … so maybe it is because it is having trouble with text/strings!?

solution?
so I tried a bunch of things but found out if i put quotes escaped like this it works (I have tried to include type switches eg " | string " but it has not worked so far ):

detail3: >- 
       '"'{{ states.sensor.sensor_alarm_consolidation_via_trigger_template.attributes.critical_short }}'"'

while again, this does NOT work (unknown error)

detail3: >- 
       {{ states.sensor.sensor_alarm_consolidation_via_trigger_template.attributes.critical_short }}

is this a bug? or is there some other way one is supposed to do this? (if so, can someone please put it in the integration page for google sheets! , here Google Sheets - Home Assistant)

2

Do you want the list printed into Sheets as a string? You can use the join() filter with you preferred separator to change a list into a string:

{% set ent = 'sensor.sensor_alarm_consolidation_via_trigger_template' %}
{{ state_attr(ent ,'critical_short') | join(', ') }}

For more complicated objects you can use to_json to convert them to a json string.

Using the state object method without a property name returns the whole State Object.

You may also want to review the use of the state object methods vs. states() functions:

Home Assistant Docs - Templating - States

3

thanks! I thought maybe the sheets would account for the single quotes in strings sent, but I guess not. My surprise at “detail” giving everything as I thought maybe it hit a character limit which it apparently did not - was on my mind as I recently had run into that limitation that the state object will not output more than 255 characters.

I guess I am not the first to be flummoxed by this behavior would it be possible to just add a comment that strings within string passed to google sheets should be taken care not to include quotes or escape hem as I suggested ‘"’ on either end of it as I show above… or we just let them hopefully find this thread … it would have saved me lots of time!