Excel spreadsheet to manage items, things and rules

Setup, Configuration and Use Migration
1

Hello all,

I recently migrated from file based configuration to a UI based configuration. I’ve been enjoying a lot of the UI benefits but wanted to address one specific downside, it’s not easy to manage items, things or rules in bulk (cleanup work in tags, labels). Also modelling the semantics requires a lot of clicking in the UI.

Therefor I’ve created an Excel file that talks to the Openhab API to get a list of items, things and rules. I’ve included a bit of vba to allow changing some of the fields in the spreadsheet and make them change in Openhab as well.

For items:

  • Type
  • Label
  • Tags (as a comma seperated list)
  • GroupNames (as a comma seperated list)

For things:

  • Label

For rules:

  • Name
  • Tags

It’s not possible to delete objects with the file. Maybe I’ll implement that later but for now I don’t like the thought of accidently cleaning up a bit to much :wink:

I wanted to share my file with the community but at the same time advise caution in using it. Even though I have tested and used it on my installation, I might have missed something important for your installation. As always, take a backup of your configuration.

You can find the file here:

Short manual

  1. Download the file, open it and accept the security warning:
    image

  2. In openhab, create an API token ( openHAB API tokens | openHAB).

  3. Fill in your Openhab host information and the newly created token in the settings tab.
    image

  4. Then refresh the data sources and the different tabs should get populated with your data.
    image

  5. Set the privacy level for all queries to ‘Public’ or choose “Ignore privacy”
    image

6 Likes
2

Works great! It help me verify consistent naming and appropriate tags of each of the almost 1000
items. Having all items, including groups, alphabetically in a single column makes it easy to refer back and forth until you get it right. Take a look at the sample pdf here.
t.pdf (398.2 KB)

I did have to bypass a number of security warnings and enable “public access” for each data query.

1 Like
3

Thanks for the feedback! I’ve included extra steps in my first post. But I’m not sure what you mean with the quoted text. Is that a setting in excel?

4

Here is the warning that I received the first time I ran ‘Refresh All’.

image

Either choosing “Ignore Privacy …” or making it “Public” for each queries allows them complete and populate the worksheet.

It is related to the “Options → Trust Center → “External Data” setting”.

Jim

5

Thanks, I’ve included this in the first post!

6

Configuration development for creating Excel documents is much harder than working with the UI. The hardest part is to learn algorithms and understand how it works. I wonder, what are data transformation techniques? Then I decided to take an excel course to understand the data movement system within this eco-system. You can develop an electronic reporting (ER) format configuration that has an ER format component. I didn’t need that, but I couldn’t work with the documents further without basic knowledge. So basic programming and basic knowledge are necessary for future file integration.

7

I have no idea what you are talking about. Do you have a question or a specific comment about this thread?

8

I get the following error when there is no data to gather. This happened because I do not have any rules on the server. Just wanted to make you aware of this.

image

Thank you

1 Like