How to Create HTML Charts Using Chart.js and Google Sheets

Over the spring I was tasked to create an interactive dashboard, using data from Google Sheets, that could be maintained and updated by people with no knowledge of HTML. While a daunting task, I eventually found a way to create a dashboard soley using HTML and Javascript, that could be updated directly by altering the data on the Google Sheet. In this tutorial I will walk through an example of how to do this yourself.

Step 1. Create a Google Sheet to store data, here’s the one I will be using in this tutorial. This data is from the NOAA site that shows the annual temperature variance in San Francisco.

https://docs.google.com/spreadsheets/d/1YFu3mEJzUXsxWVFDTVK9RhZF91VlwzDB-3F4WzHITq4/edit#gid=0

Step 2. Ensure that the Google Sheet permissions are set to viewable by anybody with a link.

Step 3. Create HTML file with the following source code. I will walk though the key portions of the code after the snippet.

The key portions of the code are:

a. Including the Chart.js and Google Charts Javascript libraries. I’m using the Google Charts libary to get access to Visualization Query language that lets me select the data.

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>

b. Query the Google Sheet for data using Google Visualization Query language.

function initChart() {
    URL = "https://docs.google.com/spreadsheets/d/1YFu3mEJzUXsxWVFDTVK9RhZF91VlwzDB-3F4WzHITq4/edit#gid=0";
    var query = new google.visualization.Query(URL);
    query.setQuery('select *');
    query.send(function(response) {
      handleQueryResponse(response);
    });
  }

c. In the response, I read the data as a JSON object as I found that it gave me the most flexibility to parse the data as I needed it.

var data = response.getDataTable();
.
.
.
dataj = JSON.parse(data.toJSON());
.
.
.
for (i = 0; i < dataj.rows.length; i++) {
      const series_data = [];
      for (j = 1; j < dataj.rows[i].c.length; j++) {
        if (dataj.rows[i].c[j] != null) {
          if (dataj.rows[i].c[j].v != null) {
            series_data.push(dataj.rows[i].c[j].v);
          } else {
            series_data.push(0);
          }
        }
}
.
.
.
datasets.push(dataset);

d. Setup a Chart.js “Chart” object with the right datasets and labels

const chartdata = {
      labels: labels,
      datasets: datasets
    };
var canvas = document.getElementById("myChart");
var setup = {
      type: 'line',
      data: chartdata,
      options: {
        plugins: {
          title: {
            display: true,
            text: dataj.cols[0].label
          }
        },
        responsive: true,
      }
    }
chart = new Chart(canvas, setup);

Step 4. In order to view the HTML file, you will need to access it from a web server. If you are familiar with Python, and want to create a local web server to try this you can enter the following command in the shell:

python -m http.server

Step 5. In a browser enter the location of the file URL and you should see the following: