Http integration in v3

1

Hey, I was using http integration in v2 to add sensor data to a google spreadsheet. I can’t find this option in v3. Can it be done with webhooks? Pls provide more info on how it can be done
Thanks,
Hassan

2

v2 HTTP Integration = v3 WebHooks

3

I am having trouble setting up the webhook. I set the base url as the webapp link for the google sheet. As i want to post sensor data in the google sheet, i enabled the uplink message. However, nothing is getting posted in the google sheet.

4

There wasn’t an HTTP Integration in v2 for that and there isn’t one in v3 either.

A WebHook sends a web request to a server with the information in JSON format - if you send it to a Google Sheet you’ll need to write some code to process the JSON.

5

Thanks for your replies. Please note that i had everything working properly in v2 and i am unable to recreate it in v3. I added a screenshot of the configuration i used in v2.

The part of the google sheet webapp script that adds the payload to the sheet:

// push name
var raw = Utilities.base64Decode(jsonData.payload_raw);
var decoded = Utilities.newBlob(raw).getDataAsString();
row.push(decoded);

Does this need to be changed? add_http

6

There is a big change in the format of the JSON but without knowing what the whole script is it will be a bit hard to comment.

I did have such a script for an integration but that was on v2 so anything I’ve got will need updating.

7

Here the reference on the updated JSON format in V3.
Data Formats | The Things Stack for LoRaWAN (thethingsindustries.com)

8

I am using this app script for the google sheet: lora_project_mkrwan1310/mkrwan1310_proj.gs at main · Mouser-Electronics/lora_project_mkrwan1310 · GitHub

1 Like
9

I’m on mobile internet until Saturday, I can see a use for this for many of us in the near future is if you hang on, I can update it for the new format.

11

hello, is there an update on this? I’m running into the same issue. The data is not being passed from the TTN backend to the google sheets. thanks

12

I’ve not had an evening to have a go at this but it is on the reminder list.

[I’m a forum member too, not paid staff]

13

Here’s an updated / tidied script for the Google Sheet:

var SHEET_NAME = "Sheet1";

function doGet(e){
  return HtmlService.createHtmlOutput("Google Sheets Webhook v0.1");
}

function doPost(e){

  var theJSON = JSON.parse(e.postData.contents);

  var theSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);

  var data = [];
  
  var theDate = Utilities.formatDate(new Date(), 'GMT-0', 'dd/MM/yyyy HH:mm:ss');
  data.push(theDate);

  data.push(theJSON.uplink_message.f_port);
  data.push(theJSON.uplink_message.f_cnt);
  data.push(theJSON.uplink_message.frm_payload);

  var decoded = Utilities.base64Decode(theJSON.uplink_message.frm_payload);
  var bytesToHex = decoded.reduce(function(str, chr) {
    chr = (chr < 0 ? chr + 256 : chr).toString(16);
    return str + (chr.length==1?'0':'') + chr;
  },''); 
  data.push(bytesToHex);

  theSheet.appendRow(data);

}

To add other fields from the JSON payload, add more data.push lines. You can re-arrange them to suit your needs with the columns. The TTS docs has examples of the JSON message.

1 Like
14

Thank you - this was very helpful.

Wasn’t exactly sure what to do with setting up the webhook in TTS, but used the custom webhook template, selected json format, added base url from google sheet/script, and enabled uplink message with no optional path and it seems to work.

15

I was trying to modify this script further as suggested and added a push line above the appendRow line for the rssi as below but nothing shows up in the sheet for rssi. The script runs and the other items are pushed fine so I’m not sure what’s going on with rssi.

var SHEET_NAME = "Sheet1";

function doGet(e){
  return HtmlService.createHtmlOutput("Google Sheets Webhook v0.1");
}

function doPost(e){

  var theJSON = JSON.parse(e.postData.contents);

  var theSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);

  var data = [];
  
  data.push(theJSON.end_device_ids.device_id);
  
  var theDate = Utilities.formatDate(new Date(), 'GMT-5', 'MM/dd/yyyy HH:mm:ss');
  data.push(theDate);

  data.push(theJSON.received_at);

  data.push(theJSON.uplink_message.f_cnt);
  data.push(theJSON.uplink_message.frm_payload);

  var decoded = Utilities.base64Decode(theJSON.uplink_message.frm_payload);
  var bytesToHex = decoded.reduce(function(str, chr) {
    chr = (chr < 0 ? chr + 256 : chr).toString(16);
    return str + (chr.length==1?'0':'') + chr;
  },''); 
  data.push(bytesToHex);

  data.push(theJSON.uplink_message.decoded_payload.analog_in_1);
  data.push(theJSON.uplink_message.decoded_payload.analog_in_2);
  data.push(theJSON.uplink_message.decoded_payload.analog_in_3);

  data.push(theJSON.uplink_message.rx_metadata.rssi);

  theSheet.appendRow(data);

}
16

This leads to an array of the gateways that heard the uplink with their respective rssi & snr’s.

If you want to use the first one, then you need:

theJSON.uplink_message.rx_metadata[0].rssi

but I’m not in a position to test it right now, so YMMV

1 Like
17

the rx_metadata is an array. If you want the rssi for the first gateway (as that is the only one guaranteed to be present) you probably need something like

data.push(theJSON.uplink_message.rx_metadata[0].rssi);
1 Like
18

Perfect- that was it - thank you both!