Retieving Data from Google Spreadsheets

172 views
Skip to first unread message

redsta...@googlemail.com

unread,
Feb 8, 2016, 7:13:07 AM2/8/16
to Google Visualization API
Hi

I am new to google charts and am trying a simple visualisation of some data I have on a google spreadsheet:


I have pieced together this code but it doesn't seem to work. I am sure there is just a simple error here. Can anyone help. Thanks.

<!DOCTYPE html>
<head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      // Load the Visualization API and the piechart package.
      google.load('visualization', '1.0', {'packages':['corechart']});
      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawSheetName);

 function drawSheetName() {
  var opts = {sendMethod: 'auto'};
  var query = new google.visualization.Query(https://spreadsheets.google.com/tq?1dyKk4gfExyR-jDQyESyPb1iUnKmZm2EwJKEHG0AEBSU);
  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:'Fruits!'};
  var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
  chart.draw(data, options);
}
 
    </script>
  </head>
  <body>
    <!--Div that will hold the pie chart-->
    <div id="chart_div"></div>
  </body>
</html>

Sergey Grabkovsky

unread,
Feb 8, 2016, 9:51:39 AM2/8/16
to Google Visualization API
Hi Redstar,

There are three primary issues with your code:
  1. You must quote the URL. In JavaScript, URLs are strings. They are not special. They must be quoted.
  2. You're not passing the query options to the query (but it doesn't really matter because you're using the defaults anyway).
  3. You're not using the correct Sheets URL. You can find instructions for querying a Google Sheet here.
Here is a jsfiddle where all the errors are fixed: https://jsfiddle.net/cjs8npq9/
The fiddle is also using the new loader (which is recommended).

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/371663ca-0eae-497b-8e58-cbe6c606d360%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--

unnamed.gif

•

Sergey Grabkovsky

•

Software Engineer

•

Google, Inc

•

gra...@google.com


Red Star

unread,
Feb 8, 2016, 11:14:24 AM2/8/16
to google-visua...@googlegroups.com
Hi Sergey

Thanks for that. I have got it working - double quotes were the trick!

I now need to start using the query options but at least I have something working!

Regards

Jon

To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/CAEwwup5kmpeg4tFdrpzCA2TnEKjSM8yrn_5kppA7kM2g%2Bfz5iQ%40mail.gmail.com.

Red Star

unread,
Feb 8, 2016, 11:59:27 AM2/8/16
to google-visua...@googlegroups.com
Hi

I have now added a few different charts based on the same data and am now trying to add a Category Filter.

It is not showing at all?

Any help appreciated.

Jon

<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">

google.charts.load('current', {
  'packages': ['corechart','controls']
});
google.charts.setOnLoadCallback(drawSheetName);
function drawSheetName() {
  var opts = {
    sendMethod: 'auto'
  };
  var query = new google.visualization.Query("https://docs.google.com/spreadsheets/d/1dyKk4gfExyR-jDQyESyPb1iUnKmZm2EwJKEHG0AEBSU/edit");
  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: 'Fruits!'
  };
 
  var dashboard = new google.visualization.Dashboard(
            document.getElementById('dashboard_div'));
  var control = new google.visualization.ControlWrapper({
          'controlType': 'CategoryFilter',
          'containerId': 'filter_div' ,
           options: {
            filterColumnIndex: 0,
            ui: { }
        }
        });
  var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
  chart.draw(data, options);
  var chart = new google.visualization.PieChart(document.getElementById('chart_div3'));
  chart.draw(data, {colors: ['#e0440e', '#e6693e', '#ec8f6e', '#f3b49f', '#f6c7b6'],is3D: true});
  var chart = new google.visualization.BarChart(document.getElementById('chart_div2'));
  chart.draw(data, options);
 
    dashboard.bind([control], [chart]);
    dashboard.draw(data);

}
    </script>
  </head>
  <body>
    <div id "dashboard>
    <div id="filter_div" style="height: 200px; width: 400px"></div>
    <div id="chart_div" style="height: 180px; width: 400px"></div>
    <div id="chart_div3" style="height: 180px; width: 400px"></div>
    <div id="chart_div2" style="height: 180px; width: 400px"></div>
    </div>
  </body>
</html>

Sergey Grabkovsky

unread,
Feb 8, 2016, 12:32:14 PM2/8/16
to google-visua...@googlegroups.com
Hi Jon,

If you're using charts within a Dashboard, you need to use ChartWrappers instead of actual charts. You may find the documentation for Dashboard here.

If you have another issue, please create a jsfiddle with your dashboard to help us help you better.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/CAHdOhpbc_vjVCKUYYBBS4%2Bqbr-Rt_qWEwdE8UYFaocD_KTATPw%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages