Actual duration input_text to send to Google Sheet using the right template

[stepsolar]

Hi,
I created a blueprint using an input_text. If it finds “Exit,” it sends the time spent in “Entrance” to a Google Sheet file. It seemed to work, but I noticed that after selecting “Entrance” and restarting HA, the elapsed time was erased, starting from 0, and when I selected “Exit,” the elapsed time written to Google Sheet was lower than the actual value.
So I created the following sensor that tracks the time spent in entrance during the day. I should insert this sensor into my blueprint to write the correct value to Google Sheet, but I’d like something simpler to avoid creating the sensors in HA.
So, how could I write the correct formula to get the correct value for the sum of the time spent in “Entrance” when HA is restarted?
I’ll provide you with the code from my blueprint.
Thank you.

    - alias: Esegue uscita
        conditions:
          - condition: state
            entity_id: !input trigger_state_entity
            state: !input text_out
        sequence:
          - action: google_sheets.append_sheet
            metadata: {}
            data:
              add_created_column: true
              config_entry: !input device_google_sheet
              worksheet: !input sheet_name
              data:
                "{{ name_column_6 }}": >-   
                    {{ (as_timestamp(now()) - as_timestamp(states[trigger_state_entity].last_changed | default(0)) | int ) | timestamp_custom("%H:%M", false) }}

sensor:
  - platform: history_stats
    name: Total Day
    entity_id: input_text.example
    state: "out"
    type: time
    start: "{{ now().replace(hour=0, minute=0, second=0) }}"
    end: "{{ now() }}"

[Didgeridrew]

You can’t build YAML configuration with templates. To use templating to define data, construct it to output the complete dictionary:

action: google_sheets.append_sheet
metadata: {}
data:
  add_created_column: true
  config_entry: !input device_google_sheet
  worksheet: !input sheet_name
  data: |
    {% set x = as_timestamp(states[trigger_state_entity].last_changed)|default(0,1) %}
    {{ { name_column_6: (now().timestamp() - x)|timestamp_custom("%H:%M", false) } }}

Without being able to see the full blueprint I can’t tell if it would work in this case, but it might be better to just use the trigger variable:

Edit: That won’t work with your Event trigger…

[stepsolar]

here is my project

