Node-red to LibreCalc

General
1

(Looking for help)

Folks, new project for me.

LOGISTICS

There is a spreadsheet with assets and all that stuff.
For one person it is ok to use as is, but the list is growing and for one person to handle it is becoming painful.

So I was thinking. (I know, that is dangerous, but....)

Have the spreadsheet on a "server" and multiple people can look at it at the same time and enter data in/on it.

Why am I asking here and not on the LibreCalc forum?

Well, I want to make the sheet accessible by many people at the same time and allow them to all see the same thing at once.

I know node-red and thought - yeah: that word again - it would be doable with NR via python3.

At this point I only have a vague idea of all the ins and outs.
There is a fairly large list of items that need to be checked off.
There are sub-groups of where they are and of types of items.

I suspect their locations would be better than their type, as you would mark things off by location rather than type.

Person 1 would go to place 1 and go through it's items. Marking them off when seen.
Person 2 would do the same at place 2.

And so on.

As they are marked off, it is reflected on the sheet by turning that item's name green - for instance.

An over-viewing person would then see the bigger picture of things turning green as they have been sighted by the people.

Then any not found items would be white and further searching done.

But it allows different viewing by different people depending on where they are in the scheme.

Possible with NR?

I'm guessing this would be a great time to use the newer dashboard too.

Thoughts?

2

The description of the use-case is kind of vague - is it just a table of items that requires multi-user/collaborative functionality or do you actually need all functionality of a spreadsheet ? Multi-user solutions (besides security) are highly complex due to concurrency and conflicts.

The smart folks at google have solved this years ago: Google Sheets - which is multi user and free and there are open source alternatives like nocodb, baserow available as well.

3

Well, the "spread sheet" part is where it is now.

I am looking for a way to help with ticking the boxes and allow more than just one person go through the list and see/check things.

Rather than printing off multiple copies of it and going around with a paper version, ticking things then sitting down marking them off from paper to spreadsheet......

Make it usable for multiple people and they have tablets and can walk around checking their items off.

Meanwhile someone can see the big picture of ALL items.
And not have multiple copies of the same file, and all those problems.

4

Spreadsheets really not the best tool for this. With the exception of the true online apps such as bakman2 has already mentioned, traditional spreadsheets are best when individuals need to take data offline.

I'm not that familiar with Libra but I've done professional solutions in the past that used separate spreadsheets with a restricted design that individuals filled in and sent via email. They included live Internet lookups to pre-populate data. Of course, the email part could be replaced by a shared file location. Though, depending on sensitivity and security requirements, it may be necessary to have separate folders for each user.

Really, an offline capable web app would be best for this. But that is, of course, a fair bit of work.

The big question to answer is: Are the tablets always online when the list is being reviewed?

Other questions:

  1. Is the list reset every time? (e.g. daily, hourly, ...)
  2. Is a log of the list required for each time period?
  3. Do you have Office 365?
  4. Do you have Google Workplace?
  5. Do you have a local equivalent of one of the above, e.g. NextCloud with OnlyOffice or Collabora?

In fact, I think that the easiest solution if you are using spreadsheets would be to build smarts into the spreadsheet. It could use a tabular lookup (I assume LibraCalc can do that? Excel can) using a Node-RED endpoint as an API. That would get you the list. Then each list item would need either a link or a button or other event handler so that when the item was clicked, it sent the data to another Node-RED API endpoint. That assumes that the tablets are online of course. If that isn't the case then you would have a "Send" button in the workbook that sent all the data in one go, gets a response from the Node-RED API and, if successful, resets the list ready for next time.

5

Yeah, it is not the best way of doing things.
Oh, and I am new to this and am offering ideas to help reduce doing it all manually.

Just the List of items is sent to the people every year (So yearly) and they have to go through it and check all the listed items are still found in stock.

I have been told MS office access is available, and may be more useful for this task.
Which may happen with new information given to me.

Just how it is/was done in the past (years) the sheet would be printed off, someone go through all the items and tick them off.
Then go back to the XL sheet and confirm what's in stock.

Seems not very good.

I thought I'd ask with the initial idea. But as said, I have since been told there may be an existing program anyway.

Thanks.

I'll maybe leave it here and wait to see if there are any existing programs and/or use the MS stuff.
Though for me that will be a painful thing.
It has been decades since I have used any MS stuff seriously.
I prefer Libre Office. But I can't expect to force them to change which programs they use.
(Though I did say LibreCalc in the subject.) Muscle memory)
I'm sure XL would accommodate/has a port but anyway.....

6

