Google Chart - Using Spreadhseet Source does not resolve

Question

Using the tutorial from google on using a spreadsheet as data for a chart I have created the following. Tutorial

<head>
  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
  <script type="text/javascript">
    google.charts.load('current', {packages: ['corechart']});
    google.charts.setOnLoadCallback(drawChart);


    function drawSheetName() {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/15l3ZK45hv2ByOfkUiAKoKp-Z9a1u1-Q_rsLS7SqC51E/editgid=0&headers=1');
  query.send(handleQueryResponse);
}

function handleQueryResponse(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>
<!-- Identify where the chart should be drawn. -->

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

However no chart resolves, I have made the link public. If I share the link it is https://docs.google.com/spreadsheets/d/15l3ZK45hv2ByOfkUiAKoKp-Z9a1u1-Q_rsLS7SqC51E/edit?usp=sharing

Following the docs and not sure where my error is.

This SO answer more about drive-sdk creates the key from a script from a new file, I don't really understand it and nuclear how you can obtain the correct URL for existing spreadsheets.

Would be great if there could be a consolidated answer obtaining correct sheets URL's.

Where is this html supposed to be used? Do you want to display the chart as a [spreadsheet dialog](https://developers.google.com/apps-script/guides/dialogs), or do you want to serve it separately as [a web app](https://developers.google.com/apps-script/guides/web)? — Adelin, Feb 25 '16 at 11:26
As a web app as the spreadsheet dialog only supports limited number of charts. I just created a basic.html file and spun up a python simple server — sayth, Feb 25 '16 at 11:39

Adelin · Accepted Answer · 2016-02-25T12:20:51.207

2

Is this what you are trying to achieve?

If yes, you can check how it works here.

EDIT

Since I notice in the comments you want to query the spreadsheet and not use the entire spreadsheet, this is the logic to query with.

First, these are the basic components of the url to query:

var BASE_URL = "https://docs.google.com/a/google.com/spreadsheets/d/";
var SS_KEY = "stringSS_Key";
var BASE_QUERY = "/gviz/tq?tq=";
var partialUrl = BASE_URL + SS_KEY + BASE_QUERY;

Notice the BASE_QUERY variable

The first bit of the url is in the partialUrl variable. Then, you need to use the Query Language that google provides. An example would be:

var queryToRun = 'select dept, sum(salary) group by dept';

You then need to call encodeURIComponent() on it, and add it to the partial URL. The final URL then becomes

var finalUrl = partialUrl + encodeURIComponent(queryToRun);

Only then you can run the google.visualiation.query as I suspect you intend.

var query = new google.visualization.Query(finalUrl);

edited Feb 25 '16 at 12:20

answered Feb 25 '16 at 11:51

Adelin

7,809
5
37
65

Hi and thanks, why does yours work when mine copied straight from tutorial doesn't? I assumed incorrectly that the link was wrong since it seems a common issue and expected tutorial code to work – sayth Feb 25 '16 at 11:56
1

Yes, to begin with, the link was wrong. Secondly, you had 2 closing curly brackets under `chart.draw(data, { height: 400 }); }`. And lastly you called `google.charts.setOnLoadCallback(drawChart);` but `drawChart` was undefined. What I did there was use `drawSheetName` instead. I hope this answers your questions. – Adelin Feb 25 '16 at 12:00
How do i get the link correct in future, i cooed from the browser and added the gid and headers as the tutorial. Is there a way you use to check validity? – sayth Feb 25 '16 at 12:09

Google Chart - Using Spreadhseet Source does not resolve

1 Answers1