Fill a google chart with data from a google spreadsheet

google-apps-script, javascript

Solution

there is a tutorial with an example here:

https://developers.google.com/chart/interactive/docs/spreadsheets

Basically, you code will look like: (I'm not sure your URL is correct. Also, set the end of the url to use the right range of data):

    google.load("visualization", "1", {packages:["corechart"]});

function initialize() {
        var opts = {sendMethod: 'auto'};
        // Replace the data source URL on next line with your data source URL.
        var query = new google.visualization.Query('http://spreadsheets.google.com/tq?key=0AnD0SFr9ooPgdG83Wm&transpose=0&headers=1&merge=COLS&range=A1%3AA5%2CB1%3AC5&gid=0&pub=1', opts);         
        -
        // Optional request to return only column C and the sum of column B, grouped by C members.
        //query.setQuery('select C, sum(B) group by C');

        // Send the query with a callback function.
        query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

    var data = response.getDataTable();

  var options = {
    title: 'Company Performance'
  };

  var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
  chart.draw(data, options);
}
      
      google.setOnLoadCallback(initialize);

Problem

This seems to be something very simple but I have searched and not found a solution. I am trying to create a chart on my site based on the data in a google spreadsheet. I have been able to get it working using hard coded values but not sure how to fill from a spreadsheet. My current script looks like this: ``` google.load("visualization", "1", {packages:["corechart"]}); google.setOnLoadCallback(drawChart); function drawChart() { var data = google.visualization.arrayToDataTable([ ['Year', 'Sales', 'Expenses'], ['2004', 1000, 400], ['2005', 1170, 460], ['2006', 660, 1120], ['2007', 1030, 540] ]); var options = { title: 'Company Performance' }; var chart = new google.visualization.LineChart(document.getElementById('chart_div')); chart.draw(data, options); } ``` My data on my spread sheet is simlilar to what is hard coded but I want to give it the spreadsheet url and have it fill it from that (url like this: https://docs.google.com/spreadsheet/ccc?key=0AnD0SFr9ooPgdG83Wm)

Original source