Learn How to Use Google Sheets with D3.js and Google Visualization

by
Advertisements

Hello, Guys welcome back Here learn How to Use Google Sheets with D3.js and Google Visualization. The D3.js visualisation library can be used to generate beautiful graphs and visualisations from external data sources such as CSV files and JSON data.

For example, this D3.js animation embedded within the Google Sheets connected with the COVID-19 tracker project visualises the progression of Coronavirus cases in India over time. It makes use of the Google Visualization API, D3.js, and Mike Bostock, the inventor of D3.jsfantastic,’s Bar Chart Race component.

Google Sheets and D3.js

This tutorial will show you how to leverage data from Google Spreadsheets to build charts with D3.js and the Visualization API. Because the data is fetched in real-time, any changes to the data in your Google Sheets are reflected in the graph as well.

Advertisements
D3.js Chart with Google Sheets

Step 1: Make the Google Sheets public

Make your Google Spreadsheet public – You can publish the sheet with “anyone who has the link can view” access or make it public so that search engines can locate your sheet with the Charts data.

We are using this Google Sheet for this tutorial.

Advertisements

Also see: RegEx – How to Extract Video ID from YouTube URLs

Step 2: Load the Libraries in HTML

In your index.html file, include D3.js (v5) and the Google charts library. The index.js file contains the JavaScript for rendering the D3 chart.

<!DOCTYPE html>
<html>
  <head>
    <script src="https://www.gstatic.com/charts/loader.js"></script>
    <script src="https://d3js.org/d3.v5.min.js"></script>
  </head>

  <body>
    <svg></svg>
  </body>
  <script src="./index.js"></script>
</html>

Step 3: Initialize the Google Visualization API

Enter the URL of your published Google Spreadsheet here (the gid should point to the sheet that has the data). The Google Visualization API Query Language (reference) allows you to describe columns that should be utilised to obtain data from the Google sheet using SQL-like syntax. To limit the data given by Google Sheets, you may also utilise offset, where, and limit clauses.

Advertisements 
google.charts.load('current');
google.charts.setOnLoadCallback(init);

function init() {
  var url = 'https://docs.google.com/spreadsheets/d/1YpiTo7Fc3QvBdbuReCIcwtg7lnmZupQAH57phrDLotI/edit#gid=0';
  var query = new google.visualization.Query(url);
  query.setQuery('select A, B');
  query.send(processSheetsData);
}

Step 4: Prepare the Data for D3.js

Once the spreadsheet data is available, transform the response into an Array of Objects that d3.js can read. Because Google Sheets sends numerical data as a String, we can convert it to an Integer using parseInt or the Unary (+) operator.

function processSheetsData(response) {
  var array = [];
  var data = response.getDataTable();
  var columns = data.getNumberOfColumns();
  var rows = data.getNumberOfRows();
  for (var r = 0; r < rows; r++) {
    var row = [];
    for (var c = 0; c < columns; c++) {
      row.push(data.getFormattedValue(r, c));
    }
    array.push({
      name: row[0],
      value: +row[1],
    });
  }
  renderData(array);
}

Step 5: Render the D3.js chart

The data from Google Sheets is then used to generate a Bar Chart in D3.js. You may learn how to construct bar charts in D3.js by following this tutorial on @ObservableHQ. The chart is in SVG format.

function renderData(data) {
  const margin = { top: 30, right: 0, bottom: 30, left: 50 };
  const color = 'steelblue';
  const height = 400;
  const width = 600;
  const yAxis = (g) =>
    g
      .attr('transform', `translate(${margin.left},0)`)
      .call(d3.axisLeft(y).ticks(null, data.format))
      .call((g) => g.select('.domain').remove())
      .call((g) =>
        g
          .append('text')
          .attr('x', -margin.left)
          .attr('y', 10)
          .attr('fill', 'currentColor')
          .attr('text-anchor', 'start')
          .text(data.y)
      );

  const xAxis = (g) =>
    g.attr('transform', `translate(0,${height - margin.bottom})`).call(
      d3
        .axisBottom(x)
        .tickFormat((i) => data[i].name)
        .tickSizeOuter(0)
    );
  const y = d3
    .scaleLinear()
    .domain([0, d3.max(data, (d) => d.value)])
    .nice()
    .range([height - margin.bottom, margin.top]);

  const x = d3
    .scaleBand()
    .domain(d3.range(data.length))
    .range([margin.left, width - margin.right])
    .padding(0.1);

  const svg = d3.select('svg').attr('width', width).attr('height', height).attr('fill', color);

  svg
    .selectAll('rect')
    .data(data)
    .enter()
    .append('rect')
    .attr('x', (d, i) => x(i))
    .attr('y', (d) => y(d.value))
    .attr('height', (d) => y(0) - y(d.value))
    .attr('width', x.bandwidth());

  svg.append('g').call(xAxis);

  svg.append('g').call(yAxis);
}

Also See: How to Use Hyperlinks in Google Sheets Easily

Advertisements

Leave a Comment