This is part 3 of a 3-part series. Part 1 covers reading temperature data via MQTT, and part 2 covers writing it to a Google Sheet. In this part, you’ll read the stored data using the GSheet node, transform it, and display it as a chart in the Node-RED Dashboard.
Requirements
- Raspberry PLC or any machine running Node-RED
- Google spreadsheet with data from part 2
- node-red-dashboard package installed
Building the flow
The flow reads a range of cells from the spreadsheet, converts the timestamps and values into chart-ready objects, and sends them to a dashboard chart node. The structure is: inject > GSheet (get) > function > chart.
Drag an inject node and connect it to a GSheet node. Configure the GSheet node the same way as in Part 2 (same credentials, same SpreadsheetID), but set the method to “Get Cells” and the Cells field to Sheet1!A1:B100 (adjust the row limit to fit your data). This time, uncheck the “Flatten Matrix” option.
Add a node function to convert each file of the speadsheet in a pair x/y for the char node:
var array = new Array(msg.payload.length);
for (let i = 0; i < msg.payload.length; i++) {
let obj = new Object();
obj.x = new Date(parseInt(msg.payload[i][0]) + 7.2e+6).toUTCString();
obj.y = parseFloat(msg.payload[i][1]);
array[i] = obj;
}
msg.payload = [{
"series": ["Excel Counter"],
"data": [array],
"labels": [""]
}];
return msg;The adjustment of the time stamp (7.2e+6) solves a 2 hour gap. Adjust it according to your needs.
Connect the node function to a char node de node-red-dashbaord and create your desired setup.
Deploy and activate the node inject to load the graphics with the data.
Flow JSON ready to import
[{"id":"74b73d59f656b253","type":"GSheet","z":"3dcd15c2de093c64","creds":"18961e3a36024be7","method":"get","action":"","sheet":"","cells":"Sheet1!A1:B100","flatten":false,"name":"","x":890,"y":320,"wires":[["a4ac8141b8d2b57b"]]},{"id":"523d3498c3bfcc8a","type":"inject","z":"3dcd15c2de093c64","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":740,"y":320,"wires":[["74b73d59f656b253"]]},{"id":"a4ac8141b8d2b57b","type":"function","z":"3dcd15c2de093c64","name":"","func":"var array = new Array(msg.payload.length);\nfor (let i = 0; i < msg.payload.length; i++) {\n let obj = new Object();\n obj.x = new Date(parseInt(msg.payload[i][0]) + 7.2e+6).toUTCString();\n obj.y = parseFloat(msg.payload[i][1]);\n array[i] = obj;\n}\nmsg.payload = [{\n \"series\": [\"Excel Counter\"],\n \"data\": [array],\n \"labels\": [\"\"]\n}];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1040,"y":320,"wires":[["834aa049eb78ac19"]]},{"id":"834aa049eb78ac19","type":"ui_chart","z":"3dcd15c2de093c64","name":"","group":"d4c392ad32a37c94","order":1,"width":0,"height":0,"label":"Chart","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":true,"ymin":"0","ymax":"40","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"x":1170,"y":320,"wires":[[]]},{"id":"18961e3a36024be7","type":"gauth","name":"Unknown"},{"id":"d4c392ad32a37c94","type":"ui_group","name":"Chart","tab":"bce747bd77dec32e","order":1,"disp":true,"width":"19","collapse":false},{"id":"bce747bd77dec32e","type":"ui_tab","name":"GSheet","icon":"dashboard","disabled":false,"hidden":false}]
Obtaining data from Google spreadsheets using Node-RED