Most of the world runs on Spreadsheets. But stuff looks nicer on the web.

I'm going to build a web application with the following characteristics:

  • Driven by data from a Google Spreadsheet
  • Running with HTML, CSS and JavaScript
  • No server-side technology, except for a webserver serving static HTML, CSS and JavaScript

Why do I want this? It avoids a lot of complications and boiler plating, but it's also lots of fun. Furthermore, the hosting I have is getting a little low on resources.

The Motivation

I like to have some insight in data, in the form of stats. For some reason, that is pleasant to me.

If all goes well, I will soon be driving an electric car and I'd like to see some details. What are the average trips, how fast does the battery discharge in various circumstances, etc etc ...

There's probably stuff for that around, but it's fun to be free in whatever you want to learn.

The Plan

First of all I want a Google Spreadsheet with data of each trip. Date, time, outside temperature, total km, use of highway, use of airco, ...

Secondly, I want a webpage where I can see whatever analysis I feel like doing.

For that second part, I'd like to do everything in HTML/CSS/JavaScript that fetches and parses the data from Google directly.

As far as the back-end is concerned, all I need is a web server that can serve static HTML, CSS and JavaScript files.

Given my scenario, this seems reasonable. I don't require complex data storage (databases), user management (logins), content creation, ... all I need to do is show some data.

But I wonder if not having a back-end at all is an advantage or disadvantage. I made a similar app before, for our office foosball stats - but that one had a little bit of back-end code.

Why Google Sheets?

  • I don't have to worry about setting up and running a Database
  • I don't have to create data entry forms
  • I don't have to worry about authentication / authorization (google does it for me!)

In general, it's less stuff to think about.

First Steps: Setting up the Spreadsheet

Create the Sheet

  • Login to Google Sheets
  • Create a new Sheet
  • On the first row, put the column headers
  • Add some data

In my case, I will be having the following columns for now:

  • Date
  • Start time
  • Start km
  • Start charge
  • Start temp
  • End time
  • End km
  • End charge
  • POB (people on board)
  • Eco-mode?
  • B-mode?
  • Airco?
  • Highway?

Publish the Google Sheet as JSON

Google sheets allows you to publish the sheet and consume the data - without authentication - as JSON.

Here's a good post on how to set it up:

I don't feel like reproducing that post, so please check it out. I made sure it's on archive.org if the link dies.

Bottom line, here's my JSON endpoint:

First Steps: Loading the JSON in a webpage

For the UI, I will have to use some third party tools. Something like bootstrap for the looks, and something like Vue.js for hooking up the data to the UI.

For now, all I care about is an index.html that's able to load the data. To load the data, I use the "fetch API": fetch

I have one HTML file:

<html>
<head>
  <script src="js/app.js"></script>
</head>

<body>

</body>
</html>

And one js/app.js file:

let url = 'https://spreadsheets.google.com/feeds/cells/16hJ_67ox89L3DuVuc8KWAGMYFwb58rVP2fjCkc2AoGE/1/public/full?alt=json';

fetch(url)
  .then(
    function(response) {
        response.json().then(function(data) {
          console.log(data);
        });
    }
  );
 
document.write('Hello world!');

All it does is load the data and log it to the console. I also write "Hello world!" to the screen. If I see that then I know the JavaScript hasn't failed.

First Steps: Storing the code

To make it easy to load the code, and update it, I will put it all in a GitHub project.

On the webserver I can then just do a git pull to update the app.

I add the two files, but use the following folder structure:

  • html/index.html
  • html/js/app.js

Commit and push:

First Steps: Hosting

I already have hosting set up for this blog, my second blog and some additional apps. Each website is running in a docker container and gets its own hostname under aaronlenoir.com.

To host this, it's enough to set up an nginx server and point it to the html folder of the repo. Setting that up happens in the docker-compose configuration.

Not going into the full details, this is what I have to add:

  edrive:
    image: nginx:alpine
    restart: always
    depends_on:
      - "nginx-proxy"
    ports:
      - 127.0.0.1:8140:80
    volumes:
      - ./edrive/html:/usr/share/nginx/html:ro
    environment:
      - url=https://edrive.aaronlenoir.com
      - VIRTUAL_HOST=edrive.aaronlenoir.com
      - LETSENCRYPT_HOST=edrive.aaronlenoir.com
      - LETSENCRYPT_EMAIL=info@aaronlenoir.com

This ensures that all requests for the domain "edrive.aaronlenoir.com" are passed to my new nginx instance.

The server is serving all files in the folder ./edrive/html and its sub-folders.

Also notice the two LETSENCRYPT environment variables. These are used by the nginx-proxy-companion docker container to fetch an SSL certificate from Let's Encrypt.

I also had to add an A-record to my DNS, which I do via Namecheap:

A-Record in DNS configuration for "edrive.aaronlenoir.com"

Conclusion

I've set up the base to start working, iteratively, on my simple stats apps for my car data:

Using Google Sheets is a way of getting some data to your web application, without having to worry about: data entry, authentication, storage, back-end hosting, ...

Hosting requirements are limited to hosting static files. I can even run the app directly from my local disk.

I can push a change to github, and pull from there to update the app, wherever it's running.

Now to actually parse some data ...