Linking a chart on my website to Google Sheets.

[ninja8370]

I currently have the following Javascript code.

<script>

    google.setOnLoadCallback(drawSeriesChart);

    function drawSeriesChart() {

      

      var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1Q_f86rmnDJ3Ei-yNSLmIDeNwgTJXRHMceNlZQGn2TU0/edit#gid=890579914', SELECT A:E)

      /*query.setQuery('Name, X Coordinate, Y Coordinate, Alliance Name, Importance');*/

      query.send(handleQueryResponse);

      

      var data = response.getDataTable();

      

      /*var data = google.visualization.arrayToDataTable([

        ['name', 'X', 'Y', 'Alliance', 'Importance'],

        ['ninja', 100, 100, 'FreeK', 10]

      ]); */

      var options = {

        title: 'GGE test Map',

        hAxis: {title: 'X Axis', minValue: 0, maxValue: 1000},

        vAxis: {title: 'Y Axis', direction: -1, minValue: 0, maxValue: 1000},

        bubble: {textStyle: {fontSize: 11}}

      };

      var chart = new google.visualization.BubbleChart(document.getElementById('series_chart_div'));

      chart.draw(data, options);

    }

</script>

This is not including the HTML, which is simply a div labled 'series_chart_div'

When I was using the arrayToDataTable method, to test and make sure the chart worked, it all worked fine. Now I want to link the chart to a table so it can receive it's data from there.
I referenced https://developers.google.com/chart/interactive/docs/spreadsheets?hl=en#sheet-name to link the chart to the table, but now I can not figure out what is wrong. When the page is loaded, it displays a blank page.

I most likely have a stupid mistake in my code, could anyone take a look at it and let me know?

Thankyou!

[Daniel LaLiberte]

What you are missing is your handleQueryResponse function, which should be defined to wrap around all the rest of the code following your query.send() call.  You are probably getting a JavaScript error about this that you can see in your console.

--
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/d66a9833-34d1-42cd-a8b2-63b7c3a0f141%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Daniel LaLiberte

dlaliberte@Google.com   5CC, Cambridge MA

[ninja8370]

Thankyou for your fast reply!

That was indeed the problem at that point! I fixed that and no longer have that error. Now I'm doing bug fixing, (mostly stupid mistakes) and still trying to get it to work, but that was definitely a step! If I need more help, I'll post back here.

  Thankyou!

[ninja8370]

I now have

<script type="text/javascript">    

    google.setOnLoadCallback(drawSeriesChart);

    function drawSeriesChart() {

      

      var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1Q_f86rmnDJ3Ei-yNSLmIDeNwgTJXRHMceNlZQGn2TU0/edit#gid=890579914', 'SELECT A:E');

      /*query.setQuery('Name, X Coordinate, Y Coordinate, Alliance Name, Importance');*/

      query.send(handleSampleDataQueryResponse);

    };

      

        function handleSampleDataQueryResponse(response) {

      if (response.isError()) {

        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

        return;

      }

    

    function handleQueryResponse() {  

      var data = response.getDataTable();

      

      /*var data = google.visualization.arrayToDataTable([

        ['name', 'X', 'Y', 'Alliance', 'Importance'],

        ['ninja', 100, 100, 'FreeK', 10]

      ]); */

      var options = {

        title: 'GGE test Map',

        hAxis: {title: 'X Axis', minValue: 0, maxValue: 1000},

        vAxis: {title: 'Y Axis', direction: -1, minValue: 0, maxValue: 1000},

        bubble: {textStyle: {fontSize: 11}}

      };

      var chart = new google.visualization.BubbleChart(document.getElementById('series_chart_div'));

      chart.draw(data, options);

    };

    };

  </script>

There are no errors showing in the console, yet it does not display the chart. Is there something else I'm missing?

[Daniel LaLiberte]

Now you have defined handleQueryResponse inside of your handleSampleDataQueryResponse function.  Functions won't get called unless someone calls them.  You probably don't need the inner function.

--

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/9bb97a70-39a8-4f6f-acbc-53e153b3449d%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[ninja8370]

woops xD that was dumb

Thanks!

It's connecting to my Database now, but it says that 'Column 0 must be of type String'

Do I need to have quotation marks around the strings in the database? 

[Daniel LaLiberte]

You don't want quotation marks in your spreadsheet data.  I can't tell why that error is occurring.  Looking at the spreadsheet you referenced, the first column seems fine.  It might be getting confused by the extra double quotes, both in your data and your first row, but if so, that is a bug.

I recommend you replace your BubbleChart with a Table chart just to see what data is being fetched by your Query.  I'm not sure what your SELECT A:E is going to do.

--

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/b716175c-4639-4a6f-ac9a-a542304cb711%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[ninja8370]

Okay, I tried google.visualization.Table and google.visualization.TableChart and neither worked, I googled and couldn't find an answer.. How am I supposed to put that data into a table?

Also, I originally didn't have those quotation marks in, I just put them in to see if they'd fix the string problem. (They didn't, and I removed them again now)

Thanks!

[Daniel LaLiberte]

Table charts are documented here:  https://developers.google.com/chart/interactive/docs/gallery/table

Note that you should add package 'table'.  Calling google.visualization.Table should have worked, if you used it correctly.

--

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/06a68da8-fa1f-4288-8e10-4f86e12da67f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[ninja8370]

Ah I  got it, thankyou! With the table, it is showing me a timestamp- I think this means that it is receiving the Form data and not the reformatted data. (On that sheet, there's two tabs) In the charts documentation, I believe I saw a sheet selector option, so I'll use that. :)

[ninja8370]

Thankyou so much for you help!

After tweaking the URL a bit to make sure it selected the correct sheet, I was able to make the table display the correct data! I should be able to display it in a bubble graph now! Thankyou!

-Ninja

[ninja8370]

I am putting it to use now and it is working great!

There is one slight annoyance- The scroll/zoom is backwards. (When you scroll it in a direction that is usually in, it scrolls out, and vice versa.) I didn't see any options that could be used to fix this.. do I just have to live with that?

[Daniel LaLiberte]

You might try using a negative zoomDelta option.  Not sure that works, but it would make sense if it does.

On Fri, Dec 18, 2015 at 10:22 PM, ninja8370 <ninj...@gmail.com> wrote:

I am putting it to use now and it is working great!

There is one slight annoyance- The scroll/zoom is backwards. (When you scroll it in a direction that is usually in, it scrolls out, and vice versa.) I didn't see any options that could be used to fix this.. do I just have to live with that?

--

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/b95e6755-2445-4ddd-b431-aadd9e474b9d%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[ninja8370]

Ah that is probably how. I will try that right now. Thankyou!

[ninja8370]

Yes, it works perfectly now, thankyou for your help!