Use Google Spreadsheet as a timeseries storage (IOT initiation)

December 31, 2016

This project was created for 3 trainees (13 years old) hired for a one week initiation. The goal was to explain my work by the simplest way.
The main condition was : NO CODE ! This means no C#, no Java, no scripts, no SQL...

All my trainees knows Excel, so let's try to build something with it !

Spreadsheet solution

I used Google Spreadsheet for 3 reasons :

Build an API in front of spreadsheet

Google Script

Go to : https://www.google.com/script/start/

A Google Script is not a macro for a spreadsheet ! You can connect all Google components with a Javascript API.
Those scripts are hosted by Google and can be called by a WebService, a special event or manually.
In our case, we need to expose an endpoint that takes parameters and field them into a spreadsheet.
In my first version, This is what I wrote : 

  var d = new Date();
  values[0] = d.getTime();
  values[1] = request.parameter.device;
  values[2] = request.parameter.proximity;
  values[3] = request.parameter.light;
  values[4] = request.parameter.ax.replace(".", ",");
  values[5] = request.parameter.ay.replace(".", ",");
  values[6] = request.parameter.az.replace(".", ",");
  values[7] = request.parameter.rotationa;
  values[8] = request.parameter.rotationb;
  values[9] = request.parameter.rotationg;
  addLine(values);

It was perfect for my needs (see bellow the webapp).
https://github.com/mathieupassenaud/timeseriesGoogleSpreadsheet/blob/master/script.js

How to push to my spreadsheet ? 

function addLine(values) {  
  var sheet = SpreadsheetApp.openById(sheetID);
  sheet.appendRow(values); 
}

Retreive your sheetId directly in the URL :

GET and POST

Just keep it simple ! Remember your first Java Servlet : doGet and doPost. Here it is the same :

function doGet(request){ }  
function doPost(request){ }

HTTP parameters (in POST or GET) are located in the “request” variable. Keep in mind that is Javascript, feel free to retrieve all your arguments and not only what I’ve done in my example.

Publish your API

In the “publish” menu, use “publish as a web application, not “as an API”.

It generates an URL, keep in mind that you need to redeploy a new version each time you edit your script.

A debug mode is available. You need to create an endpoint to fill all request params.

WebApp for sensors

We need a simple webapplication running on a smartphone that uses different sensors.
With the last version of Firefox for android you can easily access to ambiant light, poximity sensor or accelerometers.
That's perfect for my needs ! Let see how it is build : 

<html>  
<head>  
<title>Push values</title>  
<script src="https://code.jquery.com/jquery-3.1.1.min.js" integrity="sha256-hVVnYaiADRTO2PzUGmuLJr8BLUSjGIZsDYGmIJLv2b8=" crossorigin="anonymous"></script>  
</head>  
<body>  
<h3>Pushing values to hub</h3>  
ID : <input type="text" id="id"></input>  
<input type="button" onClick="go()" value="start"/>  
<input type="button" onClick="stop()" value="stop"/>  
</body>  
<script langage="javascript">  
        var light = 0;
        var proximity = 0;
        var interval;
        var ax = 0;
        var ay = 0;
        var az = 0;
        var rotationAlpha = 0;
        var rotationBeta = 0;
        var rotationGamma = 0;


        if (window.DeviceMotionEvent != undefined) {
            window.ondevicemotion = function(e) {
                ax = e.accelerationIncludingGravity.x;
                ay = e.accelerationIncludingGravity.y;
                az = e.accelerationIncludingGravity.z;
                if ( e.rotationRate ) {
                    rotationAlpha = e.rotationRate.alpha;
                    rotationBeta = e.rotationRate.beta;
                    rotationGamma = e.rotationRate.gamma;
                }       
            }
        }
        window.addEventListener('devicelight', function(event) {
                // Get the ambient light level in lux.
                light = event.value;
        });
        // An event listener for a DeviceProximityEvent.
        window.addEventListener('deviceproximity', function(event) {
            // The device proximity (in cm).
            proximity = event.value;
        });
        function go(){
            interval = setInterval("loop()", 1000);
        }
        function stop(){
            clearInterval(interval);
        }
        function loop(){
            var device = $("#id").val();
            navigator.vibrate(50);
            var battery = -1;
            if(navigator.battery != null){
                battery = navigator.battery.level;
            } 
            var args = "?device="+device+"&proximity="+proximity+"&battery="+battery+"&light="+light+"&ax="+ax+"&ay="+ay+"&az="+az+"&rotationa="+rotationAlpha+"&rotationb="+rotationBeta+"&rotationg="+rotationGamma;

            var url = "<put script URL here>"
            $.ajax({
                  url: url+""+args
            });
        }
    </script>
</html>

https://github.com/mathieupassenaud/timeseriesGoogleSpreadsheet/blob/master/index.html

Spreadsheet operations

This is what it looks like in the first sheet. We will consider this as the storage, all raw data are located here and sorted by timestamp.

Now the main work is data extraction.
First, we can implements a "last" function : 

=index(Data!E:E ; COUNTA(Data!E:E) ; 1)

This show the last value for the column E in the "Data" sheet.
How it works ?
"index" takes 3 parameters :

Do the same for min and max for each column.

Build some graphs

(sorry, my Google Account is in french language)

Advanced functions

Using "current" values make opportunities.

Conditions are simple to understand : 

=IF(B3<0; "on screen"; "on back")

=IF( AND(B3<0 ;'Luminosité valeurs'!B1  < 5 ) ; "Phone is on his screen"; "Phone is updown")

=IF(A1>0 ; "phone is on left side" ; "phone is on right side")