Helium Network GPS Tracking Directly In Google Sheets

Introduction

Some called her midlife crisis; I call her my very own space program. Blondie is a beautiful 1966 Cessna 150 single engine plane. She made an appearance on CNN back in 2017 during the coverage of Hurricane Harvey.

Like a Phoenix rising from the ashes (or water in this case), Blondie became airworthy again. That close call made me realize how much I'm attached to that plane. Last year, I built Plain And Simple Plane Locator project to keep track of my bird's whereabouts. Apparently, I was using the wrong technology. Azure IoT Hub and Azure IoT Central were just too complicated to use and broke my code in couple of updates. On top of that, I had to use my phone's hotspot to transmit data which is an overkill for what I'm trying to accomplish here.

Game Plan

Using nothing but Helium Console, we will take GPS coordinates received from LoRaWAN Development Board - B-L072Z-LRWAN1 and send it to Google Sheets.

To make all this work, I needed a Helium hotspot so I'm trying to convince my neighbor to buy one like I convinced him to buy a wireless router. Finally, I found a perfect location with good old free Wi-Fi and Helium coverage.

Hardware

B-L072Z-LRWAN1 can be used right out of the package; just plug in Micro USB cable and follow the tutorial. Speaking of package, that's exactly what I used as a case to keep the project simple.

Initially, I was planning to use the GPS functionality of Sony Spresense like I did in my previous project. Since the board is currently tied up in another project, I decided to use an old Bluetooth GPS receiver. I had to break it open and find a way to get GPS coordinates via UART. The GPS receiver times out after 10 minutes if it does not pair with any Bluetooth device. So, in addition to 5V, GND, and Tx pins I needed a fourth wire to reset the GPS receiver.

Software - Helium Console

Using the Helium Console was very easy. We need to create a device to interface with the physical board specified by three parameters: devEui, appEui, and appKey. Next we create HTTP Integration to post to an endpoint (looks like this https://script.google.com/macros/s/.../exec). That endpoint is where we have the script to receive data from Helium and populate Google Sheets. This is very powerful because we don't need to use PushingBox, Pipedream, or any other data integration platform to send data to Google Sheets. We also need to add a label to connect device to integration. The same label also used to connect to a function that we create to decode the base64 encoded string.

Software - Google Apps Script

In an effort to avoid reinventing the wheel on every project, please refer to the next video if you're not familiar with automating Google Sheets. We'll need to change couple of things. First, instead of using his Code.gs, we have to use the code that has been uploaded to Github below. Second, we'll need to use the actual Spreadsheet ID instead of SpreadsheetID in line

var GS = SpreadsheetApp.openById('SpreadsheetID')

Software - Arduino IDE

Steps have been covered in details in the tutorial. Again, instead of using their sketch we have to use the code that has been uploaded to Github below then fill out devEui, appEui, and appKey values from Helium Console.

Final Result

A copy of the trips discussed below have been uploaded to Github as well. The last column have been included to show hotspots associated with each data record. B-L072Z-LRWAN1 is transmitting GPS coordinates every 10 seconds which has been done for demonstration only. In real life, we don't need that many data points especially for a slow plane.

Demo

I took the data from Google Sheets and upload to GPS Visualizer for two different trips. The left side of the images came from GPS Visualizer while the right side is map obtained from FlightAware.

My outbound flight started from the airport at the top to the airport at the bottom of the image below. Notice that we have a gap every 10 minutes for GPS reset that we discussed earlier.

My inbound flight started from the airport at the bottom to the airport at the top of the image below. Notice that Helium tracked a little longer than FlightAware as indicated by the hook showing the flight all the way thru final approach almost to touch down.

I tried my best to lineup Google My Maps with Helium coverage map to show the closest hotspot to the airport at the bottom of the image below. We have more hotspots around the top airport than we have around the bottom one and that's why we got more details at the top than at the bottom.

Conclusion

From what I've seen so far I can tell you that Helium is the best thing ever happened to IoT since Al Gore invented the Internet. This project is one of the first 10 projects submitted to the contest and it could have been easily submitted sooner had I stayed on task instead of exploring what else can I do with this versatile board. This has been a successful simple project with room for growth in hotspot coverage and data transmitted. The area in the south does not have a lot of hotspots, but it has many large companies such as schlumberger, Center Point, Mammoet, etc. Once they get on board, we can get better coverage for General Aviation in sparsely populated areas.