Esta es la parte 3 de una serie de 3. La parte 1 cubre la lectura de datos de temperatura vía MQTT y la part 2 su escritura en una hoja de cálculo de Google. Aquí leerás los datos almacenados usando el nodo GSheet, los transformarás y los mostrarás como gráfico en el Dashboard de Node-RED.
Requisitos
- Raspberry PLC o cualquier máquina que ejecute Node-RED
- Hoja de cálculo de Google con datos de la parte 2
- Paquete node-red-dashboard instalado
Construir el flujo
El flujo lee un rango de celdas de la hoja de cálculo, convierte las marcas de tiempo y los valores en objetos aptos para el gráfico y los envía a un nodo chart del dashboard. La estructura es: 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.
JSON del flujo listo para importar
[{"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}]
Obtener datos de Google Sheets con Node-RED