Referencing Google Sheets data in D3 charts (JSON API)

[David L]

Hi - My ultimate goal is creating a dashboard of D3 charts using data from Google Sheets. I've got as far as pulling data  from Sheets using JSONP https://blogs.it.ox.ac.uk/acit-rs-team/2014/10/10/how-to-create-a-bubble-chart-from-a-google-spreadsheet-using-d3-js/.

What I want to do now is experiment with various different charts, but I don't know how to say take one of the fantastic examples out there (Mike Bostock's Bar Chart) and reference my Sheets data instead of the tsv.

Grateful for any assistance.

My current code: 

<script>

/**

 * Maps the spreadsheet cells into a dataframe, consisting of an array of rows (i.e. a 2d array)

 * In many cases we have empty rows or incomplete rows, so you can skip those by including 

 * the realrowlength parameter - it will skip any rows that don't have this length.

 * Alternatively, you can just choose to skip the first 'n' rows by setting the skip parameter.

 */

function mapEntries(json, realrowlength, skip){

  if (!skip) skip = 0;

  var dataframe = new Array();

  

  var row = new Array();

  for (var i=0; i < json.feed.entry.length; i++) {

    var entry = json.feed.entry[i];

    if (entry.gs$cell.col == '1') {

      if (row != null) {

        if ((!realrowlength || row.length === realrowlength) && (skip  === 0)){

           dataframe.push(row);

        } else {

           if (skip > 0) skip--;

        }

      }

      var row = new Array();

    }

    row.push(entry.content.$t);

  } 

  dataframe.push(row);

  return dataframe;

}

//

// Show a nice bubble chart!

//

function drawBubbleChart(root){

    

    //

    // How big the chart is 

    //

    var diameter = 960;

    

    //

    // Pick some colours for the categories (groups)

    //

    var color = d3.scale.category10();

    

    //

    // Create a bubble layout based on the tree of objects. 

    // This adds properties x,y,r to each of our leaf objects

    // indicating where to draw them (x,y), and how big to draw them (r).

    // This is worked out using the "value" property of each leaf.

    //

    var bubble = d3.layout.pack().sort(null).size([960,960]).padding(1.5);

    //

    // Make a SVG graphic

    //

    var svg = d3.select("body")

                .append("svg")

                .attr("width",960)

                .attr("height", 960)

                .attr("class","bubble");

    

    //

    // For each leaf, create a new "node" and place it in the correct

    // location using the transform attribute.

    //

    var node = svg.selectAll(".node")

                  .data(bubble.nodes(root)

                  .filter(function(d){ return !d.children;}))

                  .enter()

                  .append("g")

                  .attr("class","node")

                  .attr("transform", function(d) { return "translate(" + d.x + "," + d.y + ")"; });

   //

   // For each node, make a circle of the correct radius (r) and

   // colour it in according to the group it belongs to

   //

   node.append("circle")

       .attr("r", function(d) { return d.r; })

       .style("fill", function(d) { return color(d.group)  });

   //

   // For each node, add a label to the middle of the circle

   //

   node.append("text")

       .attr("dy", ".3em")

       .style("text-anchor", "middle")

       .text(function(d) { 

              return d.name;

        });

}

//

// Render the data

//

function render(data){

  //

  // Convert the JSON into a nice array

  //

  var dataframe = mapEntries(data,null,2);

  

  //

  // Build a "tree" from each of the data rows 

  //

  // Each leaf in the tree has a name, value and group

  //

  var root =  {};

    root.name = "Interactions";

    root.children = new Array();

    for (i=0;i<dataframe.length;i++){

      var item = {};

      item.name = dataframe[i][0];

      item.value = Number(dataframe[i][1]);

      item.group = dataframe[i][2];

      root.children.push(item);

    }

    

  //

  // Draw a bubble chart of the tree

  //

  drawBubbleChart(root);

}

</script>

[Curran]

Hello,

You may want to take a look at Tabletop.js.

Searching Blockbuilder.org for Tabletop gives some nice results that show how to pull data from Google Sheets into D3 visualizations:

• enjalot - mapping police violence: data
• tomshanley - Force directed graph using tabletop.js and shortestpath
  
• andreaangeli - d3js barchart and tabletop
  

All the best,

Curran

Datavis.tech

[David L]

Hi - Thanks for that. I will have a look at Tabletop and see if I can work out how to match the data with the visualisations.

[Alec Barrett]

You might also consider building the viz in Google Apps Script.  You have native connection to Google Spreadsheets (using the SpreadsheetApp Class) which will automatically convert data to JSON.  Here's the basic file structure I'd use:

code.gs

function getData(){
 var sheet = SpreadsheetApp.openById("asdfghjkl1234567890");
 
 //get desired range
 
 return data;
}

function doGet(){
 return HtmlService.createTemplateFromFile('index')
   .evaluate()
   .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

index.html

<script>

google.script.run.withSuccessHandler(makeViz).getData();

function makeViz(data){

//put your d3 here

}

</script>