blueprint:
  name: 👷‍♂️ Timbratura Ingresso/Uscita NFC
  description: >
    # 👷‍♂️ Timbratura Ingresso/Uscita NFC in Google Sheet
        
    **Version: 1.4**
    
    
    Tieni traccia delle presenze sul posto di lavoro, registrando l'ingresso e l'uscita su uno o più fogli di Google Sheet.
        
    <details>
    <summary><b>Il processo di automazione:</b> - Clicca qui per espandere</summary>
          
      
      - **Attivazione con Tag Rfid:**
        
        - Specifica il nome del proprio dipendente. Deve coddispondere al nome assegnato al Tag Rfid e all'entità input_text creata precedentemente.
        - Potete Personalizzare i nomi delle colonne che saranno riportati nel foglio di Google Sheet
        - Selezionato Google Sheet e aver scritto il nome del foglio l'automazione e pronta per:
            - **Identificare il Tag Rfid:** avvicinare il tag al dispositivo di lettura per essere letto anche da Smartphone (che abbia NFC)
            - **Trascrivere su Google Sheet:** scriverà la Data, Ora, Nome del Tag se ha effettuato l'ingesso, con l'uscita scriverà anche la Durata.
            
    </details>
    
    
    Necessario = *
  domain: automation
  input:
    trigger_settings:
      name: "Dipendente *"
      icon: mdi:account
      input:
        trigger_name:
          name: "Nome *"
          description: >
            Inserisci il **Nome** del dipendente
          selector:
            text:
        trigger_state_entity:
          name: "Entità Input *"
          description: >
            **Seleziona l'entità**.
            
            Seleleziona Input Text cottispondente al proprio dipendente.
          default: []
          selector:
            entity:
              filter: 
                - domain: input_text
        holiday_calendar_entity:
          name: "Calendario Festività"
          description: >
            **Seleziona il Calendario**.
            
            Agiungendo il Calendario Festività esso sarà indicato in tabella
          default: []
          selector:
            entity:
              filter: 
                - domain: calendar
    device_google_sheet_settings:
      name: "Google Sheet *"
      icon: mdi:google-spreadsheet
      collapsed: true
      input:    
        device_google_sheet:
          name: "Google Sheet *"
          description: >
            Seleziona Google Sheet
          default: []
          selector:
            config_entry:
        sheet_name:
          name: "Nome Foglio *"
          description: >
            Inserisci il nome del foglio ess. "Presenze Mario" potete creare più fogli ed assegnarli all'utente corrispondente.
          default: Inserisci qui il nome del foglio
          selector:
            text:
        language_month:
          name: "Lingua Mese *"
          description: >
            Seleziona la lingua con cui scrivere i mesi su Google Sheet
          selector:
            select:
              custom_value: true
              mode: dropdown
              options:
                - label: Italiano
                  value: >-
                    {% set mese = ["Gennaio", "Febbraio", "Marzo", "Aprile", "Maggio", "Giugno", "Luglio", "Agosto", "Settembre", "Ottobre", "Novembre", "Dicembre"] %}
                    {% set mese = mese[now().month - 1] %}
                    {{ mese }}
                - label: English
                  value: >-
                    {{ now().strftime('%B') }}
    input_text_settings:
      name: "Testo di Input"
      icon: mdi:order-alphabetical-ascending
      collapsed: true
      input:    
        text_in:
          name: "Testo per ingresso"
          description: >
            Inserisci il testo "Ingresso"
          default: Ingresso
          selector:
            text:
        text_out:
          name: "Testo per uscita"
          description: >
            Inserisci il testo "Uscita"
          default: Uscita
          selector:
            text:
    customize_text_settings:
      name: "Personalizza"
      icon: mdi:rename-box
      description: >
            
            **Qui Potrai Personalizzare la Tua Tabella**.
            
            Cambia i nomi di default per personalizzare i nomi delle tue colonne
            
      collapsed: true
      input:
        name_column_1:
          name: "Nome Colonna 1"
          description: >
            Inserisci il nome default "Mese"
          default: Mese
          selector:
            text:
        name_column_2:
          name: "Nome Colonna 2"
          description: >
            Inserisci il nome default "Data"
          default: Data
          selector:
            text:
        name_column_3:
          name: "Nome Colonna 3"
          description: >
            Inserisci il nome default "Dipendente"
          default: Dipendente
          selector:
            text:
        name_column_4:
          name: "Nome Colonna 4"
          description: >
            Inserisci il nome default "Ingresso/Uscita"
          default: Ingresso/Uscita
          selector:
            text:
        name_column_5:
          name: "Nome Colonna 5"
          description: >
            Inserisci il nome default "Orario"
          default: Orario
          selector:
            text:
        name_column_6:
          name: "Nome Colonna 6"
          description: >
            Inserisci il nome default "Tempo di Lavoro"
          default: Tempo di Lavoro
          selector:
            text:
        name_column_7:
          name: "Nome Colonna 7"
          description: >
            Inserisci il nome default "Festività"
          default: Festività
          selector:
            text:

mode: parallel
max_exceeded: silent

variables:
  trigger_name: !input trigger_name
  trigger_state_entity: !input trigger_state_entity
  holiday_calendar_entity: !input holiday_calendar_entity
  device_google_sheet: !input device_google_sheet
  sheet_name: !input sheet_name
  text_in: !input text_in
  text_out: !input text_out
  language_month: !input language_month
  name_column_1: !input  name_column_1
  name_column_2: !input  name_column_2
  name_column_3: !input  name_column_3
  name_column_4: !input  name_column_4
  name_column_5: !input  name_column_5
  name_column_6: !input  name_column_6
  name_column_7: !input  name_column_7
  
triggers:
  - trigger: event
    event_type: tag_scanned

