As part of multiple hobbies, I have a large amount of inventory that needed organization. I decided to use the knowledge from working in excel during office hours, to perform a query for my listed inventory.
Over the years, the collection of Games Workshop miniatures and boxed games has grown past the point of quick list management. I made a spreadsheet with listed barcodes, scraped the web for inventory translation to provide the names, gathered url’s for pictures, and added some manual tags for each “bin” where the miniature is stored.
I then took this sheet, uploaded it to local storage for HASS, and created an appdaemon that would allow free-text query. Based off the query, it would feed results with the desired attributes and pictures, where available.
Here’s a view of the final product, with a list below it for brief steps. Additional details below!
- Create spreadsheet with required fields
- Upload spreadsheet to HASS
- Install and configure AppDaemon
- Create application to load spreadsheet into a database
- Create application to allow free-text query of database
- Create input_text to feed free-text query, and resulting items
- Create input_select to populate resulting items where multiples exist
- Create python scripts and scripts to allow for automation based of manual input
- Create the automation, referencing all aspects
- Create dashboard to render all options in a friendly format
Now for the fun bit. The spreadsheet itself is a tangential project in and of itself. If you have a desire to scrape the web, you’ll need to find a way to connect it with a query to load the details you’re after. In my example, I have a barcode list, and an api query to a barcode lookup. If you’re interested in the details, I can provide them.
Short net: Barcodes in column A, scraped details in remaining columns.
You’ll need to install AppDaemon add-on at this point, and configure it according to the instructions. You will generate a long-lived token and add it to the config file for appdaemon.yaml:
Summary
---
appdaemon:
latitude: 52.3
longitude: 4.89
elevation: 2
time_zone: !env_var TZ
plugins:
HASS:
type: hass
ha_url: http://homeassistant.local:8123
token: [u]***YOUR TOKEN HERE***[/u]
admin:
api:
hadashboard:
dash_url: http://homeassistant.local:5050
dash_dir: /addon_configs/appdaemon/dashboards
Once the spreadsheet is ready, I uploaded it to the www/ folder within Appdaemon. This can be found under the root folder structure, * /addon_configs/a0d7b954_appdaemon/www/ in my case.
The next steps are specific prep to allow for the inventory sheet to be ingested. We need specific versions of packages to be loaded with AppDaemon. I added pandas, numpy, and openpyxl.
Next up: creating the inventory app.
Here’s the examlpe of my code. It reads the excel path, reads the absolute path, publishes that within the logs for appdaemon, and loads it into inventory.
Summary
`import appdaemon.plugins.hass.hassapi as hass
import pandas as pd
import os
class InventoryApp(hass.Hass):
def initialize(self):
self.log("Starting Inventory App")
# Path to your Excel file
excel_file_path = '/config/www/inventory.xlsx'
self.log(f"Expected path: {excel_file_path}")
self.log(f"Absolute path: {os.path.abspath(excel_file_path)}")
# List directory contents
directory = '/config/www'
self.log(f"Contents of {directory}: {os.listdir(directory)}")
# Verify the file path exists
if os.path.exists(excel_file_path):
self.log("File exists. Attempting to load...")
try:
self.df = pd.read_excel(excel_file_path)
self.log(f"Excel file loaded successfully with {len(self.df)} rows")
except Exception as e:
self.log(f"Error loading Excel file: {e}")
else:
self.log(f"File not found at path: {excel_file_path}")
def query_inventory(self, query):
self.log(f"Querying inventory for: {query}")
# Perform a basic query to search the Title column
results = self.df[self.df['Title'].str.contains(query, case=False, na=False)]
for result in results.to_dict(orient='records'):
self.log(result)
return results.to_dict(orient='records')`
Cool! Now, we need a way to query it.
I created an input_text.query, input_text.selected_result, an input_select.query_result, template sensor.query_result, sensor.selected_result, and 2 scripts to reference in automations.
input_select
Summary
query_result:
name: Query Result
options:
- "Loading..."
initial: "Loading..."
input_text
Summary
query:
name: Query
initial: ""
selected_result:
name: Selected Result
initial: ""
templates
Summary
`- sensor:
######################################
Section for Query Excel
######################################
- name: “Query Result”
unique_id: query_result
state: “{{ states(‘sensor.query_result’) }}”
attributes:
Results: >-
{% if state_attr(‘sensor.query_result’, ‘Results’) %}
{{ state_attr(‘sensor.query_result’, ‘Results’) }}
{% else %}
‘No data’
{% endif %} - name: “Query Selected Result”
unique_id: selected_result
state: “{{ states(‘sensor.selected_result’) }}”
attributes:
EAN: “{{ state_attr(‘sensor.selected_result’, ‘EAN’) }}”
BIN: “{{ state_attr(‘sensor.selected_result’, ‘BIN’) }}”
Classification: “{{ state_attr(‘sensor.selected_result’, ‘Classification’) }}”
Title: “{{ state_attr(‘sensor.selected_result’, ‘Title’) }}”
Keywords: “{{ state_attr(‘sensor.selected_result’, ‘Keywords’) }}”
Images1: “{{ state_attr(‘sensor.selected_result’, ‘Images1’) }}”
Images2: “{{ state_attr(‘sensor.selected_result’, ‘Images2’) }}”
Images3: “{{ state_attr(‘sensor.selected_result’, ‘Images3’) }}”
Images4: “{{ state_attr(‘sensor.selected_result’, ‘Images4’) }}”
Images5: “{{ state_attr(‘sensor.selected_result’, ‘Images5’) }}”
Images6: “{{ state_attr(‘sensor.selected_result’, ‘Images6’) }}”
Images7: “{{ state_attr(‘sensor.selected_result’, ‘Images7’) }}”
Images8: “{{ state_attr(‘sensor.selected_result’, ‘Images8’) }}”
Images9: “{{ state_attr(‘sensor.selected_result’, ‘Images9’) }}” - name: “Images1 Check”
state: “{{states.sensor.selected_result.attributes.Images1}}” - name: “Images2 Check”
state: “{{states.sensor.selected_result.attributes.Images2}}” - name: “Images3 Check”
state: “{{states.sensor.selected_result.attributes.Images3}}” - name: “Images4 Check”
state: “{{states.sensor.selected_result.attributes.Images4}}” - name: “Images5 Check”
state: “{{states.sensor.selected_result.attributes.Images5}}” - name: “Images6 Check”
state: “{{states.sensor.selected_result.attributes.Images6}}” - name: “Images7 Check”
state: “{{states.sensor.selected_result.attributes.Images7}}” - name: “Images8 Check”
state: “{{states.sensor.selected_result.attributes.Images8}}” - name: “Images9 Check”
state: “{{states.sensor.selected_result.attributes.Images9}}”`
scripts
Summary
`query_inventory:
alias: Query Inventory
sequence:
- action: python_script.query_inventory
data_template:
query: ‘{{ states(’‘input_text.query’‘) }}’`
python_scripts
query_inventory.py:
Summary
query = data.get('query') result = hass.services.call('inventory_app', 'query_inventory', {'query': query}) hass.states.set('sensor.query_result', result, {'friendly_name': 'Query Result'})
remove_duplicates.py:
Summary
`# remove_duplicates.py
options = data.get(‘options’, )
unique_options =
for option in options:
if option not in unique_options:
unique_options.append(option)
if not unique_options: # If the list is empty, set to default option
unique_options = [“Loading…”]
service_data = {
‘entity_id’: data.get(‘entity_id’, ‘input_select.query_result’), # Default to input_select.query_result if not provided
‘options’: unique_options
}
logger.info(f"Setting options for {service_data[‘entity_id’]}: {unique_options}")
hass.services.call(‘input_select’, ‘set_options’, service_data)
`
Now that all of that noise is completed, we get to the actual query app.
here’s the examlpe of what the query does. In my case, I query based off of keyword, referencing the Title and Keyword column. Based off what it finds, I make it throw everything into resulting sensors above.
Summary
`import appdaemon.plugins.hass.hassapi as hass
import pandas as pd
import json
class ExcelQueryApp(hass.Hass):
def initialize(self):
self.log("ExcelQueryApp initialized")
self.listen_event(self.query_excel, "QUERY_EXCEL")
self.listen_state(self.update_sensor, "input_select.query_result")
def query_excel(self, event_name, data, kwargs):
keyword = data.get("keyword", "").lower()
self.log(f"Received keyword: {keyword}")
try:
df = pd.read_excel('/config/www/inventory.xlsx')
self.log(f"DataFrame columns: {df.columns}")
# Filter the DataFrame based on the keyword in specific columns
result = df[df[['Title', 'Keywords']].apply(lambda row: row.astype(str).str.contains(keyword, case=False).any(), axis=1)]
self.log(f"Search result for '{keyword}': {result}")
if not result.empty:
result = result[['EAN', 'BIN', 'Classification', 'Title', 'Keywords', 'Images1', 'Images2', 'Images3', 'Images4', 'Images5', 'Images6', 'Images7', 'Images8', 'Images9']].where(pd.notnull(result), None)
# Prepare image attributes
attributes = {"titles": result['Title'].tolist()}
for i in range(1, 10):
col_name = f"Images{i}"
if col_name in result.columns:
attributes[col_name.lower()] = result[col_name].dropna().tolist()
result_dict = result.to_dict(orient='records')
count = len(result_dict)
attributes.update({"Results": result_dict, "Count": count})
else:
result_dict = []
count = 0
attributes = {"Results": result_dict, "Count": count}
self.log(f"Setting state for sensor.query_result with state: {count} and attributes: {attributes}")
# Set the state and attributes of the sensor
self.set_state("sensor.query_result", state=count, attributes=attributes)
except Exception as e:
self.log(f"Error: {e}")
def update_sensor(self, entity, attribute, old, new, kwargs):
query_result = self.get_state("sensor.query_result", attribute="Results")
selected_result = next((result for result in query_result if result["Title"] == new), {})
self.set_state("sensor.selected_result", state=new, attributes=selected_result)
`
it’s a lot, I know. Once I got it all working, I plugged this into 2 automations.
the first will take the results, remove any duplicates (we have 2 or more of the same box), and only list the unique entries in the results.
automations:
Summary
`alias: Query Excel on Input Change
description: Query Fire
triggers:
- trigger: state
entity_id:- input_text.query
actions:
- input_text.query
- event: QUERY_EXCEL
event_data:
keyword: “{{ states(‘input_text.query’) }}”
mode: single
alias: Update Query Result Options
description: query updater
triggers:
- trigger: state
entity_id:- sensor.query_result
conditions:
actions:
- sensor.query_result
- action: python_script.remove_duplicates
metadata: {}
data: {} - action: input_select.set_options
metadata: {}
data:
options: >
{% set results = state_attr(‘sensor.query_result’, ‘Results’) %} {% if
results %}
{{ results | map(attribute=‘Title’) | list | unique | list }}
{% else %}
[“No results”]
{% endif %}
target:
entity_id: input_select.query_result
mode: single
`
Beauty. That gets us to the final product!
Dashboard was set with entities card, to allow input text for the query sensor. Then, another entities card, showing the dropdown of input_select for what we select as the results from query!
Based off the current select in the dropdown, we then get the details of each picture, attribute and location from a third entities card, and markdown/swipe card combo that allows swipe thru each image:
Summary
`type: vertical-stack
cards:
- type: entities
entities:- entity: input_text.query
title: “Enter a keyword to begin:”
show_header_toggle: false
state_color: false
- entity: input_text.query
- type: entities
entities:- entity: input_select.query_result
- type: conditional
conditions:- condition: state
entity: input_select.query_result
state_not: Loading… - condition: or
conditions:- condition: state
entity: input_select.query_result
state_not: unknown - condition: or
conditions:- condition: state
entity: input_select.query_result
state_not: unavailable
card:
type: vertical-stack
cards:
- condition: state
- type: entities
entities:- entity: sensor.query_selected_result
name: Title
type: attribute
attribute: Title
icon: mdi:text - entity: sensor.query_selected_result
name: BIN
type: attribute
attribute: BIN
icon: mdi:bucket-outline - entity: sensor.query_selected_result
name: Classification
type: attribute
icon: mdi:magnify-expand
attribute: Classification - entity: sensor.query_selected_result
name: Keywords
type: attribute
attribute: Keywords
icon: mdi:key-plus
state_color: false
- entity: sensor.query_selected_result
- type: custom:swipe-card
parameters:
scrollbar:
hide: false
draggable: true
pagination:
type: bullets
clickable: true
loop: true
cards:- type: conditional
conditions:- condition: state
entity: sensor.images1_check
state_not: unknown
card:
type: markdown
content: >-
- condition: state
- type: conditional
conditions:- condition: state
entity: sensor.images2_check
state_not: unknown
card:
type: markdown
content: >-
- condition: state
- type: conditional
conditions:- condition: state
entity: sensor.images3_check
state_not: unknown
card:
type: markdown
content: >-
- condition: state
- type: conditional
conditions:- condition: state
entity: sensor.images4_check
state_not: unknown
card:
type: markdown
content: >-
- condition: state
- type: conditional
conditions:- condition: state
entity: sensor.images5_check
state_not: unknown
card:
type: markdown
content: >-
- condition: state
- type: conditional
conditions:- condition: state
entity: sensor.images6_check
state_not: unknown
card:
type: markdown
content: >-
- condition: state
- type: conditional
conditions:- condition: state
entity: sensor.images7_check
state_not: unknown
card:
type: markdown
content: >-
- condition: state
- type: conditional
conditions:- condition: state
entity: sensor.images8_check
state_not: unknown
card:
type: markdown
content: >-
- condition: state
- type: conditional
conditions:- condition: state
entity: sensor.images9_check
state_not: unknown
card:
type: markdown
content: >-
title: “Selection Details:”
`
- condition: state
- type: conditional
- condition: state
- condition: state
et voila! the final product:
