google-visualization - 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?

标签： google-visualizationgoogle-query-language

Answer 1

收到查询的响应后，
您可以将响应转换为 google数据表。

var dataTable = response.getDataTable();

并且google数据表有将数据表转换为JSON的方法。

var jsonData = dataTable.toJSON();

这将返回一个 JSON 字符串。

如果您想使用 JavaScript 处理 JSON，
您可以解析字符串...

jsonData = JSON.parse(jsonData);

JSON 将是一个具有两个属性的对象，cols并且rows. 您可以在此处
查看 JSON 结果的示例...

使用以下代码查看此小提琴以获取工作示例...

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);
  });
});