actions:
  - choose:
      - alias: Esegue ingresso
        conditions:
          - condition: state
            entity_id: !input trigger_state_entity
            state: !input text_in
        sequence:
          - action: google_sheets.append_sheet
            metadata: {}
            data:
              add_created_column: true
              config_entry: !input device_google_sheet
              worksheet: !input sheet_name
              data:
                "{{ name_column_1 }}": !input language_month                # MESE
                "{{ name_column_2 }}": "{{ now().strftime('%d/%m/%Y') }}"   # DATA
                "{{ name_column_3 }}": "{{ trigger.event.data.name }}"      # NOME DIPENDENTE
                "{{ name_column_4 }}": !input text_in                       # INGRESSO
                "{{ name_column_5 }}": "{{ now().strftime('%H:%M') }}"      # ORA
          - action: input_text.set_value
            metadata: {}
            target:
              entity_id: !input trigger_state_entity
            data:
              value: !input text_out
      - alias: Esegue uscita
        conditions:
          - condition: state
            entity_id: !input trigger_state_entity
            state: !input text_out
        sequence:
          - action: google_sheets.append_sheet
            metadata: {}
            data:
              add_created_column: true
              config_entry: !input device_google_sheet
              worksheet: !input sheet_name
              data:
                "{{ name_column_1 }}": !input language_month                # MESE
                "{{ name_column_2 }}": "{{ now().strftime('%d/%m/%Y') }}"   # DATA
                "{{ name_column_3 }}": "{{ trigger.event.data.name }}"      # NOME DIPENDENTE
                "{{ name_column_4 }}": !input text_out                      # USCITA
                "{{ name_column_5 }}": "{{ now().strftime('%H:%M') }}"      # ORA
                "{{ name_column_6 }}": >-                                   # TEMPO DI LAVORO
                    {{ (as_timestamp(now()) - as_timestamp(states[trigger_state_entity].last_changed | default(0)) | int ) | timestamp_custom("%H:%M", false) }}
                "{{ name_column_7 }}": >-
                    {% if is_state(holiday_calendar_entity, 'off') %}
                    {% else %}
                    {{ state_attr(holiday_calendar_entity, 'message') }}
                    {% endif %}
          - action: input_text.set_value
            metadata: {}
            target:
              entity_id: !input trigger_state_entity
            data:
              value: !input text_in

[stepsolar]

I tried to enter your code, but it doesn’t work. I’m probably entering it incorrectly.

[stepsolar]

I wrote it this way, it returns the value but the automation gives me an error

    '{{ name_column_6 }}': >-
      {% set x =
      as_timestamp(states[trigger_state_entity].last_changed)|default(0,1) %} {{
      (now().timestamp() - x)|timestamp_custom("%H:%M", false) }}

[Didgeridrew]

@stepsolar

I wrote it this way, it returns the value but the automation gives me an error

You can’t template YAML structure.

[stepsolar]

You’ll have to excuse me, but I’m not an expert, I don’t understand your answer. Are you telling me it’s not possible? Even if I can see the value but it doesn’t write it to Google Sheets?

[Didgeridrew]

I’m telling you that…

    '{{ name_column_6 }}': >-
      {% set x =
      as_timestamp(states[trigger_state_entity].last_changed)|default(0,1) %} {{
      (now().timestamp() - x)|timestamp_custom("%H:%M", false) }}

… will not produce valid YAML. You need to template the entire value for data so that it returns a dictionary:

action: google_sheets.append_sheet
metadata: {}
data:
  add_created_column: true
  config_entry: !input device_google_sheet
  worksheet: !input sheet_name
  data: |
    {{ {
      name_column_1: language_month,
      name_column_2: now().strftime('%d/%m/%Y'),
      name_column_3: trigger.event.data.name,
      name_column_4: text_in,
      name_column_5: now().strftime('%H:%M') 
    } }}

… making sure to change the second google sheets action to use the same syntax.

[stepsolar]

Are you telling me I have to write it this way? I tried it but it doesn’t work.

variables:
  trigger_name: !input trigger_name
  trigger_state_entity: !input trigger_state_entity
  holiday_calendar_entity: !input holiday_calendar_entity
  device_google_sheet: !input device_google_sheet
  sheet_name: !input sheet_name
  text_in: !input text_in
  text_out: !input text_out
  language_month: !input language_month
  name_column_1: !input  name_column_1
  name_column_2: !input  name_column_2
  name_column_3: !input  name_column_3
  name_column_4: !input  name_column_4
  name_column_5: !input  name_column_5
  name_column_6: !input  name_column_6
  name_column_7: !input  name_column_7
  
triggers:
  - trigger: event
    event_type: tag_scanned

