In my last post I showed you how to configure the Seeeduino LoRaWAN module to connect to TheThingsNetwork (TTN). But this is only half the rent. You want to do something with the data the node sends, right? This post will show you how you can log the messages from the node to a Google Spreadsheet. It is relatively easy to do but not save, since it lacks security features.
In the first post of this series we had a look at a very specific LoRaWAN module, the Seeeduino LoRaWAN. We configured the module as well as TheThingsNetwork to allow the module to send data to the network. But TTN is just the network infrastructure. It doesn’t allow you to do anything with the data the nodes sends you beyond having a quick peek. Now I want to to show you how you can build a simple system which logs the data in a spread sheet. To get started you need:
- Configured Seeeduino LoRaWAN module (read more)
- Configured TheThingsNetwork account (read more)
- Google Docs account
Setting Up the Spreadsheet
Log into your Google Docs account and create an new Spreadsheet
Give the Spreadsheet a meaningful name, e.g. LoRaLogger
Then go to Tools > Script Editor…
Replace the few lines of code with the code below:
// 2017 by Daniel Eichhorn, https://blog.squix.org // Inspired by https://gist.github.com/bmcbride/7069aebd643944c9ee8b // Create or open an existing Sheet and click Tools > Script editor and enter the code below // 1. Enter sheet name where data is to be written below var SHEET_NAME = "Sheet1"; // 2. Run > setup // 3. Publish > Deploy as web app // - enter Project Version name and click 'Save New Version' // - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously) // 4. Copy the 'Current web app URL' and post this in your form/script action var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service // If you don't want to expose either GET or POST methods you can comment out the appropriate function function doGet(e){ return handleResponse(e); } function doPost(e){ return handleResponse(e); } function handleResponse(e) { var lock = LockService.getPublicLock(); lock.waitLock(30000); // wait 30 seconds before conceding defeat. try { // next set where we write the data - you could write to multiple/alternate destinations var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key")); var sheet = doc.getSheetByName(SHEET_NAME); // we'll assume header is in row 1 but you can override with header_row in GET/POST data //var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; var nextRow = sheet.getLastRow()+1; // get next row var row = []; var headerRow = []; // loop through the header columns var jsonData = JSON.parse(e.postData.contents); headerRow.push("jsonData.app_id"); headerRow.push("jsonData.dev_id"); headerRow.push("jsonData.hardware_serial"); headerRow.push("jsonData.port"); headerRow.push("jsonData.counter"); headerRow.push("jsonData.payload_raw"); headerRow.push("jsonData.payload_decoded"); headerRow.push("jsonData.metadata.time"); headerRow.push("jsonData.metadata.frequency"); headerRow.push("jsonData.metadata.modulation"); headerRow.push("jsonData.metadata.data_rate"); headerRow.push("jsonData.metadata.coding_rate"); headerRow.push("jsonData.metadata.downlink_url"); for (var i = 0; i < jsonData.metadata.gateways.length; i++) { var gateway = jsonData.metadata.gateways[i]; headerRow.push("gateway.gtw_id"); headerRow.push("gateway.timestamp"); headerRow.push("gateway.channel"); headerRow.push("gateway.rssi"); headerRow.push("gateway.snr"); headerRow.push("gateway.latitude"); headerRow.push("gateway.longitude"); headerRow.push("gateway.altitude"); } sheet.getRange(1, 1, 1, headerRow.length).setValues([headerRow]); row.push(jsonData.app_id); row.push(jsonData.dev_id); row.push(jsonData.hardware_serial); row.push(jsonData.port); row.push(jsonData.counter); row.push(jsonData.payload_raw); var raw = Utilities.base64Decode(jsonData.payload_raw); var decoded = Utilities.newBlob(raw).getDataAsString(); row.push(decoded); row.push(jsonData.metadata.time); row.push(jsonData.metadata.frequency); row.push(jsonData.metadata.modulation); row.push(jsonData.metadata.data_rate); row.push(jsonData.metadata.coding_rate); row.push(jsonData.metadata.downlink_url); for (var i = 0; i < jsonData.metadata.gateways.length; i++) { var gateway = jsonData.metadata.gateways[i]; row.push(gateway.gtw_id); row.push(gateway.timestamp); row.push(gateway.channel); row.push(gateway.rssi); row.push(gateway.snr); row.push(gateway.latitude); row.push(gateway.longitude); row.push(gateway.altitude); } // more efficient to set values as [][] array than individually sheet.getRange(nextRow, 1, 1, row.length).setValues([row]); // return json success results return ContentService .createTextOutput(JSON.stringify({"result":"success", "row": nextRow})) .setMimeType(ContentService.MimeType.JSON); } catch(e) { // if error return this return ContentService .createTextOutput(JSON.stringify({"result":"error", "error": e})) .setMimeType(ContentService.MimeType.JSON); } finally { //release lock lock.releaseLock(); } } function setup() { var doc = SpreadsheetApp.getActiveSpreadsheet(); SCRIPT_PROP.setProperty("key", doc.getId()); }
Goto Run > Setup
Publish > Deploy as web app
- enter Project Version name and click ‘Save New Version’
- set security level and enable service (most likely execute as ‘me’ and access ‘anyone, even anonymously)
- Copy the ‘Current web app URL’ and post this in your form/script action
Give permissions to the script:
Copy the URL:
Setting Up TTN
Now we have to tell TheThingsNetwork where to write the data to. Go to your application in the TTN console and click on the “Integration” button. Then select the HTTP Integration:
Now paste the URL you earlier copied from the Google Spreadsheet to the URL form field and set the other fields like here:
Leave the other fields as they are and click on “Add integration”. There you go! Now plug in your configured Seeeduino (or any other TTN node) and you’ll get a new row for each message!
Summary
I hope you found this really easy and it worked for you. We created a simple IoT application which can log (sensor) data and node meta data. If you know how to write app script you can easily extend the code by sending push notifications to your cell phone in special situations. Or you can add a chart to the spread sheet analyzing signal quality, etc.
One word of warning: the current script is not really save, anyone knowing the URL can send data to your spread sheet. We could improve it by adding an authentication header (in the currently) empty TTN form field and check it in our App script code.
Do you have questions about this post? Go over to https://support.squix.org and place it there so everyone can profit!
How can we show GPS information on maps ? can you please explain that
Hi,
Thank you for sharing this useful method with everyone! Unfortunately i followed this article word by word but i am unable to get this to work.
New payloads keep coming to my TTN console but i don’t see any updates appearing on my Google Spreadsheet?
I have attached my work so far as screenshots in this Google Drive Folder:
https://drive.google.com/drive/folders/1yMBjV_UaAKtLu-m5_Tf6fiQp-wZxzFyf?usp=sharing
Can you please tell me how to fix the problem? Or what i am doing wrong?
Best Regards,
Dilip
Hi Dilip,
Have you had any luck? I as well can see updates from my arduino payloads to the TTN gateway but see nothing happening on the spreadsheet. Google says the app was launched but when I clicked on the link to test it then get sent to https://script.googleusercontent.com/macros/echo?user_content_key=jLU6C… where it shows me a “Result : ‘Error'”
i have same result
Hi Dilip,
This is maybe a bit late but are you sure you ran the setup in the script?
In the script editor you have to go to ‘Run’ –> ‘Run Function’ –> ‘setup’.
Regards,
Alexander
How can i plot data in the spreadsheet from a cayenne lpp field ?
I tried this solution to locate via GPS my Seeeduino, but the spreadsheet stays empty.
——————————————————————
headerRow.push(“jsonData.fields.gps_1.latitude”);
headerRow.push(“jsonData.fields.gps_1.altitude”);
….
row.push(“jsonData.fields.gps_1.latitude”);
row.push(“jsonData.fields.gps_1.altitude”);
——————————————————————
Without these four lines, the plot works perfectly.
Best Regards,
Falc
thank you , this looked promising… but I get a pop up telling me “There are no available integrations for handler meshed-handler. Check back later!”.
I gotta keep reading <:-|
Hi,
Thank you so much for webpage.
I have followed procedure as it is mentioned above. I’m keep posting the data to the backend but I Could not see any packet in my spreadsheet. Actually where can I see my data.
Or
Do I need to close the spreadsheet for dumping the data ?
Hi tried to get my TTN device data into google spreadsheet with this script , but no any data on google side, neither no any headers generated after running the script.
if someone have solved this problem………?????/
works fine!! if not, don’t forget to setProperty key