converting Google Visualization Query result into javascript array - Stack Overflow

admin管理员组

文章数量:1356350

The url to the spreadsheet I am querying is

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

---

The query url being used is

:&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);

The url to the spreadsheet I am querying is

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

---

The query url being used is

https://spreadsheets.google./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);

• javascript
• arrays
• google-visualization
• google-query-language

Share Improve this question Follow edited Aug 1, 2015 at 22:13 user4639281 asked Aug 1, 2015 at 20:11 GeorgeGeorge 16311 silver badge1212 bronze badges 2

• Have you tried any actual JavaScript code yet, apart from the URL query? – dakab Commented Aug 1, 2015 at 20:22
• yes i am currently using one query for a visualization but i am trying to add rows- the addRows( ); only allows arrays for additional data inputs- I am trying to add data from multiple queries for the single visualization – George Commented Aug 1, 2015 at 21:03

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 4

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]]

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 ment.

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/, ''));

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 "ment" 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 es from and decide if it's worth the risk. But, considering it es 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.

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]))

本文标签： converting Google Visualization Query result into javascript arrayStack Overflow