actions:
  - choose:
      - alias: Esegue ingresso
        conditions:
          - condition: state
            entity_id: !input trigger_state_entity
            state: !input text_in
        sequence:
          - action: google_sheets.append_sheet
            metadata: {}
            data:
              add_created_column: true
              config_entry: !input device_google_sheet
              worksheet: !input sheet_name
              data: |
                {{ {
                  name_column_1: language_month,
                  name_column_2: now().strftime('%d/%m/%Y'),
                  name_column_3: trigger.event.data.name,
                  name_column_4: text_out,
                  name_column_5: now().strftime('%H:%M')
                } }}
          - action: input_text.set_value
            metadata: {}
            target:
              entity_id: !input trigger_state_entity
            data:
              value: !input text_out
      - alias: Esegue uscita
        conditions:
          - condition: state
            entity_id: !input trigger_state_entity
            state: !input text_out
        sequence:
          - action: google_sheets.append_sheet
            metadata: {}
            data:
              add_created_column: true
              config_entry: !input device_google_sheet
              worksheet: !input sheet_name
              data: |
                {% set x = as_timestamp(states[trigger_state_entity].last_changed)|default(0,1) %}
                {{ {
                  name_column_1: language_month,
                  name_column_2: now().strftime('%d/%m/%Y'),
                  name_column_3: trigger.event.data.name,
                  name_column_4: text_out,
                  name_column_5: now().strftime('%H:%M'),
                  name_column_6: (now().timestamp() - x)|timestamp_custom("%H:%M", false)
                  name_column_7: >-
                    {% if is_state(holiday_calendar_entity, 'off') %}
                    {% else %}
                    {{ state_attr(holiday_calendar_entity, 'message') }}
                    {% endif %}
                } }}
          - action: input_text.set_value
            metadata: {}
            target:
              entity_id: !input trigger_state_entity
            data:
              value: !input text_in

[Didgeridrew]

@stepsolar

I tried it but it doesn’t work.

You’re still trying to build YAML with a template:

@stepsolar

name_column_7: >-
  {% if is_state(holiday_calendar_entity, 'off') %}
  {% else %}
  {{ state_attr(holiday_calendar_entity, 'message') }}
  {% endif %}

… and you were missing a , after the name_column_6 line:

action: google_sheets.append_sheet
metadata: {}
data:
  add_created_column: true
  config_entry: !input device_google_sheet
  worksheet: !input sheet_name
  data: |
    {% set x = as_timestamp(states[trigger_state_entity].last_changed) | default(0,1) %}
    {% set n7 = '' if is_state(holiday_calendar_entity, 'off') else state_attr(holiday_calendar_entity, 'message') %}
    {{ {
      name_column_1: language_month,
      name_column_2: now().strftime('%d/%m/%Y'),
      name_column_3: trigger.event.data.name,
      name_column_4: text_out,
      name_column_5: now().strftime('%H:%M'),
      name_column_6: (now().timestamp() - x)|timestamp_custom("%H:%M", false),
      name_column_7: n7
    } }}

[stepsolar]

Now I understand the concept, entering your function code correctly.

However, when I do some tests, when HA restarts, the time elapsed between “Entry” and “Exit” resets.

[Didgeridrew]

@stepsolar

However, when I do some tests, when HA restarts, the time elapsed between “Entry” and “Exit” resets.

That is unavoidable, because you are using last_changed which will reset on restart.

There are a couple ways to accomplish what you are trying to do… but if you want it to all happen in the blueprint-generated automation, your only real option is to enable add_created_columnin your google_sheets.append_sheet actions and use the google_sheets.get_sheet action to query the sheet and search for the previous event for the current person, then calculate the difference.

Other things to consider:

There is a missing condition to link the trigger_name variable from the input to the name value coming from the actual trigger variable object. As currently configured, if someone creates multiple version of the automation for different employee-based sheets, the data in the sheets won’t be employee-specific. All sheets will be populated with all tag scan events.

Even if you add the query, the automation relies on the Input Text as the source of truth for what is an “entering” event and what is an “exiting” event. What happens if it gets out of sync with reality?

[stepsolar]

I understand, I’ll try to give it a try. I wanted to create a blueprint that was accessible even to less experienced users (like me), to avoid creating sensors and lines of code to make the blueprint work.

[stepsolar]

