Closed orwant closed 9 years ago
To clarify, all I had to do was replace the & with a ? in the gviz_url parameter to
retrieve the proper response:
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"600199841","table":{"cols":[{"id":"A","label":"Requestor
Name","type":"string"},{"id":"B","label":"Requestor Email","type":"number","pattern":"General"}],"rows":[{"c":[{"v":"a"},{"v":1.0,"f":"1"}]},{"c":[{"v":"b"},{"v":2.0,"f":"2"}]},{"c":[{"v":"c"},{"v":3.0,"f":"3"}]}]}});
My workaround is to construct the datasource URL with the tq parameter already encoded
and appended:
function buildQuery(url, statement) {
var url = url + "?tq=" + escape(statement);
return new google.visualization.Query(url);
}
Original issue reported on code.google.com by tgrills@costco.com
on 2015-02-19 22:31:22
Hi, I'm not really seeing an issue with the Query. Here's a simple jsfiddle that I made
in an attempt to reproduce your issue: http://jsfiddle.net/4rzaejd9/
Would it be possible for you to modify it such that it fails?
It looks like the issue might be with how the script.google.com/... url is built, and
not the query one. Are you using AppScripts?
Original issue reported on code.google.com by grabks@google.com
on 2015-02-20 15:08:06
Yes, as the URL in step 4 above indicates, I'm sending the query from an App Script.
I am not including the jsapi script, because if I do, I get an "Uncaught TypeError:
Can't add property loader, object is not extensible".
This code fails in that it returns the data from entire spreadsheet, ignoring the select
statement:
var query = new google.visualization.Query('https://docs.google.com/a/costco.com/spreadsheets/d/<ss
key here>/gviz/tq');
query.setQuery("select A, B");
query.send(handleQueryResponse);
This code works correctly - returns only columns A and B:
var query = buildQuery(
'https://docs.google.com/a/costco.com/spreadsheets/d/<ss key here>/gviz/tq',
"select A, B");
function buildQuery(url, statement) {
var url = url + "?tq=" + escape(statement);
return new google.visualization.Query(url);
}
The problem is definitely with how the gviz_url parameter is being constructed from
Apps Script - there is no query string since there is no question mark in the URL,
The problem is reproducible from an App Script.
code.gs:
function doGet() {
var page = HtmlService.createTemplateFromFile("index").evaluate()
.setTitle('Query Bug').setSandboxMode(HtmlService.SandboxMode.NATIVE);
return page;
}
index.html:
<script type="text/javascript">
google.load("visualization", "1", {packages:["treemap","annotationchart","table","corechart","controls"]});
google.setOnLoadCallback(initialize);
function initialize() {
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1eJf1-mzrFIUe6ucRMPTR1S8jDrUX_-xJFcrXmUKVAZ8//gviz/tq');
query.setQuery("select A, B");
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(data, {showRowNumber: true, allowHtml : true});
}
</script>
<div id="table_div" style="width:60%"></div>
The script (https://script.google.com/d/1aufYb1-6z0ciVGW404Ellei1MpEHUIDLqNQPaoA5milB8Ob3LdrpSJjh/)
and spreadsheet in the above example the last example is publicly accessible from my
personal account.
The problem is not reproducible in the fiddle.
Original issue reported on code.google.com by tgrills@costco.com
on 2015-02-20 16:18:14
Hmm.. So I attempted to reproduce the issue in an AppScript, and copied your code pretty
much verbatim, but I'm simply not seeing the issue. Here is the script I created: https://script.google.com/macros/d/1Z16s3dc6JhuAsFQ-ND36v0e4OtdSMLKnfBpiPdDimygzgCmuR_rOMSY1/edit
And here it is running: https://script.google.com/macros/s/AKfycbz6prnkBTBKfc-tO0VU0GMv7mDqaEvcExOTQpRKMn_U/dev
As you can see, the table renders with no issues whatsoever.
On another note, I was unable to access the script that you linked to. I get an error
saying that it doesn't exist for some reason.
Original issue reported on code.google.com by grabks@google.com
on 2015-02-20 16:38:39
I also get the error "Sorry, the file you have requested does not exist." when trying
to run your script. I changed the access in the Publish dialog of my script from "Only
myself" to "Anyone, even anonymous". Perhaps that's the issue with your script.
I made a copy of your script and published it locally, and it has the same issue -
it returns the entire spreadsheet data and renders it, instead of only columns A and
B.
I also noted that you have two forward slashes in your Query URL, but that does not
seem to matter.
Original issue reported on code.google.com by tgrills@costco.com
on 2015-02-20 17:00:11
OK, so I was able to reproduce this issue in AppScripts. I'm investigating it now. It
looks like the AppScripts sandbox is causing the Query to get confused, and append
parameters with '&' instead of '?', since it looks like the URL already has some parameters.
Thanks for your report!
Original issue reported on code.google.com by grabks@google.com
on 2015-02-20 18:16:54
After talking to someone from AppScripts, it was brought to my attention that you could
use IFRAME mode (instead of NATIVE mode), which does not rewrite URLs. If this is insufficient
for you, please file a bug against AppScripts.
Original issue reported on code.google.com by grabks@google.com
on 2015-02-20 18:27:11
Original issue reported on code.google.com by
tgrills@costco.com
on 2015-02-19 19:25:22