Highcharts with Public Google Spreadsheet does not work

Question

I'm currently trying to use data from a google sheet to create a highchart. However, even after making the sheet public and publishing it to the web, it still does not generate the data. I'm not sure what I'm doing wrong here and any help would be appreciate it.

The code snippet is here:

Highcharts.chart('container', {

chart: {
    polar: true,
    type: 'line'
},

title: {
    text: 'Objectives',
},

pane: {
    size: '80%'
},

xAxis: {
    categories: ['Financial Cost', 'Health Damages', 'Water Usage'],
    tickmarkPlacement: 'on',
    lineWidth: 0
},

yAxis: {
    gridLineInterpolation: 'polygon',
    lineWidth: 0,
    min: 0
},

tooltip: {
    shared: true,
    pointFormat: '<span style="color:{series.color}">{series.name}: <b>${point.y:,.0f}</b><br/>'
},

legend: {
    align: 'right',
    verticalAlign: 'top',
    y: 70,
    layout: 'vertical'
},

series: [{
    name: 'Design 2',
    data: {googleSpreadsheetKey: 
          '1QwJuLpRWeXTTZ3k0zaUeCncHY1sRu6FxBnmlgsPw9pE',
           },
    pointPlacement: 'on'
}]

});

The google sheets link is: https://docs.google.com/spreadsheets/d/1QwJuLpRWeXTTZ3k0zaUeCncHY1sRu6FxBnmlgsPw9pE/edit?usp=sharing

Answer 1

You placed the data property in the wrong place. It should be a property of the options object (highest level):

Highcharts.chart('container', {

  data: {
    googleSpreadsheetKey: '1QwJuLpRWeXTTZ3k0zaUeCncHY1sRu6FxBnmlgsPw9pE',
    parsed: function(options) {
      console.log(options);
    }
  },

Live demo: http://jsfiddle.net/kkulig/Lc02r8sx/

As you can see in the above example the data is fetched successfully (console.log in parsed function).

Now you need to prepare it - string values are fetched from the sheet and Highcharts accepts only numbers as y values.

API reference: https://api.highcharts.com/highcharts/data.parsed

Answer 2

This code gets data as an array/object from the sheet. But it needs jQuery in you client.

Notice that it is wrapped in a promise. This means that the chart won't render until the data is returned. Here is a jsbin based on data from this sheet

<script src="https://code.jquery.com/jquery-3.1.0.js"></script>

var firstSheet = function(){
  var spreadsheetID = "1qZTQonzIrfVQ2rSPtschek7EvAbxwDgOCz7sf7wbkjU";
  var url = "https://spreadsheets.google.com/feeds/list/" + spreadsheetID +"/1/public/values?alt=json";
  return new Promise((resolve,reject)=>{
    $.getJSON(url, (data)=>{
        let result = data.feed.entry
        resolve(result)
   });
  })
}

firstSheet().then(function(data){
  let series = [];
  let newData = [];

  data.forEach(item => {

    let amount = Number(item.gsx$amount.$t); 
    //gsx$amount = sheet column header name - The api call converts column header names to lower case and strips spaces 
    newData.push(amount)
  })
  series.push({name: 'somedata', data: newData })
  renderChart(series)
})

const renderChart = (data)=>{

Highcharts.chart('container', {

    title: {
        text: 'Solar Employment Growth by Sector, 2010-2016'
    },

    subtitle: {
        text: 'Source: thesolarfoundation.com'
    },

    yAxis: {
        title: {
            text: 'Number of Employees'
        }
    },
    legend: {
        layout: 'vertical',
        align: 'right',
        verticalAlign: 'middle'
    },
    series: data
});
}