Google Sheets as database

Turning Google Sheets into a Backend Database with MetricsGraphics.js at http://metricsgraphicsjs.org/

by Alex Cowan

HTML

<link rel="stylesheet" href="http://metricsgraphicsjs.org/css/metricsgraphics.css">
<script src="http://kovalent.co/mksht/dollaraday/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.5/d3.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/metrics-graphics/2.3.0/metricsgraphics.js"></script>
<p class="summary"><span id="total"></span> Miles driven,
    <span id="buffer"></span> mile buffer.</p>

<p><a href="https://docs.google.com/forms/d/1ejZAk8NJhe_dTJbabLPglieQmf68L1K7F03Ilh42PyQ/viewform">Enter more data.</a></p>

<div id="chart">
</div>

<p>
    <a href="kovalent.co/blog/google-docs-as-a-backend">Wait does this use google docs as a backend? Yes. How this was created.</a></p>

CSS

* {
    font-family: monospace;
}
a {
    color: rgb(98, 128, 205);
}
p {
    text-align: center;
}
#chart {
    margin: 0 auto;
    text-align: center;
}

JavaScript

var JSONURL = 'https://spreadsheets.google.com/feeds/list/1e4bE5Xk1w1d-N3-xWCYk9ilUA2kFn2TH629lrT_w-Gk/1/public/basic?alt=json';

function drawChart(rows){
    
    console.log(rows);
    
    for (var i = 0; i < rows.length; i++){
        rows[i].timestamp = new Date(rows[i].timestamp);
    }
    
    MG.data_graphic({
      title: "Odometer Reading vs. Date",
      data: rows,
      width: 400,
      height: 250,
      target: "#chart",
      x_accessor: "timestamp",
      y_accessor: "odometer",
      interpolate: "monotone"
    });
}

function callback(data){
    var rows = [];
    var cells = data.feed.entry;
    
    for (var i = 0; i < cells.length; i++){
        var rowObj = {};
      rowObj.timestamp = cells[i].title.$t;
        var rowCols = cells[i].content.$t.split(',');
        for (var j = 0; j < rowCols.length; j++){
            var keyVal = rowCols[j].split(':');
            rowObj[keyVal[0].trim()] = keyVal[1].trim();
        }
        rows.push(rowObj);
    }
    
    var mostRecent = rows[rows.length - 1];
    $('#total').text(mostRecent.odometer);
    $('#buffer').text(mostRecent.buffer);

    drawChart(rows);

}

$(document).ready(function(){
    
    $.ajax({
        url:JSONURL,
        success: function(data){
            callback(data);
        }
    });

});