Excel is certainly pretty decent at this kind of thing. I've never use Libre Office professionally so not sure about that.

Excel can even now use JavaScript as a macro language though VBA is also still there.

Anyway, the key takeaway is that Node-RED can be your convenient API server. It could also act as a data consolidator (e.g. users submit their sheets to the server and a Node-RED flow ingests them all and spits out the summaries and totals - using UIBUILDER as the UI generator of course! :rofl:

Of course, with UIBUILDER, it wouldn't be that hard to create an online form that can send data right back to Node-RED as things are checked off a list. Let me know if you decide to go that route, it is the kind of thing that floats my boat and I would likely be happy to do the basics for you. :wink:

7

I'm not sure i agree with @TotallyInformation that a spreadsheet is not the right tool for the job.

Clearly the most important things are that person1 and person2 can easily see a list of items to check and record each item status.

Almost everyone is familiar with spreadsheets and it's fairly straightforward to code a list of items and an ok/notok button for each, a save button to confirm.

I think Google sheets is likely a better choice than MS or LibreOffice for this, though I've never integrated either with node red.

My background is with databases so i would tend to keep the data in a db, populating each person's sheet from it daily

8

In my opinion, there are two options: either rebuild it using a database with a clean, user-friendly UI, or use Google Sheets / Microsoft Excel 365. The downside of the second option is that the data is stored in someone else’s cloud, and users need to have accounts.

9

Using Excel from Office 365 does not absolutely require you to keep the data in the cloud, that is optional. But in enterprise terms, this is often the preferred option in any case.

I would generally agree. But everything depends on what tools are in use and whether suitable development capabilities are available. For such a small requirement, no DB is really needed. Node-RED could easily manage the data in a retained flow variable. And it can easily provide useful HTTP endpoints for data queries and updates.

What you have (skills & tools) is more important than what is "Right" of course. :smiley:

10

But if multiple users need access, you’ll need the Microsoft 365 version. Otherwise, you may get a notification that someone else is already using the file, and you’ll only be able to view it.

11

theoretically, you can share a file over a local network, and several users can edit the same file

EDIT: However, there will be a problem with the client for smartphone/mobile.

12

Thanks to all.

Yes, it is a bit complicated.

I'm new to this and was seconded to help with the checking.

To give a better perspective:
Small unit.
6 vehicles. Stocked with tools, etc.
Work shop with tools, etc.
Office with computers, etc.
Kitchen area with fridge, and stuff.

So - as an example - each of the vehicles would be done by a person/people. Then the work shop, then the office and then the kitchen.

So the NUMBER of people could range from say 2 to .... 5? (If we could get that many people interested in doing that.) :wink:

At the end, anything that wasn't ticked off is obvious and follow up action is taken.
And anything found not on the list is also actioned.

We then have a spreadsheet to send back to the powers that be and they leave us alone.

To be clear:
The spread sheet is an artefact of days gone by when there wasn't much else.
We have access to the full MS office suite - or so it has been implied.
ITMT, I have been told that someone - before me - may have already done what I'm wanting to do now/here.
I need to check that as I don't want to re-invent the wheel.

But the things discussed here have helped me better understand the task required and what it involves.

Why I mentioned LibreOffice and not MS.

Well, that's a sore point. (For some people)

I try to avoid MS stuff.
I have Libre Office.
I'm hoping changing from one to the other wouldn't be a deal breaker in the application of the idea. (As in the small changes needed to go from one to the other.)

So, again:
Thanks to all who have posted.
It is food for thought.

When I get more info I'll update on what's what.

13

maybe use mqtt via DAELOC Extension on the fields that needs updating all use the same template...
I am also new at this but that is the route that I would have gone, subscribe to your topics and publish your changes and all spread sheets should be live

14

Leo AI (via Brave browser) tells me:

LibreOffice Calc supports multi-user collaboration through its native Share Document feature, allowing multiple users to edit the same spreadsheet simultaneously. To enable this, the file must be saved in the .ods format on a shared network location, and users must activate sharing via Tools > Share Document.

Key operational details include:

  • Simultaneous Editing: Multiple users can open and write to the file at the same time, with changes merged upon saving.

  • Conflict Resolution: If users edit the same cell, a Resolve Conflicts dialog appears during save, allowing the user to choose "Keep Mine" or "Keep Other".

  • Limitations: While in shared mode, users cannot modify formatting, charts, or drawings; only data cells are editable.

  • Alternative for Real-Time Sync: For true real-time collaboration (similar to Google Sheets), users can integrate LibreOffice Online with cloud storage like Nextcloud or Collabora Online.

Ah, interesting! Now that would be good.

Libre Calc also supports several macro languages: LibreOffice Basic, Python, JavaScript, and BeanShell. So plenty of options for automation.

The biggest disadvantage to Libre Calc of course is that it is likely to be unfamiliar to your users and so may undermine any usability. That and they may have to install it.

Integration of Excel to MQTT is a little more complex. You can purchase a library to make it easier :frowning: or you can call native Windows classes for HTTP or call out to Python for MQTT. But, if you have Node-RED, of course, none of that is necessary since, as I mentioned previously, the sensible thing to do would be to use Node-RED to provide the integration layer. HTTP API endpoints for Excel and direct MQTT connections if needed (probably not in this specific case).

15

Unless I misunderstood Andrew's post, users of the system will be at remote locations, surely live updates are unlikely to be practical?

@Xlink's reminder of MQTT, combined with the guaranteed delivery flow available in the forum looks like a good fit, almost live updates when internet is available.

Hmm. Many of us folks not financially backed by HM Treasury have long ago switched to LibreOffice. :upside_down_face:

16

That's more of a network issue to be honest. I'd almost certainly use separate workbooks though, 1 for each location probably.

Really? I've dealt with a lot of organisations over the years and never met any that had it in common use - but they were almost all large enterprises of course. But if you are paying for O365, why would you also commonly use Libre Office? Andrew says they have access to O365.

I'm not advocating O365 by the way, I just happen to me more familiar with it due to forced use of Office over far too many years. This is likely to change now that I'm retired. Though we do still have access at home since O365 Family is still one of the cheapest ways to get multi-terabyte cloud file storage which I use for extensive backups. So we get Office effectively for free.

17

Did you have a look at Seatable ?
We use that, and they have an api, I have written a simple CRUD node around their api.

18

Used Google with AppSheets recently, 3 people had to record assets, conditon survey & photo record some items on 500 properties, their routes crossed so they had to see which properties had been visited and also be able to modify other peoples data if required. Ended up with a Google Sheets master copy with all the data and links to photographs etc. Would probably be a paid option for you, but free to explore & test out for small number of users. I think there is also an option to use a data base instead of a sheet.

19

I love open source, I love Node-RED , I love Claude

I had 30 minutes this afternoon so

I pasted the link to original thread into Claude code as context, then just kept iterating

- "Build a simple Docker app — web frontend, Postgres DB, no queue, just do it"

- "Is LibreOffice actually web-based?" → no, but there's Collabora Online, which is

- "Can we deploy the web version as an alternate view?" (this is the bit that actually answers the original ask — real browser-based LibreOffice, proper multi-user co-editing, no more shared-file-on-a-network-drive workaround)

- "Is it feasible to use Node-RED as the API layer instead of hand-written code?"

- "Give me both — one API built with Express, one built entirely as Node-RED flows, both defined in the same docker-compose"

- "Can both versions share the same data?"

- "Can some people use the web checklist and others use the spreadsheet, all managing the same data?"

- "Write a proper README explaining why both exist and the limitations, then push it to GitHub"

End result: a small Docker Compose stack — Postgres for the actual data, a plain web checklist (items turn green when checked, records who/when), and a live Collabora Online spreadsheet view of the *same* data, kept in sync both ways. And because the "could Node-RED do this" question came up, there are genuinely two interchangeable API backends in there — one written in Express, one built entirely from Node-RED flows — so you can compare them side by side rather than just take my word for it either way.

Repo's here if anyone wants to poke at it, steal the WOPI/Collabora wiring, or tell me what's wrong with it: **https://github.com/chrisn-au/inventory-check\*\*

Be warned - it works on my machine and it seemed to work - but for 20 minutes of prompting it Haas quite fun - I was pretty impressed the AI created the node red flows. (Iterated a couple of times)

To grab it and run it yourself:

```bash

git clone GitHub - chrisn-au/inventory-check: Multi-user inventory checklist app — Postgres-backed, with interchangeable Express and Node-RED APIs and a Collabora Online spreadsheet view · GitHub

cd inventory-check

docker compose --profile express up -d # or --profile nodered, or both

```

Then open `http://localhost:8088` (Express) or `http://localhost:1880` (Node-RED) — full URL list and details are in the README.

Limitations are called out honestly in the README — dev-grade auth token, no locking if two people save the spreadsheet at the exact same instant, that kind of thing. It's not trying to be a polished product, just a "here's a real answer to the question in this thread" writeup.

20

Just be aware that AI generated flows sometimes miss critical settings values from nodes that are not easily addressed since node authors have not allowed for missing data (understandably).