Data in -> MySql and Google Spreadsheet out

Takes an input from a variety of sources and saves it where necessary.

Providing that the input fulfills the same logic that the parser requires (and that the inject node shows), then the data will be saved to both MySQL database and the Google Spreadsheet via Form entry.

You need either or both a MySQL database setup and a Google Form setup with details from each. The Google Form takes a little more time as it requires you to access the form (ideally in Chrome), right click and look for the entry.XXXX's that are assigned to each of the questions within the Form.

I use this coupled with an Arduino that outputs the data where necessary. The Arduino selects, based on connection available, which method it wants to upload its data. Data can be anything, this flow is for a temperature and moisture sensor.

You can use this to also log multiple Sensors, just update the parser to extract a module number from the string it receives, then, update your input accordingly.

"Timestamp" isnt required for the Google Form as it is saved by the Form itself upon posting. "ID" auto updates in MySQL, I have a simple update formula macro in Google to achieve the same goal.

[{"id":"f83191ca.07ce7","type":"serial-port","serialport":"/dev/ttyUSB0","serialbaud":"57600","databits":"8","parity":"none","stopbits":"1","newline":"\\n","bin":"false","out":"char","addchar":"false"},{"id":"94098a36.6bf678","type":"delay","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"5","rateUnits":"minute","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":792.3928833007812,"y":227.8095703125,"z":"db95823b.246a8","wires":[["7e322dd0.81cdd4"]]},{"id":"ddc1362b.223ec8","type":"inject","name":"","topic":"","payload":"id=x,timestamp=17/08/2014 12:26:27,temp1=7.6,moi1=1.4","payloadType":"string","repeat":"","crontab":"","once":false,"x":283.0000305175781,"y":75.52383422851562,"z":"db95823b.246a8","wires":[["19d983b0.e6267c"]]},{"id":"b667b59a.499848","type":"mysql","mydb":"","name":"","x":1234.1785888671875,"y":412.0952453613281,"z":"db95823b.246a8","wires":[[]]},{"id":"ebdb3ab8.1424c8","type":"http in","name":"","url":"/ArduinoData","method":"post","x":163,"y":109,"z":"db95823b.246a8","wires":[["19d983b0.e6267c"]]},{"id":"4dd6fbe9.b22904","type":"tcp in","server":"server","host":"","port":"8762","datamode":"single","datatype":"utf8","newline":"","topic":"","name":"8762","base64":false,"x":128,"y":176,"z":"db95823b.246a8","wires":[["19d983b0.e6267c"]]},{"id":"19d983b0.e6267c","type":"function","name":"Parser","func":"var data = msg.payload.split(\",\");\nmsg.payload = {};\nmsg.payload.id = parseInt(data[0].split(\"=\")[1]);\nmsg.payload.timestamp = parseInt(data[1].split(\"=\")[1]);\nmsg.payload.temp1 = parseFloat(data[2].split(\"=\")[1]);\nmsg.payload.moi1 = parseFloat(data[3].split(\"=\")[1]);\n//msg.payload.module = parseInt(data[4].split(\"=\")[1]);\nreturn msg;","outputs":1,"x":651,"y":228,"z":"db95823b.246a8","wires":[["94098a36.6bf678"]]},{"id":"fed914a.f0126e8","type":"comment","name":"Google Form","info":"Replace \"XXXXXXXX\" at the start of the URL with your Form key\n\nReplace the \"entry.XXXX\" with the entry numbers found when you\nopen the Form in Chrome, Press F12 or right click, inspect \nelement.\n\nCTRL-F to find, look for \"entry\", you'll have one for each\nof the Form fields.","x":1253.3333740234375,"y":237.22225952148438,"z":"db95823b.246a8","wires":[]},{"id":"cb0281a3.34fd8","type":"http request","name":"","method":"POST","url":"https://docs.google.com/forms/d/XXXXXX/formResponse?entry_190108279={{payload.id}}&entry_275456604={{payload.temp1}}&entry_1392836693={{payload.moi1}}","x":1255,"y":276,"z":"db95823b.246a8","wires":[[]]},{"id":"7e322dd0.81cdd4","type":"change","action":"replace","property":"msg.payload.timestamp","from":"","to":"DEFAULT","reg":false,"name":"","x":735,"y":269,"z":"db95823b.246a8","wires":[["e78c1371.1873f"]]},{"id":"e78c1371.1873f","type":"change","action":"replace","property":"msg.payload.id","from":"","to":"NULL","reg":false,"name":"","x":762,"y":315,"z":"db95823b.246a8","wires":[["cb0281a3.34fd8","86ea8a10.791578","68fd2047.9702e"]]},{"id":"b33ba331.4cc46","type":"debug","name":"MySQL","active":true,"console":"false","complete":"false","x":1236,"y":451,"z":"db95823b.246a8","wires":[]},{"id":"86ea8a10.791578","type":"change","action":"change","property":"payload.temp1","from":"temp1=","to":"","reg":false,"name":"","x":747,"y":361,"z":"db95823b.246a8","wires":[["36a3fc9e.c95c04"]]},{"id":"36a3fc9e.c95c04","type":"change","action":"change","property":"payload.moi1","from":"moi1=","to":"","reg":false,"name":"","x":750,"y":410,"z":"db95823b.246a8","wires":[["e61caaf.f19e358"]]},{"id":"e61caaf.f19e358","type":"function","name":"","func":"    msg.topic = \"INSERT INTO `sensor_bank_1`(`id`, `timestamp`, `temp1`, `moi1`) VALUES (NULL,DEFAULT,\" + msg.payload.temp1 + \",\" + msg.payload.moi1 + \")\";\n    return msg;","outputs":1,"x":813,"y":459,"z":"db95823b.246a8","wires":[["b33ba331.4cc46","b667b59a.499848"]]},{"id":"68fd2047.9702e","type":"debug","name":"Google Form Data","active":true,"console":"false","complete":"false","x":1275,"y":314,"z":"db95823b.246a8","wires":[]},{"id":"f6df9f75.09206","type":"serial in","name":"","serial":"f83191ca.07ce7","x":143,"y":142,"z":"db95823b.246a8","wires":[["19d983b0.e6267c"]]},{"id":"80c44932.7f3bb8","type":"comment","name":"MySQL","info":"Fill in the details for your MySQL DB","x":1235,"y":374,"z":"db95823b.246a8","wires":[]}]
nuriyan

Flow Info

created 3 years, 4 months ago

Node Types

Core
  • change (x4)
  • comment (x2)
  • debug (x2)
  • delay (x1)
  • function (x2)
  • http in (x1)
  • http request (x1)
  • inject (x1)
  • serial in (x1)
  • serial-port (x1)
  • tcp in (x1)
Other

Tags

  • MySQL
  • Google
  • Spreadsheets
  • CSV
  • TCP
  • HTTP
  • POST
  • Serial
  • Arduino
Copy this flow JSON to your clipboard and then import into Node-RED using the Import From > Clipboard (Ctrl-I) menu option