Hi,
I'm quite new in Node-red...
Is there a way to access an excel file located in a sharepoint library and poll data from it into a Node-red flow?
There are several possibilities. We need to know more about what platform you are using and what version of SharePoint.
For example, on Windows 10 and SharePoint Online, you could use the OneDrive for Business client to sync the file to the local device. Alternatively, you can directly access Excel data using the Microsoft Graph API.
For SharePoint on-prem (2010+) you can use the Excel Services API.
Thank you for your response.
The plan is to run node-red on a Siemens IOT2040 box. (No windows there). Sharepoint is O365 Online.
My goal is to pull data from a excel sheet into a dashboard.
Kind regards.
OK. Well bear in mind that I've not actually done this, I generally process Excel from SharePoint Online using Excel client or PowerBI both of which have direct connections.
However, you will need to look at how to get the Microsoft Graph API working. You will, as part of that, need to also get OAuth working so that your client can authenticate to the API.
This is of some minor interest to me as it might be useful but sadly I've not much time to help. But I am more than happy to give what little help I can.
Here are some references that might help you get started. Always useful, I find, to start looking at what others have done with Node.JS. In theory, anything you could get working using Node.JS could be translated into Node-RED though you might need a custom node writing.
Have you checked to see if there some form of network segregation between the siemens iot box and the share point instance?
This is pretty important tbh. Especially if there's a factory network for your PLC/IoT stuff.
Also @olab I'd raise caution about allowing a Excel document to interact with a facility. Sure it can work and will work fine when the values are right but once something goes wrong (which it will with excel) you risk downtime/breakdowns. You are better off building a dashboard to input values that's available over web browser. Or build some validation in to your nodes as well as the excel document.
1 Like
I aggree, personally if I wanted to use a dashboard I would set up a separate instance that gets the information from the siemens box and from share point rather than having everyone log on to the siemens device.
The added benefit being you can more easily update Node-RED and any nodes without impacting the siemens box.
1 Like
Some serious assumptions have been made here! We don't know if this is IoT related at all. It may well be information processing or analytics related. In that case, this is actually a potentially very useful approach.
Excel is almost universally used in enterprises and small businesses all over the world. Office 365 is one of the fasted growing cloud platforms and the leading cloud office/information management platform for medium to large enterprises.
So being able to use Node-RED to process an Excel file stored in SharePoint Online could be incredibly useful to a lot of organisations.
Julian, an odd choice of hardware if it isn’t
1 Like
I'm not saying it's not useful I'd just advise against it. I've seen first hand the issues this type of system can cause in enterprise environments l. Be it with a scada system or node red or some fancy HMI. Validation is required at all turns and tbh the performance of most excel apps is terrible. You're writing a front end in a number processing application, it's just not ideal, there is lots of other better suit solutions.
1 Like
I don't entirely disagree. I agree that such things are fragile and dependent often on the original author who may be long gone.
Just saying that all too often, business and enterprise users have to make do with what they can lay their hands on because the IT department takes too long to respond or their solutions are too expensive.
Node-RED should be a good fit in some of these cases since it isn't hard to run and is fairly easy for any power user to understand. Certainly better than many Office VBA solutions I've had to deal with over the years.
Certainly I wouldn't recommend this as an IoT solution as such except as a last resort. But again, it will fill a specific use-case, particularly when needing to link from business users to devices - just make absolutely certain that the spreadsheet is rock solid, has lots of validation and is protected from fundamental changes. Then also put in loads of validation on the Node-RED side. It is possible to get spreadsheet-based solutions pretty solid, I've done a few successful ones over the years where I had to get something done quickly and the main interaction was with business (sales) people over email for example.
Currently, Node-RED still lacks in the area of self-documentation of flows. Thankfully, this is being dealt with and the current beta version has some nice improvements in this area. It is up to all of us to make sure that we now document our flows well!
1 Like
Thank you for all your responses.
Let me point out that this learning by doing.
I also have an SQL server available in my network.
I follow your idea that an excel at some point of time will fail. Therefore i think that importing excel into sql with SSIS might be a better solution. Then I can link an query into the node red project. node red will then have two interfaces: Opcua and sql for input data and output as a web dashboard.
1 Like