maybe I have a simpler solution for my blueprint, make it read the time_fired date and time of the tag_id when it is scanned and make the difference with the input time

  event:
    event_type: tag_scanned
    data:
      tag_id: 3a902b22-a618-4d21-a5e4
      name: Evelin
      device_id: df085db12d42b7ccf9a752
    origin: LOCAL
    time_fired: '2025-12-30T07:35:34.040285+00:00'

[stepsolar]

@Didgeridrew

There is a missing condition to link the trigger_name variable from the input to the name value coming from the actual trigger variable object. As currently configured, if someone creates multiple version of the automation for different employee-based sheets, the data in the sheets won’t be employee-specific. All sheets will be populated with all tag scan events.

Even if you add the query, the automation relies on the Input Text as the source of truth for what is an “entering” event and what is an “exiting” event. What happens if it gets out of sync with reality?

Each employee must have their own tag and input_text.evelin (entry/exit). Are you saying this doesn’t work? The sheet must have multiple tabs with the employee’s name so that the automation only populates that employee’s tab.

[Didgeridrew]

@stepsolar

maybe I have a simpler solution for my blueprint, make it read the time_fired date and time of the tag_id when it is scanned and make the difference with the input time

That’s essentially going to be the same as using the value from the add_created_column, you will still need to query the sheet to get the previous value…

@stepsolar

… the automation only populates that employee’s tab.

What I’m saying is that there isn’t anything enforcing that. The way you currently have it, all tag_scanned events will populate all sheets that are linked to automations created by this blueprint.

The better method would be to include an input so that the name value from the tag can be used in the trigger. That creates a employee-specific trigger for the employee-specific automation:

trigger: event
event_type: tag_scanned
event_data:
  name: !input employee_name_as_stored_on_tag

[stepsolar]

@Didgeridrew

The better method would be to include an input so that the name value from the tag can be used in the trigger. That creates a employee-specific trigger for the employee-specific automation:

copied!

trigger: event
event_type: tag_scanned
event_data:
  name: !input employee_name_as_stored_on_tag

I hadn’t thought of that, maybe because I’m only using one tag for now. Thanks for your suggestions.

[stepsolar]

@Didgeridrew

There are a couple ways to accomplish what you are trying to do… but if you want it to all happen in the blueprint-generated automation, your only real option is to enable add_created_columnin your google_sheets.append_sheet actions and use the google_sheets.get_sheet action to query the sheet and search for the previous event for the current person, then calculate the difference.

I’m trying to figure out how to retrieve the value from the Google Sheet cell, the only thing I can retrieve is the entire row, by doing this…

action: google_sheets.get_sheet
data:
  config_entry: 01KCDZG10HEQF415
  worksheet: Presenze Evelin
  rows: 1

range:
  - - "2025-12-30 09:00:33.709347"
    - Dicembre
    - 30/12/2025
    - Evelin
    - Uscita
    - "09:00"

How could I retrieve only the value of the cell that shows the time?

[Didgeridrew]

Sensor based on a Google Sheets cell

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: [image] … Using the following ac…

[stepsolar]

I followed your detailed guide and wrote the following code… but it returns an error. I wrote time_in without the {{}} and without the "" but I still get an error.
For now, the way it’s written is just a test. I wanted to display the input date value in column 7 to see if it was written correctly, but it isn’t.

        sequence:
          - action: google_sheets.get_sheet
            data:
              config_entry: !input device_google_sheet
              worksheet: !input sheet_name
              rows: 1
            response_variable: sheet_data
          - variables:
              time_in: |
                {{ (sheet_data['range'][6] }}
          - action: google_sheets.append_sheet
            metadata: {}
            data:
              add_created_column: true
              config_entry: !input device_google_sheet
              worksheet: !input sheet_name
              data: |
                {% set x = as_timestamp(states[trigger_state_entity].last_changed)|default(0,1) %}
                {% set n7 = '' if is_state(holiday_calendar_entity, 'off') else state_attr(holiday_calendar_entity, 'message') %}
                {{ {
                  name_column_1: language_month,
                  name_column_2: now().strftime('%d/%m/%Y'),
                  name_column_3: trigger.event.data.name,
                  name_column_4: text_out,
                  name_column_5: now().strftime('%H:%M'),
                  name_column_6: (now().timestamp() - x)|timestamp_custom("%H:%M", false),
                  name_column_7: "{{ time_in }}"
                } }}