Using Decoder and Google Sheets

1

Hello,

I have been posting my TTN data to Google Sheets with good results. I would like to be able to pull the decoded payload data using the Decoder function from ttn rather than the raw bytes. Do you know if and how the Decoder can be invoked to pass the decoded data to google sheets?

2

The Decoder is always executed, regardless any further handling. (You should be able to see this in the Data pages in TTN Console.) Assuming you’re using the HTTP Integration, but same goes for the MQTT Data API: the result of the Decoder is always present in the payload_fields attribute.

1 Like
3

Thank you.

Yes it’s http integration but the correct field names I should use to get the decoded data is not obvious. Is there an example in the documentation somewhere?

4

As every application is different, you won’t find examples for your specific payload.

I’d just log the incoming JSON payload, or temporary route the integration to, say, https://requestbin.com or https://rbaskets.in, or just put the full request into a cell on your Google Sheet.

Example HTTP Integration payload 
{
  "app_id": "arjanvanb-app-ttnode",
  "dev_id": "arjanvanb-ttnode-1",
  "hardware_serial": "0004A30B001C1D1E",
  "port": 5,
  "counter": 9,
  "payload_raw": "EqYAJwha",
  "payload_fields": {
    "battery": 4774,
    "event": "motion",
    "light": 39,
    "temperature": 21.38
  },
  "metadata": {
    "time": "2020-03-30T12:48:56.661160996Z",
    "frequency": 868.5,
    "modulation": "LORA",
    "data_rate": "SF7BW125",
    "coding_rate": "4/5",
    "gateways": [
      {
        "gtw_id": "arjanvanb-gw-1",
        "gtw_trusted": true,
        "timestamp": 1897504995,
        "time": "2020-03-30T12:48:56Z",
        "channel": 2,
        "rssi": -53,
        "snr": 7.5,
        "rf_chain": 1,
        "latitude": 52.374485,
        "longitude": 4.635738,
        "location_source": "registry"
      }
    ]
  },
  "downlink_url": "https://integrations.thethingsnetwork.org/ttn-eu/api/v2/down/arjanvanb-app-ttnode/arjanvanb-http-ttnnode?key=ttn-account-v2.REDACTED"
}
5

Thank you.

I think I get it now. The payload_fields is an array of fields and the array is available for download. After that I need to parse out the fields. I presume they are in the same format as they have been loaded in by the Decoder, ie a string for the name and a numeric for the value.

I will see what I can do with that.

Thanks again.

6

Did you look at the example payload? Just to be sure, the following in the JSON HTTP payload is not called “an array”:

{
  ...,
  "payload_fields": {
    "battery": 4774,
    "event": "motion",
    "light": 39,
    "temperature": 21.38
  }
}

(The square brackets in the "gateways": [...] property within metadata do define an array.)

How this JSON text from the HTTP request is available in your code depends on that code. Sometimes it’s converted into an object on the fly for you, sometimes you’ll need to add some code to parse it and get such object out of it to easily access its properties.

I’d very much recommend to not parse the JSON text yourself, but convert it into an object right away. In JavaScript that’s typically using JSON.parse(), but might be different in other languages. Google’s Apps Script uses the Google V8 JavaScript Runtime (or Firefox Rhino for existing pre-February 2020 scripts), for which parsing of JSON text is the same, like:

function doPost(e) {
  const data = JSON.parse(e.postData.contents);
  const fields = data.payload_fields;
  const temperature = fields.temperature;
  // Or:
  // const temperature = data.payload_fields.temperature;
  ...
}