converting Google Visualization Query result into javascript array

Question

The url to the spreadsheet I am querying is

docs.google.com/spreadsheets/d/1EIBhBQY1zbdBEKXsJIY1uyvdQw0b1cIBSrBE_tZvA6Y/edit?usp=sharing

---

The query url being used is

https://spreadsheets.google.com/tq?tqx=out:&key=1EIBhBQY1zbdBEKXsJIY1uyvdQw0b1cIBSrBE_tZvA6Y&gid=0&headers=1&tq=select%20B%2CC%2CD%20where%20(A%20matches%20%22DIS%22)

Is there a way to convert or store this result in a JavaScript array?

var dis = ["The Walt Disney Company","Entertainment",.1]

I need to be able to manipulate the data at one point and add the new data to the visualization.

Data from one of multiple queries --> Convert to array --> Manipulate data ex: multiplying an input --> data.addRows(manipulated input);

Answer 1

Your query does return a string containing JSON wrapped in a function call:

var responseText = 'google.visualization.Query.setResponse({…});';

This is because you specified out: as an argument for tqx (see Google Developers guides).

If you want it all raw, you can extract and parse the JSON of multiple queries and push the data to an array, so you end up with an array of arrays of row data. For your single query, you could start from something like this:

responseJSON = JSON.parse(
responseText.replace(/(^google\.visualization\.Query\.setResponse\(|\);$)/g,'')
);
var rowsArray = [];
responseJSON.table.rows.forEach(function(row){
    var rowArray = [];
    row.c.forEach(function(prop){ rowArray.push(prop.v); });
    rowsArray.push(rowArray);
});
console.log(rowsArray); // ===  [["The Walt Disney Company", "Entertainment", 0.1]]

Answer 2

According to Google's documentation on their Visualization API for response formats, you can add a header in your request that will return JSON without the function or comment.

If you add a header named X-DataSource-Auth in your request, the Visualization API will respond in JSON format rather than JSONP format, which is the default format of the response and includes the JSON wrapped in a function handler.

However, even with this header present, the API prepends a strange string to the response: )]}' which I think has to do with the anti-content-sniffing mentioned by @Diego. Okay, Google — even with an OAuth token do you really need to do that?

So, to get at the actual JSON in that response, you can use the following Javascript to get around it. Assume responseBody is what the API actually returns to you, and that data is storing the JSON you want.

var data = JSON.parse(responseBody.replace(/^\)]\}'\n/, ''));

Answer 3

There is a more straightforward solution to this. What you get in the response is a JSONP string whose data is hold within a callback function, just as @dakab has mentioned.

Besides this, recently Google has included some extra text in the response to help with some anti-content-sniffing protections to their API. You can read more about this in this Github thread. The response you get now is an unparseable string in this form:

/*O_o*/
google.visualization.Query.setResponse({…});

One way to deal with both issues (the "comment" string and the data hidden inside the callback function) is to evaluate the function. Whether this is risky or not is something intrinsic to the JSONP format, so you must be aware of where your response comes from and decide if it's worth the risk. But, considering it comes from a request to a Google server, and in terms of parsing, it works.

So in your case, what you could do is just declare the callback function (note that you can pass your own function name in the query string, as also mentioned in the Google Developers guides) and then evaluate it. I take inspiration on this thread:

//Declare your call back function
function callback(data){
  return data;
}
//Evaluate and store the data in your callback function
var result = eval(UrlFetchApp.fetch(url + uri, options).getContentText());

In "result" you'll have an already parsed JSON that you can convert to whatever you wish.

Answer 4

Assuming str is the returned JSONP formatted response:

var str = `/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"403123069","table":{"cols":[{"id":"A","label":"Timestamp","type":"datetime","pattern":"dd/MM/yyyy HH:mm:ss"},{"id":"B","label":"AskGod Search Query","type":"string"}],"rows":[{"c":[{"v":"Date(2020,9,25,12,30,5)","f":"25/10/2020 12:30:05"},{"v":"لا أعرف لماذا"}]}],"parsedNumHeaders":1}});`

console.log(JSON.parse(str.match(/(?<=.*\().*(?=\);)/s)[0]))