Split single JSON payload (from MQTT) into google sheets columns

General
1

Hello,

I am sorry for the novice question, but hoping someone can help.

I receive a JSON payload over MQTT from Chirpstack. What I would like to do is place these values into a google sheets spreadsheet.

So far I have managed to make the connection and the data is flowing, however everything is in the same cell as follows

{"applicationID":"15","applicationName":"Network-testing","deviceName":"AN106","deviceProfileName":"winextfieldtester","deviceProfileID":"REDACTED","devEUI":"REDACTED","rxInfo":[{"gatewayID":"REDACTED","uplinkID":"REDACTED","name":"Laird_1","rssi":-67,"loRaSNR":7,"location":{"latitude":49.16510074802484,"longitude":REDACTED,"altitude":5}}],"txInfo":{"frequency":868300000,"dr":1},"adr":false,"fCnt":589,"fPort":5,"data":"AAAAAAAA//8=","object":{"accuracy":0,"altitude":65535,"latitude":0,"longitude":0}}

Ideally, what I would like as columns is:

Timestamp, Latitude, Longitude, rssi, altitude and gateway name

The JSON looks like this:

{
  "applicationID" : "15",
  "applicationName" : "Network-testing",
  "deviceName" : "AN106",
  "deviceProfileName" : "winextfieldtester",
  "deviceProfileID" : "REDACTED",
  "devEUI" : "REDACTED",
  "rxInfo" : [ {
    "gatewayID" : "REDACTED",
    "uplinkID" : "c837a066-a1c8-4520-829d-c403249a2181",
    "name" : "Laird_1",
    "rssi" : -67,
    "loRaSNR" : 7,
    "location" : {
      "latitude" : REDACTED,
      "longitude" : REDACTED,
      "altitude" : 5
    }
  } ],
  "txInfo" : {
    "frequency" : 868300000,
    "dr" : 1
  },
  "adr" : false,
  "fCnt" : 589,
  "fPort" : 5,
  "data" : "AAAAAAAA//8=",
  "object" : {
    "accuracy" : 0,
    "altitude" : 65535,
    "latitude" : 0,
    "longitude" : 0
  }
}

I would be most grateful for any help anyone may be able to offer.

Many thanks

2

You need to make a JSON array of your selected columns data and send that to the node-red-contrib-google-sheets node in "Append Row" mode
See below

[{"id":"e19b4edf7547e01d","type":"GSheet","z":"35893b86c5936e2e","creds":"","method":"append","action":"","sheet":"**********************","cells":"Feuille 1!A1","flatten":false,"name":"Append Row","x":530,"y":180,"wires":[["37d24edb1b7ff661"]]},{"id":"37d24edb1b7ff661","type":"debug","z":"35893b86c5936e2e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":710,"y":180,"wires":[]},{"id":"b5f80f6b87fefb0c","type":"inject","z":"35893b86c5936e2e","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"applicationID\":\"15\",\"applicationName\":\"Network-testing\",\"deviceName\":\"AN106\",\"deviceProfileName\":\"winextfieldtester\",\"deviceProfileID\":\"REDACTED\",\"devEUI\":\"REDACTED\",\"rxInfo\":[{\"gatewayID\":\"REDACTED\",\"uplinkID\":\"c837a066-a1c8-4520-829d-c403249a2181\",\"name\":\"Laird_1\",\"rssi\":-67,\"loRaSNR\":7,\"location\":{\"latitude\":45.12345,\"longitude\":2.12345,\"altitude\":5}}],\"txInfo\":{\"frequency\":868300000,\"dr\":1},\"adr\":false,\"fCnt\":589,\"fPort\":5,\"data\":\"AAAAAAAA//8=\",\"object\":{\"accuracy\":0,\"altitude\":65535,\"latitude\":0,\"longitude\":0}}","payloadType":"json","x":150,"y":180,"wires":[["b2a0521fac14010e"]]},{"id":"b2a0521fac14010e","type":"change","z":"35893b86c5936e2e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t    $now(),\t    msg.payload.rxInfo[0].location.latitude,\t    msg.payload.rxInfo[0].location.longitude,\t    msg.payload.rxInfo[0].location.altitude,\t    msg.payload.rxInfo[0].rssi,\t    msg.payload.rxInfo[0].gatewayID   \t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":180,"wires":[["e19b4edf7547e01d"]]}]

Of course my GSheet node here attached is not properly configured
and you have to configure your GSheet account as explained in the node's documentation

1 Like
3

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.