Data Logging on node-red

Node-Red
1

First time poster -

Setting up an Iot Gateway for data logging purposes -
Basically my end goal is to have a NCD IoT Gateway - and a few sensors logging at 1 minute intervals in such a way that i can automatically update set of calculations in excel.

(Psychrometric / air conditioning calculation for which i need temp and hum as base inputs)

This could be as simple as local without internet logging on an SD / USB to a excel file / CSV and email it once a week - with a live dashboard (dashboard data can be since start and does not need to reference the saved data)

or along the lines of updating a remote SQL database - which i can reference with my spreadsheet which is what i was having a play with that led me to post the topic here.

or a google sheet -

so ive had a go at using SQIITE node - and am having some issues. there is a node called sqlite that you can get to from pallete.
Ideally id just click on this and use it, however there are issues with the version of node.

So it looks like i need to get newer versions of Node.js, and NPM to get this to work - in the node-red environment.

so far i have done this:

I’ve upgraded NVM (node version manager to 0.38.0)
i then upgraded NPM, to which i now get errors that NPM does not support node.js version v8.10.0 (8.10.0 is the one that comes with the IoT Gateway and appears too old to use with newer downloadable nodes like sqlite)

I try to use NVM to install a new version of node, and successfully download it, - it completes the checksum - however cannot compile it as the compiler is again an old version.

i get the error

Computing checksum with sha256sum
Checksums matched!
$> ./configure --prefix=/root/ .nvm/versions/node/v14.18.0 <
Node.js configure: Found Python 2.7.18. . .
WARNING: C++ compiler (CXX=g++, 5.4.0) too old, need g++ 6.3.0 or clang++ 8.0.0
WARNING: warnings were emitted in the configure phase
Session closed.

When i then check node version i am still on version 8.10.0

further trying to upgrade the C++ compiler leads me to Onion’s OPKG repository which only contain version 5.4.0 of the compiler - and nobody out there has a updated version - the consensus is to compile Node on a computer and somehow just copy it over

Not being a programmer - and all of this is a bit new, i could do with some tips on how to accomplish this.

help on the SQL or an alternative to achieve by other means appreciated,

Kind regards

Jon

2

Hi Jon,

The Omega Onion 2S+ is an embedded Linux computer. This means it has limited processing power. The Node/NPM installation on the Onion is not conventional as you have discovered. All libraries and NodeJS itself must be compiled on the device. Currently OPKG only supports Node up to version 8.10.0. If you require a later version of Node to run 3rd party libraries in the short term then you will need to use a local server computer and connect a USB modem to it. This could be as simple as a Raspberry Pi with this USB modem connected:

You would simply install Node, Node-Red, our ncd-red-wireless library, and any 3rd party Node-Red libraries required for your application onto the Pi.

Long term we will be transitioning the IoT Edge Computer to a more robust capable computer. This is in development and we hope to start offering this higher power IoT Edge Computer by the end of this year. It will be capable of running the latest version of Node-Red.

As for your questions about how to accomplish what you are looking to do I would be inclined to use Google Sheets. This would make it possible for Node-Red to log sensor telemetry to the sheet in real time.

Thank you Jon,
Travis Elliott

3

Hi Travis,

Ok so moving sideways for now - (need to get SQL logging up and running long term) - and attempting to get some nodes for interfacing with googlesheets running I have tried 3 approaches.

shown here in one flow.

2
21341×833 57.3 KB

Approach 1

“google-sheets” - does not seem to work,
this is the node labelled “sheet”
I’ve debugged it to the point where “debug” returns no errors but it doesn’t populate the spreadsheet, so don’t know whats up with that - i suspect this too needs node to be a newer version.

looks to be connected to the sheet fine

Any one out there with experience using this node ?
i suspect node.js needs updating for this to work - as per my previous post.

Approach 2

“gsheet” - looks to be a simpler node - and i have this functional, but this places all my payload from my environmental sensor in a vertical array - what i am ideally after is placing a time stamp to in column A, then temp in B, humidity in C etc etc.

Is there a way to get this to place the data in a row rather than a column ? - also if you manually edit the sheet as its parsing data - this seems to corrupt the flow and place data after the manual entry - and does not observe the rules in the node properties dialogue. (not robust if your sharing the sheet with editing privileges)

Anyone out there with this node running ? -

Approach 3

This is using node-red to fill in a google form - and then using google to format the data as a spreadsheet report.
Thus far this is the most successful of the 3 approaches i have attempted but clukny in the sense that its not directly interfacing with the spreadsheet. It is however reliable - robust and in the format that i want (mostly)
I’ve had this logging for 5 days - with one IoT environmental sensor and it works quite well.
for example if someone with editing privileges edits the googlesheet, and data is arriving the way the data is handled simply inserts a new row - so you still receive the remote data in the correct cells - and it just bumps whatever was in its way further down. (this is good - and what we want here)

5
5992×517 32.2 KB

6
6846×89 6.55 KB

Ok so i’m after some simple advice here. I need to have a proper time stamp - at the moment its random date / time. whats the best way to do this.

(my worry is the time stamp is prob generated by google) and not my node-red application.
i need this to be every minute at the zero second mark.

Best

Jon

4

You can use a function node to add a timestamp to the data being posted to the google sheet. Just use Javascript’s timestamp function to do this in the Node-Red function node. That should be relatively simple to do.

I will plan to write up an article/video on logging data to a googlesheet from the IoT Edge computer as this is certainly a viable use case.

5

Hey Travis,

Did you end up writing up an article/video about the logging of data to a google sheet from the IoT Edge Computer??

6

Hi @marc.hernandez ,

No, I have not done that yet. I do have a video on logging data to a CSV and uploading that to dropbox here:

This could more than likely be altered to upload to a google drive.
The node-red flow for that application video is available here:

7

Just found this article that is a pretty good on how to sync things into Google Sheets.