Tech Diary

I have written about using  App Scripts to collect telemetry data  and the ease of integrating it to Google Sheet to chart the temperature and humidity reading. In this post, I shall describe the approach using a CSV uploaded via Google File Stream and a scheduled job which is written in App Script to load the data into Big Query. This approach is very good for integrating the transactional capability of ERP system and the Analytics capability of Visualisation system (Data Studio). ERP system has rich built-in business process workflow and transactional control, When performing Dashboard and Data Analytics functionality, it will slow down the entire system. In this case, it may be better for analytics features to be performed by another system. Fortunately, most ERP system is capable of auto execute a query to export CSV data. I have made use of this capability to import the daily files generated by ERP to Big Query via Google Drive. This CSV file is picked up by an App Scri...

[update 30 May 2019: This post has been outdated. Google has released  clasp  which allows the development of app script locally] Google App Script (GAS) is a very versatile tool for writing scripts to control many of the GSuites and Google Cloud services. One can build many small applications using GAS and hosts it seamlessly on Google Drive for free. It has a built-in editor, debugging tool and logging of logs to StackDriver. The main drawback I had with GAS is the difficulty to create save the scripts to source control like Github. Fortunately, danthareja  shared his tools to read/write app script into GDrive seamlessly, but the script has a limitation, it can only handle a single project. This is because it is writing the OAuth information into %UserProfile%\.gapp . I wrote a simple wrapper script pull.bat and push.bat that synchronous the App Script.  pull.bat copy .gapps %UserProfile%\.gapps gapps init [REPLACE YOU PROJECTID] -o C...

There are many ESP32 form factor breakout boards that can be purchased cheaply. In this mini project, I am using a form factor that looks like Wemos D1 Mini, so that I can reuse my shield without any wiring. Although no wiring is required, the pinout is different from Wemos D1 Mini. The DHT11 GPIO is 16 in ESP32 instead of 2 (note that Mongoose is using port pin definition. in Arduino, it is defined as D4 but it is mapped IO2).   ESP32 mini board is quite warm when in operation. I have put a dummy shield in between to reduce the interference it may cause to the DHT11 temperature reading. The source code for the mongoose-os javascript can be found on  github . The setup of Mongoose OS is straightforward. Go to  Mongoose  site and download the binary, select the board type and change the init.js. Part 1 Using Google App Scripts to collect telemetry data

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...