Export chart values over email (or comparable solution) Excel Node help?

[AndyDufresne422]

Hi, if this a repeat post then I apologize and all linked posts will be thoroughly explored, but I've been stumped for a few days about how to get a chart to export to a user friendly format (like excel or similar data).

I think the issue is in the formatting of the data by the chart but here's what's up:

Essentially I have three sensors that send readings every ten minutes over MQTT. (this works, no issues here)

These readings are then displayed on the dashboard via a line chart that shows the past 24 hours (or the last 144 data points). (again no issues here)

All three lines appear and the various data is stored (or buffered?) in the chart.

Issue - I would like to then send this information over email as an attachment in a file format that would be more user friendly. As I mentioned, I think my main issue that the chart stores the information in a way that I don't understand how to manipulate in order to create a CSV or "tabled" output that would be understood by a layman...

Ideally the output would have the sensor names along the top and time stamps down the first column and the sensor readings in the bulk of the sheet corresponding to the various topics/timestamps.

copy of the flow

[{"id":"99c0ee23.06ed58","type":"ui_button","z":"10db9dc0.71ea3a","name":"","group":"4db5964c.5b423","order":3,"width":3,"height":1,"passthru":false,"label":"reset","tooltip":"","color":"white","bgcolor":"transparent","icon":"","payload":"[]","payloadType":"json","topic":"chartreset","x":650,"y":580,"wires":[["b0733fc2.7269a"]]},{"id":"b0733fc2.7269a","type":"ui_chart","z":"10db9dc0.71ea3a","name":"","group":"4db5964c.5b423","order":1,"width":9,"height":5,"label":"","chartType":"line","legend":"true","xformat":"HH:mm","interpolate":"bezier","nodata":"Gathering Sensor Data","dot":true,"ymin":"0","ymax":"100","removeOlder":"24","removeOlderPoints":"144","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":790,"y":540,"wires":[["3ba52146.3d31a6"]]},{"id":"3ba52146.3d31a6","type":"json","z":"10db9dc0.71ea3a","name":"","property":"payload","action":"","pretty":true,"x":910,"y":540,"wires":[["48b5554a.371cb4"]]},{"id":"5b792ea4.a1c878","type":"link in","z":"10db9dc0.71ea3a","name":"Chart","links":["d2555637.7082a","a44355fa.322128","516c31d4.8553f"],"x":615,"y":540,"wires":[["b0733fc2.7269a"]]},{"id":"48b5554a.371cb4","type":"function","z":"10db9dc0.71ea3a","name":"","func":"if (msg.topic === \"send\") {\n    msg.payload = context.last;\n    return msg;\n}\nelse {\n    context.last = msg.payload;\n}\nreturn null;","outputs":1,"noerr":0,"x":1050,"y":580,"wires":[["2a7528c0.9ff83"]]},{"id":"5d9813a8.a5767c","type":"ui_button","z":"10db9dc0.71ea3a","name":"send","group":"4db5964c.5b423","order":5,"width":3,"height":1,"passthru":false,"label":"Send","tooltip":"Send a copy of chart data to the email address on file.","color":"white","bgcolor":"transparent","icon":"","payload":"","payloadType":"str","topic":"send","x":910,"y":580,"wires":[["48b5554a.371cb4"]]},{"id":"2a7528c0.9ff83","type":"file","z":"10db9dc0.71ea3a","name":"log file","filename":"/tmp/sensor_history.log","appendNewline":false,"createDir":true,"overwriteFile":"true","encoding":"none","x":1170,"y":580,"wires":[["e0acd2de.c73518"]]},{"id":"e0acd2de.c73518","type":"json","z":"10db9dc0.71ea3a","name":"","property":"payload","action":"","pretty":false,"x":1290,"y":580,"wires":[["e2dfb62d.02d19"]]},{"id":"e2dfb62d.02d19","type":"excel","z":"10db9dc0.71ea3a","name":"Excel","file":"/tmp/sensor_history.xlsx","x":1410,"y":580,"wires":[["ad7bc058.56008"]]},{"id":"ad7bc058.56008","type":"change","z":"10db9dc0.71ea3a","name":"","rules":[{"t":"move","p":"file","pt":"msg","to":"attachments.path","tot":"msg"},{"t":"set","p":"attachments.filename","pt":"msg","to":"sensor_history.xlsx","tot":"str"},{"t":"set","p":"topic","pt":"msg","to":"Chart Summary","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"Attached is your chart summary.","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1560,"y":580,"wires":[["628bfd2f.e686bc"]]},{"id":"628bfd2f.e686bc","type":"link out","z":"10db9dc0.71ea3a","name":"","links":["97f33d0.f3ff4c"],"x":1675,"y":580,"wires":[]},{"id":"4db5964c.5b423","type":"ui_group","z":"","name":"Sensor History","tab":"ff384e6b.a3f9","order":5,"disp":true,"width":"9","collapse":true},{"id":"ff384e6b.a3f9","type":"ui_tab","z":"","name":"WaterPi","icon":"fa-home","order":1,"disabled":false,"hidden":false}]

Current output attachment looks like this:

Maybe I just need to the "move" the payload around so that the Excel node can find it the right place?

(I read some suggestions that mentioned using a database and perhaps that's a better solution. Just unfamiliar with DBs. Is the database locally stored on the Node-red Host Device (in this case a RasPi3) or does this require another service?.)

[mtoko]

Hi

Try using InfluxDB and Grafana. This is what I used to get started, it works great

#255 Node-Red, InfluxDB, and Grafana Tutorial on a Raspberry Pi

[dceejay]

Or you could use a function to reformat the data to try to create a simple csv file
maybe something like

[{"id":"29269901.d23a06","type":"function","z":"d1e29928.8fa058","name":"Chart to CSV","func":"var d = msg.payload[0];\nvar cols = d.series;\nvar series = d.data;\nvar list = {};\n// iterate over all the series\nfor (var i=0; i<cols.length; i++) {\n    // and each data point\n    for (var j=0; j<series[i].length; j++) {\n        // create a new timestamped row if needed\n        if (!list[series[i][j].x]) { list[series[i][j].x] = new Array(d.series.length).fill('') }\n        // otherwise assign each datapoint to that timestamped row\n        list[series[i][j].x][i] = series[i][j].y; \n    }\n}\n\n// create a header line for csv\nvar res = [\"timestamp,\"+cols.join(',')];\n\nvar ordered = {};\n// sort the teimstamped rows into timestamp order\nObject.keys(list).sort().forEach(function(key) { \n    ordered[key] = list[key]; \n    // add the timestampt o the start of the row\n    ordered[key].unshift(parseInt(key));\n    // push them out as a comma separated string\n    res.push(ordered[key].join(','));\n});\n\n// finally join the rows as lines to complete the csv file\nconsole.log(res.join(\"\\n\"))\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":230,"y":1500,"wires":[["247ee957.a25736"]]}]

(Not tested with your data)

[AndyDufresne422]

Thanks for your responses,

@dceejay I'm looking at your solution, but when I debug before the function node and after the function node I have the same payload.

(I'm still new to this, so sorry if it's glaringly obvious and I'm missing it.) I kind of follow what the function node is doing. And your commented comments helped me understand it better (thanks for that), but I can't seem to get it to output a csv.

1. Should this function node be immediately after the chart?
2. Should this function be followed by a CSV node?
3. Should this function just go right to a file node? (Wouldn't that file grow significantly over time if never overwritten?)

[dceejay]

Hi yes right after chart and then to file set to overwrite as it sends the complete array every time.

[AndyDufresne422]

@dceejay thanks again for responding. I'm getting closer.

Another question:

The function node you posted ends with "return msg" should it be "return list"?

When I run it as is, I only get the same output as before the function node.

Thanks!

[dceejay]

Sorry, not quite. I left it printing it out to the console... You need that. So assign that final join back to msg.payload. then return it.

[AndyDufresne422]

So adding something like this:

msg.payload = res.join("\n")

at the end before return msg;

(Tried it, and it worked!)

Thanks so much!

[dceejay]

If you have lots of data points arriving then may be nicer to also add a trigger node to stop creating the file until the data is stable for a while (in this example 0.25 seconds)

[{"id":"247ee957.a25736","type":"debug","z":"d1e29928.8fa058","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":790,"y":1560,"wires":[]},{"id":"29269901.d23a06","type":"function","z":"d1e29928.8fa058","name":"Chart to CSV","func":"var d = msg.payload[0];\nvar cols = d.series;\nvar series = d.data;\nvar list = {};\n// iterate over all the series\nfor (var i=0; i<cols.length; i++) {\n    // and each data point\n    for (var j=0; j<series[i].length; j++) {\n        // create a new timestamped row if needed\n        if (!list[series[i][j].x]) { list[series[i][j].x] = new Array(d.series.length).fill('') }\n        // otherwise assign each datapoint to that timestamped row\n        list[series[i][j].x][i] = series[i][j].y; \n    }\n}\n\n// create a header line for csv\nvar res = [\"timestamp,\"+cols.join(',')];\n\nvar ordered = {};\n// sort the teimstamped rows into timestamp order\nObject.keys(list).sort().forEach(function(key) { \n    ordered[key] = list[key]; \n    // add the timestampt o the start of the row\n    ordered[key].unshift(parseInt(key));\n    // push them out as a comma separated string\n    res.push(ordered[key].join(','));\n});\n\n// finally join the rows as lines to complete the csv file\nmsg.payload = res.join(\"\\n\");\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":230,"y":1500,"wires":[["6da38900.fd0c48"]]},{"id":"6da38900.fd0c48","type":"trigger","z":"d1e29928.8fa058","name":"","op1":"","op2":"","op1type":"nul","op2type":"payl","duration":"250","extend":true,"units":"ms","reset":"","bytopic":"all","topic":"topic","outputs":1,"x":500,"y":1500,"wires":[["247ee957.a25736","3df76c65.9b73a4"]]},{"id":"3df76c65.9b73a4","type":"file","z":"d1e29928.8fa058","name":"","filename":"/tmp/mychart.csv","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":810,"y":1500,"wires":[[]]}]