Converting Google Visualization Query result into JSON

Question

I am a beginner trying to read a Google Sheet in a Javascript app using: https://docs.google.com/spreadsheets/d/mySheetID/gviz/tq?tq=Select%20*%20where%20A%20=%20%22Nan%22&tqx=out:JSON

I can access that row in my sheet and save it as JSON giving me a file with the content headed "/O_o/ google.visualization.Query.setResponse..." This is the file I cannot further process in javascript.

I see in: converting Google Visualization Query result into javascript array that the solution appears to be: "If you add a header named X-DataSource-Auth in your request, the Visualization API will respond in JSON format". After a day of googling I am quite unable to find where I am supposed to put such a header and what its syntax should be.

But then I'm 82 years old and this stuff gets more difficult with each passing year... Can someone point me in the right direction?

WhiteHat WhiteHat · Accepted Answer · 2020-10-16T17:38:02

once you receive the response from the query,
you can convert the response to a google data table.

var dataTable = response.getDataTable();

and the google data table has a method to convert the data table to JSON.

var jsonData = dataTable.toJSON();

this will return a JSON string.

if you then would like to work with the JSON using JavaScript,
you can parse the string...

jsonData = JSON.parse(jsonData);

the JSON will be an object with two properties, cols and rows.
you can see an example of the JSON result here...

see this fiddle for a working example using the following code...

https://jsfiddle.net/WhiteHat/5mu9wnbz/1/

google.charts.load('current', {
  packages: ['corechart']
}).then(function () {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1M3wQgKg3JBF6_hzv1xWONP7HWVYoOvJ1jPbB27IUg94/gviz/tq?gid=0&headers=1');
  query.send(function (response) {
    if (response.isError()) {
      console.log('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
      return;
    };

    var dataTable = response.getDataTable();
    var jsonData = dataTable.toJSON();

    jsonData = JSON.parse(jsonData);
    document.getElementById('cols').innerHTML = jsonData.cols.length;
    document.getElementById('rows').innerHTML = jsonData.rows.length;
    console.log(jsonData);
  });
});

Converting Google Visualization Query result into JSON

1 Answers