Routing Sensor Data From Helium Devices to Google Sheets

Overview

Earlier this week, I received my own Helium Developer Kit. The kit contains everything you need to get started with the Helium Network: a LoRaWAN Development Board and two sensor shields. In an earlier project, I showed how to send coordinate data from the GNSS1A1 GPS shield to a Cayenne myDevices dashboard to build a basic LoRa-based asset tracker. While the myDevices platform is excellent for getting simple data-logging projects off the ground quickly, you are at the mercy of Cayenne's relatively limited analytics and exporting capabilities. To perform your own analysis, whether that be a simple box-and-whisker plot in Excel or a Recurrent Neural Network in Tensorflow, your only option (within the Cayenne framework) is to manually export a CSV file, which somewhat defeats the purpose of real-time data collection. It's my understanding that Cayenne used to support a REST API for this purpose, but for some reason or another (perhaps it didn't make financial sense to freely host terabytes of time-series data for users who never accessed the site...) they decided to decommission the platform earlier this year.

Fortunately, there are other (free) options for prototypers who do not want to go through the trouble and expense of hosting a full-scale database on AWS or Azure just to monitor the humidity levels in their backyard garden. In this tutorial, I'll show you how to transmit environmental sensor data from the X-NUCLEO-IKS01A3 shield over the Helium Network and log it in a Google Sheet for rapid, real-time analysis. On a high-level, the workflow looks like this:

This tutorial assumes that you already have a Helium Console account and have enough coverage to reliably ping the network. In other words, think of this as an extension of the Quickstart guide in the Helium docs. Let's get started.

1. Upload the Arduino script

The code for the X-NUCLEO-IKS01A3 will look a lot like the starter script in the quickstart guide. You will just need to include the libraries for the individual sensors and CayenneLPP to package the buffers. In my case, I'm using the temperature, pressure, and humidity sensors, but the shield also has a gyroscope and accelerometer. Usage for these inputs is very similar - check out this "Hello World" sketch. The heart of the code is within the do_send(osjob_t *j) function:

void do_send(osjob_t *j) {
// Check if there is not a current TX/RX job running
    if (LMIC.opmode & OP_TXRXPEND) {
        Serial.println(F("OP_TXRXPEND, not sending"));
    } else {
        float humidity = 0, temperature = 0;
        HumTemp->GetHumidity(&humidity);
        HumTemp->GetTemperature(&temperature);
        // Read pressure and temperature.
        float pressure = 0;
        PressTemp->GetPressure(&pressure);
        lpp.reset();
        lpp.addTemperature(1, temperature);
        lpp.addBarometricPressure(2, pressure);
        lpp.addRelativeHumidity(3, humidity);
        //LMIC_setTxData2(1, mydata, sizeof(mydata) - 1, 0);
        LMIC_setTxData2(1, lpp.getBuffer(), lpp.getSize(), 0);
        Serial.println(F("Packet queued"));
    }
        // Next TX is scheduled after TX_COMPLETE event.
}

"lpp" is the CayenneLPP packet object, which is a LoRaWAN-friendly encoder for Arduino sensor data. It supports a number of common data formats, including temperature, pressure, humidity, GPS, and accelerometers, as well as arbitrary analog/digital outputs. Check out the CayenneLPP docs if you're interested in learning more. The full code for setting up your device is included in this Github repo. Don't forget to include your own DevEUI, AppEUI, and AppKey on lines 19, 24, & 29. Mount your shield and upload the script through the Arduino IDE, and you should see packets start to propagate in "Devices" page on the Helium Console.

2. Payload Decoder Function

At this point, the payloads of sensor data are encoded in a base64 string. If you were to inspect the uplink JSON files, you would see something like this:

{
    "payload": AWcBAAJzJkMDaG0EAwAA
}

To get this string back into a readable format, our decoder function will first use a base64 decoder to extract the individual bytes (in Python, we would use something like the built-in base64.b64decode(payload_string)). Then, it will translate the bytes back into float-type sensor readings according to the encoding format used to generate the packet. Now, you could write your own decoder from scratch, but fortunately, CayenneLPP and Helium have saved us the trouble. In the Helium Console, open up the “Functions” tab, and click “Create New Function”. Give it a name, choose the “Decoder” function type and the “CayenneLPP” format. If you don’t already have a Label assigned to your device, you’ll have to quickly create it in the “Labels” tab and also assign it to your new function. Once you’re satisfied, click “Save Function”.

And that’s it! Now, if you were to inspect the JSON packets, you’ll now notice a “decoded” field within the body, which will make parsing the relevant data much more straightforward:

"decoded": {
    "payload": [
    {
        "channel": 1,
        "name": "temperature",
        "type": 103,
        "unit": "celcius",
        "value": 25.6
    },
    {
        "channel": 2,
        "name": "humidity",
        "type": 104,
        "unit": "hPa",
        "value": 979.5
    },
    {
        "channel": 3,
        "name": "humidity",
        "type": 104,
        "unit": "percent",
        "value": 54.5
    }
    ]

All that from an 11-byte payload! Now let’s route those packets somewhere useful.

3. Pipedream Workflow

Pipedream is a data integration platform that is used (among other things) to connect event triggers to a variety of actions through custom recipes. The graphical interface and expression-based framework allow you to create sophisticated “Workflows” without writing any code. In this way, it’s similar to an Azure Logic App. They have built-in support for hundreds of apps, like Twitter, Spotify, and Slack, and you can even set up bots for SMS and email notifications. In this case, we’ll be tapping into the Google Sheets API to add new rows to a spreadsheet. Go to Pipedream, then navigate to the Workflows tab and create a new workflow. For the trigger, search for “HTTP / Webhook” to generate an endpoint URL for our POST requests.

Copy the webhook URL and head back to the Helium Console. Go to the Integrations tab and add a Custom HTTP Integration. Paste the endpoint under Connection Details, add a name, and apply the same label that you gave to your device and decoder function.

Now, whenever your device sends a payload over the Helium Network, it will send the data via a POST request to our Pipedream webhook. For the ensuing steps, it will be helpful if you have already sent some JSON files over the network, so that Pipedream has an idea of how to parse them. To route the information to Google Sheets, head back to Pipedream, click the plus sign under your trigger and search for “Add Row to a Sheet”. You’ll have to authenticate the requests by logging into your Google Account, then create a blank Sheet and copy your spreadsheet ID, which is this portion of the web address:

Paste this ID into the required field within your Pipedream action, along with the Sheet Name, which is probably still “Sheet1”. Lastly, you just have to define exactly what information will be sent to your spreadsheet. For my purposes, I’d like to log the decoded sensor values and a UNIX timestamp. This is where is helps to already have some JSON files to work with, because Pipedream will use past requests to give you some user-friendly previews of the data you intend to send. All in all, the row parameters should look something like this (the blank column will make sense in a second):

Once you’ve deployed your workflow, you can either enable it and wait for your LoRa device to send new payloads through Helium or, for faster feedback, use the “Send a Test Event” feature. If all goes well, you should start to see new rows propagating your spreadsheet!

To convert the UNIX timestamp into a more interpretable UTC-based date, apply this Excel function to column B:

Add some fancy charts or your own desired statistical metrics and watch the payloads come in. Before long, you will have your own custom (and most importantly, free) real-time analytics dashboard!

Recap

To wrap things up, let's consider what we just accomplished:

• We connected low-power environmental sensors to send data to the internet through the LoRaWAN-based Helium Network.
• To transmit them over vast distances (relative to WiFi), we encoded the sensor payloads into data-efficient CayenneLPP packets, then decoded them with a function in the Helium Console.
• With a Pipedream Workflow, we routed the POST requests from a Helium HTTP Integration through a webhook endpoint, and then tapped into the Google Sheets API to automatically send the decoded sensor data to a spreadsheet for application-specific, real-time analysis.