TTN to Google Sheets

1

Hello,

I’m recently working on a project, which has the goal to show data (as diagramm) in the companys intranet.
Im using a Heltec ESP32 to receive the data. At the moment im using the Tago.io integration to visualize it.
But now i want to integrate google sheets to display a diagramm on the website.

Has anyone an idea how i can transfer my data from TTN to Google App Scripts as easy as possible?

I tried many scripts, but none of them ist working.

If you have an completly different idea without Google Sheets, tell me :wink:

Thanks for helping me :smile:

2

I remember posting a link to a how too about 1.5-2 years back for getting data into ggogle sheets - use forum search with squix & google sheets as terms and should find as way forward? May need to do work to get from datalines in sheets and simple graphing ther eto integrate to your website.

1 Like
3
2 Likes
4

I did this many many moons ago: https://github.com/cBashTN/TheThingsNetwork2GoogleSpreadsheet

Since then the Google Sheets API and TTN changed.

Nowadays, I’d probably try to decrypt the payload on TTN and forward it to IFTTT and there let it store in a sheet row.

1 Like
5

with IFTTT.com you can do this, if you’re message has max 3 values, that is.You need the “webhooks” to do this.
André

6

Hey guys,
Does anyone has a solution for automatically get all the data to google spreadsheet, that works now?

I looked at the solutions that are in this thread, but it looks that the ttn v3 is quite a bit different then previously. Not sure if IFTTT is also still relevant, from browsing the page it does not really looks so.

7

Add as an AppScript to a 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);

}
8

@ozolniece I posted about this one some 3-4 years back if I recall… havent looked at whether still ok for V3. Note Daniel’s caution wrt access va the URL and potential security issue unless authenticated.

Might be worth giving it a try again :wink: I dont have time right now to crawl over the code or attempt to use as busy with other projecs… Previous HTTP Integration now under Webhooks…Custom Webhook…

9

Thanks for the reply guys.
@descartes I don’t understand - where is the script supposed to get the variable data from? That’s exactly the part I don’t get - how to get a the data variable from TTN. The latter formatting part I’d manage afterwards.

@Jeff-UK Thanks I had found and tried this solution but nothing is happening. Not sure if I didn’t create the Webhooks correctly or done something else wrong (even though the link was quite well described, thanks for that) somehow or the changes there have been too large.

10

TTS sends JSON to the Google URL, the script captures that data and puts it in the sheet:

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

Probably easiest if you travel hopeful and give it a try - add as an AppScript to your sheet, deploy, take the URL Google gives you and set up a webhook with it and, at minimum, turn on uplinks.

The script was copied out of a live setup last night, so it’s about as oven fresh as such scripts are going to get. Here are some older ones: GitHub - descartes/TheThingsStack-Integration-Starters: Starter / Template code for various The Things Stack (v3) integrations

I’ve presented Integrations at a previous conference and taught them at last years TTN Summer Academy, so whilst I’m not a doctor, you can trust me!

1 Like
11

Thanks, I didn’t mean that I don’t trust your code, but I just didn’t understand it, thanks for taking the time and adding a little explanation. Just tried it and it works just fine for me - so thanks for sharing it.

2 Likes
12

Hey, I hope I’m not bothering you, but I can’t seem to get the metadata out of this. Is there something I’m missing?
Right now, just trying to get anything out, but I don’t get what’s wrong with this line to just get RSSI out.

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

13

The software developers nightmare word!

The GitHub link above has examples, like this:

as the signal info is for each gateway so you need to reference the array, ideally iterate around all the gateway entries to find the best signal, but TL;DR:

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

which I haven’t tried as Google Sheets isn’t a robust storage solution for me, so it’s just there for the occasional convenience. But getting out any of the data is just normal JSON path constructions for objects & arrays, so plenty of resources to read for that.

2 Likes