Google Sheets - Unknown error

Configuration
1

First attempt at using the Google Sheets integration is generating this error:

image
image878×415 5.72 KB

The integration appears to be working as it is populating the headers in the Googlesheet :

image
image714×512 19.7 KB

… but no data. The ymal is below:

service: google_sheets.append_sheet
data:
  config_entry: b8071a942d422e1b2b68dc0c48b81688
  worksheet: Heating
  data:
      exterior_temp: {{(states('sensor.davis_vantage_pro_air_temp_outside') | float())}}
      device: exterior_temp

The log derail (error) returns many lines of code and ends with this:

gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid values[1][1]: struct_value {\n  fields {\n    key: "device"\n    value {\n      string_value: "exterior_temp"\n    }\n  }\n  fields {\n    key: "temperature"\n    value {\n      struct_value {\n        fields {\n          key: "[object Object]"\n          value {\n            null_value: NULL_VALUE\n          }\n        }\n      }\n    }\n  }\n}\n', 'status': 'INVALID_ARGUMENT'}

Thoughts?

2

It seems there is a indentation error. Exterior_temp is indented four steps. Not sure that is the issue, but then there is a template that is not enclosed in " ".

I also use sheets to long term save my heating.
I trigger on TRV temperature attribute and use this yaml in action:

  - service: google_sheets.append_sheet
    data:
      config_entry: 927e50ef6b3c6946d426cfd41c7d5de3
      worksheet: >-
        {{ (trigger.to_state.entity_id | replace("climate.", "")|
        replace("_thermostat", ""))[0:5] | upper ~ (trigger.to_state.entity_id |
        replace("climate.", "")| replace("_thermostat", "")|
        replace("kok","kök"))[5:] }}
      data:
        room_temp: >-
          {{ state_attr(trigger.to_state.entity_id | replace("trv_", "") |
          replace("_thermostat", "") | replace("kok", "vardagsrum"),
          "current_temperature") }}
        set_temp: >-
          {{ state_attr(trigger.to_state.entity_id | replace("trv_", "") |
          replace("_thermostat", "") | replace("kok", "vardagsrum"),
          "temperature") }}
        set_temp_TRV: "{{ trigger.to_state.attributes.temperature }}"
        outside_temp: "{{ state_attr('weather.smhi_home', 'temperature') }}"
        wind_speed: "{{ state_attr('weather.smhi_home', 'wind_speed') | float / 3.6 }}"
        wind_bearing: "{{ state_attr('weather.smhi_home', 'wind_bearing') }}"
        weather: "{{ states('weather.smhi_home') }}"
        open_doors/windows: >-
          {{ 'none' if expand('group.doors_and_windows') | selectattr('state',
          'eq', 'on') | list | map(attribute='entity_id') | join(', ') == ''
          else (expand('group.doors_and_windows') | selectattr('state', 'eq',
          'on') | list | map(attribute='entity_id') | join(',
          ')).replace('binary_sensor.', '').replace('_', ' ') }}

Remember to make the automation queued since the write takes a few seconds to do.

1 Like
3

Thanks - Applied both suggestions and it works!