Feed a Google Chart from a Google Sheet
104 views
Skip to first unread message
samuel.ma...@bbva.com
Sep 4, 2015, 12:48:17 PM9/4/15
to Google Visualization API
I have the following Dashboard in Google Chart (Code and Image Below)
My question is: How do I get the Data from a Google Sheets instead of manually introducing the Data? I would like to have the data in a google sheets and link this code to the Sheet so its get the data from the sheet instead of having to input the data manually.
Thank you very much for your help
<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 controls package.
google.load('visualization', '1.0', {'packages':['controls']});
// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawDashboard);
// Callback that creates and populates a data table,
// instantiates a dashboard, a range slider and a pie chart,
// passes in the data and draws it.
function drawDashboard() {
// Create our data table.
var data = google.visualization.arrayToDataTable([
['Name', 'Donuts eaten'],
['Michael' , 5],
['Elisa', 7],
['Margareth', 8]
]);
// Create a dashboard.
var dashboard = new google.visualization.Dashboard(
document.getElementById('dashboard_div'));
// Create a range slider, passing some options
var donutRangeSlider = new google.visualization.ControlWrapper({
'controlType': 'NumberRangeFilter',
'containerId': 'filter_div',
'options': {
'filterColumnLabel': 'Donuts eaten'
}
});
// Create a pie chart, passing some options
var pieChart = new google.visualization.ChartWrapper({
'chartType': 'PieChart',
'containerId': 'chart_div',
'options': {
'width': 300,
'height': 300,
'pieSliceText': 'value',
'legend': 'right'
}
});
// Establish dependencies, declaring that 'filter' drives 'pieChart',
// so that the pie chart will only display entries that are let through
// given the chosen slider range.
dashboard.bind(donutRangeSlider, pieChart);
// Draw the dashboard.
dashboard.draw(data);
}
</script>
</head>
<body>
<!--Div that will hold the dashboard-->
<div id="dashboard_div">
<!--Divs that will hold each control and chart-->
<div id="filter_div"></div>
<div id="chart_div"></div>
</div>
</body>
</html>
Daniel LaLiberte
Sep 4, 2015, 1:14:56 PM9/4/15
to Google Visualization API
Hi Samuel,
Did you find this documentation?: https://developers.google.com/chart/interactive/docs/spreadsheets
--
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 http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/501aa720-9734-4a2e-a9f7-c29d7a7e0754%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
daniel.laliberte@GMail.com 9 Juniper Ridge Road, Acton MA
samuel.ma...@bbva.com
Sep 4, 2015, 1:45:03 PM9/4/15
to Google Visualization API
Hi Daniel,
Yes I did. And I tried different things but I'm not able to make it work.
I have for instance this other Chart (Code Below) that is fed from a Google Sheet but I'm not able to combine the Interactive Chart (with sliders or selectors) and to feed it from a Google Sheet. I tried so many different things but I cannot figure out what I'm doing wrong.
I would highly appreciate if someone could help me out to find out how to feed the original Chart from a google sheet.
<html> <head> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> google.load('visualization', '1'); google.setOnLoadCallback(drawVisualization);
function drawVisualization() { var wrapper = new google.visualization.ChartWrapper({ chartType: 'PieChart', dataSourceUrl: 'https://docs.google.com/spreadsheets/d/1c34jIRfIeuh-rzr8VS7rPw168nJkflvWNpLR2de3KZw/edit#gid=0', query: 'SELECT A,C WHERE C > 1 ORDER BY C', options: {'title': 'States'}, containerId: 'vis_div' }); wrapper.draw()
// No query callback handler needed! } </script> </head> <body style="font-family: Arial;border: 0 none;"> <div id="vis_div" style="width: 600px; height: 400px;"></div> </body></html>To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/501aa720-9734-4a2e-a9f7-c29d7a7e0754%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Daniel LaLiberte
Sep 4, 2015, 2:02:33 PM9/4/15
to Google Visualization API
While the ChartWrapper provides a convenient way to access data from spreadsheets, it only works for individual charts, not Dashboards. To provide the data to a dashboard, you'll have to fetch the data another way, such as by using google.visualization.Query, as described on the documentation page.
function drawChart() {
var query = new google.visualization.Query(URL);
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
var data = response.getDataTable();
var chart = new google.visualization.ColumnChart(document.getElementById('columnchart'));
chart.draw(data, null);
}Replace the chart construction and drawing with your Dashboard construction and drawing.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 http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/501aa720-9734-4a2e-a9f7-c29d7a7e0754%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/38b57c3f-ee46-48ec-a441-a88140e4ff92%40googlegroups.com.
samuel.ma...@bbva.com
Sep 4, 2015, 2:37:29 PM9/4/15
to Google Visualization API
Daniel,
I went and tried to replicate the example in the link you provided me and I cannot replicated.
Here is the code that I came up with, but it is not working. What is wrong with it?
Is there any debugger for this so I would see where my code is not working?
thank you very much again
<html> <head> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> google.load("visualization", "1", {packages:["corechart"]}); google.setOnLoadCallback(drawChart);
function drawChart() { var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1c34jIRfIeuh-rzr8VS7rPw168nJkflvWNpLR2de3KZw'); query.send(handleQueryResponse); }
function handleQueryResponse(response) { var data = response.getDataTable(); var chart = new google.visualization.ColumnChart(document.getElementById([1,4])); chart.draw(data, null); }
function drawSheetName() { var queryString = encodeURIComponent('SELECT A, B, C, D');
var query = new google.visualization.Query( 'https://docs.google.com/spreadsheets/d/1c34jIRfIeuh-rzr8VS7rPw168nJkflvWNpLR2de3KZw' + 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.ColumnChart(document.getElementById('chart_div')); chart.draw(data, { height: 400 }); }
</script> </head> <body> <div id="piechart" style="width: 900px; height: 500px;"></div> </body></html>To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/501aa720-9734-4a2e-a9f7-c29d7a7e0754%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
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-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/38b57c3f-ee46-48ec-a441-a88140e4ff92%40googlegroups.com.
Daniel LaLiberte
Sep 4, 2015, 2:56:06 PM9/4/15
to Google Visualization API
Starting from your code, I put together this: http://jsfiddle.net/dlaliberte/ytrs7pvp/
- You forgot to change the call of drawChart to your drawSheetName.
- You need to add a magic incantation to the URL before your query string.
- I don't have access to your data (but requested it), so this still fails for me.
You should learn to use the JavaScript debugger in your browser.
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 http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/501aa720-9734-4a2e-a9f7-c29d7a7e0754%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/38b57c3f-ee46-48ec-a441-a88140e4ff92%40googlegroups.com.
--daniel.l...@GMail.com 9 Juniper Ridge Road, Acton MA
--
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 http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/123f64c1-08b8-40b9-8d2b-e399b90c6fba%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages