Using Google App Scripts to collect telemetry data - part 1

This is one of Google best-kept secrets, Google App Script (GAS). GAS is a Javascript engine that can link various Google front and back end services together. e.g. Periodic scanning a GDrive folder, detect a CSV file and insert into MySQL. The best part of this, it is FREE !!!

Having said that, is there a limit? Yes, there is. Quotas for App Scripts

In the next few articles, I am going to demonstrate how we can collect the IoT data into Google Sheet and dynamically visualise the data using Google Sheet and/or Data Studio.

For this simple demo, I will use an ESP32 and runs Mongoose OS to act as the bridge between GAS and local MQTT server and send temperature/humidity data from DHT11 to Google Sheet.

As written in my previous article on Mongoose OS, the connectivity aspect of Mongoose is very powerful. it provides simple and easy to use Javascript APIs which glued the underlying C/C++ library. To read the sensor value and to Google Apps Script, it needs less than 20 lines of code. The code can be found here. Google has good tutorials on Apps Scripts and how to use it to integrate to Google Sheet.

On the App Script side, I have created a simple webapp to listen to the incoming request. To prevent spamming, I will match with a secret key in the request data.

The App Script code:

function doGet(request) {

    var sharedkey = request.parameter.data1;
    var deviceData = request.parameter.data2;
    Logger.log(sharedkey);

    if (sharedkey != "SOMESHAREDKEY")
    {
      return HtmlService.createHtmlOutput("error");
    }

    processDeviceData(deviceData);
    return HtmlService.createHtmlOutput("success");
};


function processDeviceData(data){
  var sheetid = '1CXEVLhK-Tv_iTJKIOnSmwCLA2ORFwJxMOwswRV0J434';
  var sheet = SpreadsheetApp.openById(sheetid).getSheetByName('Device Data');

  var jsonObj = JSON.parse(data);
  var deviceid = jsonObj.deviceid;
  var temp = jsonObj.temp;
  var humidity = jsonObj.humidity;
  var heatidx = jsonObj.heatidx;
  var todaystr = Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss");

  sheet.appendRow([todaystr,deviceid,temp, humidity, heatidx]);
}

When the request is received from the ESP32, first it compares the shared key, if it matches, the script will insert the data to Google Sheet.

A simple charting can be done by using the internal charting capability of the Google Sheet. Here we have it, simple and fast!! The next few parts I will describe in details on how this can be done.

Part 2 Using Google App Scripts to collect telemetry data

Update 1 (21/12/2017) - The ESP32 and the App script has been running for almost a month. As the DHT11 sensor is kept indoors, the temperature derivation is minimum. Below is the chart plotted using GSheet. One of the interesting property observed about Google Sheet is the auto expansion of the data range. as the script is inserting the data at the end of the sheet, the chart range expands by itself.