Google Charts with Spreadsheets

Question

How can I use the data in google spreadsheets to make a google chart? I tried the examples linked below but it didn't work for me. I would like to put the charts into a website by putting the code in the html. I want to use the Pie Charts mostly, but want to be able to use the other chart options such as the Bar Charts and Donut Charts. All the information I found did not show how to ingest data from google spreadsheets or fusion table.

Creating a Chart from a Seperate Spreadsheet

Querying a Fusion Table

Code I tried using for inserting the chart in the website:

<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <title>Excel Pull Test</title>
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript">
    function drawSheetName() {
    var queryString = encodeURIComponent('SELECT A, H, O, Q, R, U LIMIT 5 OFFSET 8');

    var query = new google.visualization.Query(
    'https://docs.google.com/spreadsheets/d/1_2QRYLFsUqr6tAri8HWx9QArWclkbzmgfjLYO0Bnosg/edit?usp=sharing' + queryString);
      query.send(handleSampleDataQueryResponse);
    }

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

    var chart = new     google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
  </script>
</head>

<body>
  <div id="chart_div"></div>
</body>
</html>

I haven't received any errors. When I load the page with this code in my broswer, it's completely blank. Some thought on the issue is it may not be extracting the code from the google spreadsheet correctly. I have the spreadsheet permissions set to "Anyone with the link" can view. The code I used is set to display the information as a table, not a pie chart or anything else. I didn't fully understand the query string either. The range I was trying to select is from A1:B7 and row 1 is the header row. I didn't change it from the original script, just added the html around it from one of the examples.

include detailed info about whats not working. make sure you configure permissions correctly. — Zig Mandel, Jul 24 '15 at 13:07
Adding to what Zig said, Are you getting any sort of errors? If yes, please include that information to give us a better idea of what might not be working. — pointNclick, Jul 24 '15 at 16:52
I'm not receiving any errors, nothing displays on the page. I'm assuming it's not extracting the data from the google spreadsheet correctly. I have the spreadsheet permissions set to "Anyone with the link" can view. — nats_ohchewy, Jul 24 '15 at 17:55

score 0 · Answer 1 · answered Jul 27 '15 at 16:32

You are receiving an empty response because your data variable is undefined in the function handleSampleDataQueryResponse(response). Try doing something like:

var data = response.getDataTable();

as shown here before the chart.draw. This should populate your chart with the required data.

nats_ohchewy · Answer 2 · 2015-07-29T15:31:25.860

The problems were the Google Visualization API wasn't being loaded, a callback to run once loaded wasn't set, and the data variable wasn't identified.

<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <title>Excel Pull Test</title>
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript">

  google.load('visualization', '1.0', {'packages':['corechart']});

  google.setOnLoadCallback(drawSheetName);

   function drawSheetName() {
      var queryString = encodeURIComponent('SELECT A:B7');

      var query = new google.visualization.Query(
      'https://docs.google.com/spreadsheets/d/1_2QRYLFsUqr6tAri8HWx9QArWclkbzmgfjLYO0Bnosg/edit#gid=0&headers=1&tq=' + queryString);
      query.send(handleSampleDataQueryResponse);
    }

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

      var data = response.getDataTable();
      var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
  chart.draw(data, { height: 400 });
    }
  </script>
</head>

<body>
  <div id="chart_div"></div>
</body>
</html>

Google Charts with Spreadsheets

2 Answers2