Skip to main content

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.



Comments

Popular posts from this blog

DIY Sonoff RF Bridge

Tasmota recently supported the RF bridge by iTead which allowed for RF 433 remote devices to be controlled/controlling using Sonoff products. e.g A handphone can be used to control a remote RF device or an RF remote control can use to control a WIFI enabled device. By using Tasmota, I can control the device using MQTT. This idea fits exactly to my smart home plan. Here is the schematic: I cheated a bit by having ESP01 mounted on a breakout board. This breakout board converts all the external pins to 5v which simplified the wiring and I do not need to use a voltage level shifter. Moreover, the Arduino 3.3v power does not have enough current to power the ESP reliably and I have to power it via the 5V from Arduino Uno. Next, I used an Arduino Uno prototype shield with a tiny breadboard to hold all the components together. For the RF 433 transmitter/receiver, I am using the development kits when I purchased the all-in-one Arduino development kits. An antenna is added for more sensi

Using ESP-Link transparent bridge (ESP-01 and Arduino Pro Mini)

Recently stumbled across an interesting open source project ESP-Link . Its main purpose is to network-enable a non-network microcontroller (MCU) such as Arduino Uno, Pro mini or Nano using ESP8266. The author termed it as "Transparent Bridge". The ESP and MCU  communicate via the serial link and there is a companion Arduino library EL-Client  for the MCU to connect up the network using MQTT, REST, TCP and UDP. Setup I have put together an ESP-01 and an Arduino Pro Mini for this experiment. I have chosen a 3.3 version Pro mini so that I do not need to do any voltage level shifting between the I/O pins. In order to have a stable voltage source, the ESP8266 is powered by Pro Mini and the Pro Mini "RAW" pin is connected to a 5v USB power source. The RAW pin can take voltage up to 12V. The reset pin of Pro Mini is connected to GPIO 0 of ESP-01. This enables the ESP-01 to reset the Pro Mini.   I have linked up an APDS 9960 sensor to it and periodically se

Using ESP-Link transparent bridge (ATmega2560+ESP8266 board)

I have found this interesting board selling on Aliexpress website. It is an Arduino Mega 2650 with a built-in ESP8266 which allows the Mega to connect to wifi. This board has cut down a lot of wiring and bring out interesting possibilities. One of this is to connect up both the microcontrollers using ESP-link firmware. I have an earlier blog post  that described how to wire up an ESP-01 and Arduino Pro Mini. One of the tricky parts in this setup is the sequence of flashing the ESP firmware. Flashing the onboard ESP8266 requires some setting changes to the dip switches. The following are the sequences which I get both the microcontrollers to talk to each other. Set the jumper 5,6,7 to ON and the rest OFF. This will set the ESP to update mode. I am using version 3.0.14  and flashed the ESP using the following parameters: esptool --port com26 --baud 230400 write_flash -fm dio -fs 4MB -ff 80m 0x0 boot_v1.6.bin 0x1000 user1.bin 0x3fc000 esp_init_data_default.bin 0x3fe0