Query Language not working with new Sheets

Romain Vialard

unread,

Jan 28, 2014, 11:24:53 AM1/28/14

to google-visua...@googlegroups.com

I thought it was working but the method setQuery() doesn't seem to work with the new version of Sheets.

As an example, you can try this Sheet with the code available here:

https://code.google.com/apis/ajax/playground/?type=visualization#using_the_query_language

The query query.setQuery('SELECT A,D WHERE D > 100 ORDER BY D'); has no effect.

Is there a plan to make it work in the near future, or a workaround ? (I tried v1.1 of the viz API but it didn't seem to work either)

Mitchell Foley

unread,

Jan 29, 2014, 3:31:41 PM1/29/14

to google-visualization-api

Hello,

Can you post a full example of the code you tried to run?

Thanks,

Mitch

Romain Vialard

unread,

Jan 30, 2014, 2:21:23 AM1/30/14

to google-visua...@googlegroups.com

Sure, here it is:

<head>

<title>

Google Visualization API Sample

</title>

google.load('visualization', '1', {packages: ['linechart']});

</script>

var visualization;

function drawVisualization() {

// To see the data that this visualization uses, browse to

// http://spreadsheets.google.com/ccc?key=pCQbetd-CptGXxxQIG7VFIQ

var query = new google.visualization.Query(

'https://docs.google.com/spreadsheets/d/1uzuafFtIi1f9Z3J0_KTzo7LDRubfe9WPzfE7FrX7oSs/edit#gid=0');

// Apply query language.

query.setQuery('SELECT A,D WHERE D > 100 ORDER BY D');

// Send the query with a callback function.

query.send(handleQueryResponse);

}

function handleQueryResponse(response) {

if (response.isError()) {

alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

return;

}

var data = response.getDataTable();

visualization = new google.visualization.LineChart(document.getElementById('visualization'));

visualization.draw(data, {legend: 'bottom'});

}

google.setOnLoadCallback(drawVisualization);

</script>

</head>

</body>

</html>

Romain Vialard

unread,

Feb 5, 2014, 3:56:33 PM2/5/14

to google-visua...@googlegroups.com

Hi Mitch,

Can you confirm if it is working or not for you ?

rocits

unread,

Feb 7, 2014, 12:02:46 AM2/7/14

to google-visua...@googlegroups.com

Hey Romain ,

No it's not working

You may have posted the edit version by accident, anyway, the old 'table query' tq & tqx version does not seem work yet with the new format.

i.e. /tq?key=...&pub=1

The new format seems to be a 'solid' URL with # symbol parameters instead of a ? symbol .

i.e. /d/{key}/...#

Also View > List [ /lv?key=...] is missing from the main menu, maybe one to watch for changes.

Hopefully it is all in prep for Cloud/BigData seemless integration.. which will be Epic..no doubt! :)

..and maybe html view tq?tqx=out:html&key=... could be combined with JavaScript frameworks such as angular.js...that would be seriously fast & powerful control

Mitchell Foley

unread,

Feb 7, 2014, 1:14:18 PM2/7/14

to google-visualization-api

Hi Romain,

It looks like you have a few errors in your code.

First, you're loading the library like this:

google.load('visualization', '1', {packages: ['linechart']});

You actually don't want to load the linechart package, you want to load the corechart package. Try this instead:

google.load('visualization', '1', {packages: ['corechart']});

Second, you have the wrong type of url for your data source. Instead, use this url: https://spreadsheets.google.com/a/google.com/tq?key=pCQbetd-CptGXxxQIG7VFIQ&pub=1

Hope this helps,

Mitch

On Wed, Feb 5, 2014 at 3:56 PM, Romain Vialard <romain....@gmail.com> wrote:

Romain Vialard

unread,

Feb 8, 2014, 2:16:34 PM2/8/14

to google-visua...@googlegroups.com

Hi Mitch,

The code I've added in this thread is just a copy of a code from the playground. And it's not really important.

I just need to know if the method setQuery() should work with the new version of Google Sheets, because for me it doesn't.

(by the way, in the new version of Sheets, the URL is no more https://spreadsheets.google.com... but 'https://docs.google.com/spreadsheets/d/... instead)

Eric So

unread,

Feb 18, 2014, 1:35:53 PM2/18/14

to google-visua...@googlegroups.com

I'm running into this problem too. I have a Ritz sheet at

https://docs.google.com/a/google.com/spreadsheets/d/1TqadCSRPJ2AEeS12fBBygbX53ZpghlqaCuxdAj31aWc#gid=2

and I'm using the Chart Visualization API to to display it however when I load the page I get the error "Uncaught SyntaxError: Unexpected token <"

I'm using the Table API:

google.load('visualization', '1.0', {packages: ['table']});

I tried loading a Trix spreadsheet which worked, but I was unable to get the API to load the correct sheet:

https://docs.google.com/a/google.com/spreadsheet/tq?key=0AobNU9T3MusKdGFqZk9SRHNvbUlJYkFnb3RuSkt4QlE&sheet=Volume

The API would always load the first sheet. Have you had any luck?

Jon Orwant

unread,

Feb 19, 2014, 2:41:38 PM2/19/14

to google-visua...@googlegroups.com

Hi Romain,

We've forwarded this to the Google Sheets team and they'll investigate. Thanks for alerting us (and them) to this!

Jon

Martin Hawksey

unread,

Mar 18, 2014, 4:31:11 PM3/18/14

to google-visua...@googlegroups.com

Just a note for anyone else coming to this thread. Using var query = new google.visualization.Query(

'//docs.google.com/spreadsheets/d/1cnle5u7v-TGFInsFj_pooFWvwbti0NCL1anegCrltXU/gviz/tq');

You can get the google.visualization.Query.setResponse data but the query.setQuery method still doesn't work. (throws

Error: Problem parsing d="" format+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:1315

R.naformat+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:1315
R.fyaformat+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:1359
R.$oformat+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:1350
R.PAaformat+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:1382
R.$oformat+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:1381
R.Zeformat+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:1401
R.setDataformat+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:1401
(anonymous function)format+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:1298
handleQueryResponseretrieve_cache?unique_id=ed05b3521f28f8412b4fe288b79bf74b269262ea:41
R.jEformat+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:272
gvformat+en_GB,default+en_GB,ui+en_GB,browserchart+en_GB,linechart+en_GB.I.js:264
(anonymous function)tq?tq=SELECT%20A%2CD%20WHERE%20D%20%3E%20100%20ORDER%20BY%20D&tqx=reqId%3A0:1

Sergey Grabkovsky

unread,

Mar 18, 2014, 4:35:34 PM3/18/14

to google-visua...@googlegroups.com

Hello Martin, your problem does not appear to be the same as Romain's. It seems that your issue stems from not having access to the spreadsheet. For instance, I am getting the error "GET https://docs.google.com/a/google.com/spreadsheets/d/1cnle5u7v-TGFInsFj_pooF…CL1anegCrltXU/gviz/tq?tq=SELECT+A,D+WHERE+D+%3E+100+ORDER+BY+D&tqx=reqId:0 403 (Forbidden)" along with the "Problem parsing" error that you are getting. Making the spreadsheet publicly viewable should fix this issue.

- Sergey

Martin Hawksey

unread,

Mar 18, 2014, 5:03:39 PM3/18/14

to google-visua...@googlegroups.com

Hi Srgey - I had File > Published to the web which I recall was enough to get Visualisation API access. I've now shared the sheet which will hopefully let you replicate the Error: Problem parsing d="" error and see in this bl.ocks http://bl.ocks.org/mhawksey/9629539

--

Martin Hawksey

Tw: @mhawksey

G+: google.com/profiles/mhawksey

Sergey Grabkovsky

unread,

Mar 18, 2014, 5:22:25 PM3/18/14

to google-visua...@googlegroups.com

Hi Martin, this error is occurring because you are loading the old linechart package, which is extremely deprecated. You need to load the package "corechart" in order to get our new charts and newest features. When you do, you will inevitably get the error "All series on a given axis must be of the same data type". This is because the data is not coming in as expected because of the issue that Romain mentioned earlier in the thread -- since queries are not properly processed on the server-side, you get all 4 columns of your spreadsheet, instead of just A and D like you requested. You could work around this problem while we continue to investigate by creating a DataView with just the columns that you want. Here is a fiddle with those issues somewhat mended: http://jsfiddle.net/FwHNx/

- Sergey

Martin Hawksey

unread,

Mar 18, 2014, 5:36:11 PM3/18/14

to google-visua...@googlegroups.com

Hi Sergey - thanks for your help. I can see what you mean now. Fingers crossed for a setQuery fix

Thanks

Message has been deleted

Jereme Guenther

unread,

Apr 3, 2014, 7:20:45 PM4/3/14

to google-visua...@googlegroups.com

Tom Fletcher

unread,

Apr 8, 2014, 6:33:14 AM4/8/14

to google-visua...@googlegroups.com

+1.

Complete spreadsheet is returned by https://docs.google.com/spreadsheets/d/<MYKEY>/gviz/tq?tq=SELECT%20A%20WHERE%20B%20=%20Mon. ie. the tq parameter is ignored.

Scott Sherris

unread,

Apr 9, 2014, 5:41:54 PM4/9/14

to google-visua...@googlegroups.com

+1 here too. Panicking a little that this won't be working before the old Spreadsheets stops

jon_pdx

unread,

Apr 14, 2014, 11:44:08 PM4/14/14

to google-visua...@googlegroups.com

I had this problem as well. I just wanted to mention that there are a couple of work arounds until they get it fixed.

Personally, I was playing with the new candlestick chart based on the new stock quote speadsheet function. ironically the speadsheet function and the candlestick chart have different default ordering of the open, high, low, close quotes. Looking at my spreadsheet, the quote function appears to be in the usual order. To get the chart going, it was pretty easy to create a new view and select the columns that I needed in the order that the candlestick chart expects: low, open, high, close.

I think this is the right reference: https://developers.google.com/chart/interactive/docs/reference#DataView_setColumns

The other option that I found, is that there is a join function that works on the DataTables and maybe DataViews as well. you can find it listed under Data Manipulation Methods as part of the larger Visualization API.

https://developers.google.com/chart/interactive/docs/reference#google_visualization_data

So maybe that can meet some people's needs until they get it fixed. I would assume most people posting would already know that, but sometimes a little reminder is helpful.

jon

Gomez

unread,

Apr 30, 2014, 11:07:42 AM4/30/14

to google-visua...@googlegroups.com

Alguna solución a esto?

Sigue sin funcionar el parametro tq en la nueva url de los nuevos spreadsheet

; (

El martes, 8 de abril de 2014 5:33:14 UTC-5, Tom Fletcher escribio:

Raj Jagesar

unread,

Jun 8, 2014, 8:05:39 AM6/8/14

to google-visua...@googlegroups.com

+1, is this going to be fixed? :\

Op dinsdag 28 januari 2014 17:24:53 UTC+1 schreef Romain Vialard:

Sergey Grabkovsky

unread,

Jun 8, 2014, 9:10:27 AM6/8/14

to google-visua...@googlegroups.com

This is already fixed.

mth

unread,

Jun 13, 2014, 6:33:57 PM6/13/14

to google-visua...@googlegroups.com

I continue to have the same problem as Tom Fletcher.

Sergey Grabkovsky

unread,

Jun 16, 2014, 10:26:21 AM6/16/14

to google-visua...@googlegroups.com

Hello mth,

This issue has since been fixed. If you would like to post the format of the URL with which you are attempting to access your spreadsheet, that would be great. Alternatively, you could create a dummy spreadsheet and post the URL that you are trying that doesn't work, or you could email me directly at gra...@google.com with your real spreadsheet URL, and we can look at your issue in private.

Scott Sherris

unread,

Jun 16, 2014, 10:43:32 AM6/16/14

to google-visua...@googlegroups.com

I'm getting it to work a bit with a dummy spreadsheet here:
https://docs.google.com/spreadsheets/d/1EbXvQW2IQ3QCoeXj-1tt0Ed_VwULk-YJZ7373MBG8PQ/edit#gid=0
With this query:
https://docs.google.com/spreadsheets/d/1EbXvQW2IQ3QCoeXj-1tt0Ed_VwULk-YJZ7373MBG8PQ/gviz/tq?tqx=out:html&gid=0&tq=Select+B,C+where+F%3D%27C%27

And these results:

google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"99345440","table":{"cols":[{"id":"B","label":"","type":"string"},{"id":"C","label":"","type":"string"}],"rows":[{"c":[{"v":"123@jomama"},{"v":"Jome"}]},{"c":[{"v":"con...@home.com"},{"v":"321 Contact"}]},{"c":[{"v":"bxr...@fredex.com"},{"v":"Betty Rubble"}]},{"c":[{"v":"wxf...@bedrock.com"},{"v":"Wilma Flintstone"}]}]}});

Obviously there have been some changes to the API since I used to have the option of CSV or html results and now they're coming only in JSON... mostly. Is there new documentation? Also I don't understand what the "c": and "v": represent here.

Sergey Grabkovsky

unread,

Jun 16, 2014, 10:53:24 AM6/16/14

to google-visua...@googlegroups.com

Hello Scott, the structure of the DataTable has not changed. In order to understand the 'c' and 'v' values, you need to understand the structure of the DataTable, which is described here. If there is something missing from the documentation, or if you are still confused about something, feel free to respond on this list.

The out:html and out:csv are supposed to work, but currently they do not. This is a bug, and will be fixed in the coming weeks.

Scott Sherris

unread,

Jun 16, 2014, 11:19:55 AM6/16/14

to google-visua...@googlegroups.com

Thank you for your response. I look forward to when the output functionality will be working. Are there any other unresolved bugs in this or is that the last one?

Sergey Grabkovsky

unread,

Jun 16, 2014, 11:25:45 AM6/16/14

to google-visua...@googlegroups.com

It's the last one that I'm aware of. If you find any more bugs, feel free to open issues for them.

On Mon Jun 16 2014 at 11:19:59 AM, Scott Sherris <scotts...@gmail.com> wrote:

Jian Sun

unread,

Jun 16, 2014, 6:09:35 PM6/16/14

to google-visua...@googlegroups.com

Hi, Sergey,

Could you use Scott's dummy spreadsheet here:
https://docs.google.com/spreadsheets/d/1EbXvQW2IQ3QCoeXj-1tt0Ed_VwULk-YJZ7373MBG8PQ/edit#gid=0 to create a working query? He created one but it doesn't work, I really want to see what the working one looks like.

Thank you very much !

Sergey Grabkovsky

unread,

Jun 17, 2014, 9:38:56 AM6/17/14

to google-visua...@googlegroups.com

Here is an example query: https://docs.google.com/a/google.com/spreadsheets/d/1EbXvQW2IQ3QCoeXj-1tt0Ed_VwULk-YJZ7373MBG8PQ/gviz/tq?tq=SELECT%20A,B,E%20WHERE%20E%3E4

The query is SELECT A, B, E WHERE E > 4

Message has been deleted

Jian Sun

unread,

Jun 17, 2014, 12:57:19 PM6/17/14

to google-visua...@googlegroups.com

Hi, Sergey,

When I click your example query link, I saw the following in the browser window. I was expecting to see a HTML page like we saw with the old spreadsheet version(for example https://spreadsheets.google.com/tq?tqx=out:html&tq=select+D,F,count(C)+where+(B+contains+'Agoulnik')+group+by+D,F&key=0AqAPbBT_k2VUdEtXYXdLdjM0TXY1YUVhMk9jeUQ0NkE)

google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1579047909","table":{"cols":[{"id":"A","label":"","type":"datetime","pattern":"M/d/yyyy H:mm:ss"},{"id":"B","label":"","type":"string"},{"id":"E","label":"","type":"number","pattern":"0"}],"rows":[{"c":[{"v":new Date(2014,2,30,12,25,49),"f":"3/30/2014 12:25:49"},{"v":"bxr...@fredex.com"},{"v":5.0,"f":"5"}]},{"c":[{"v":new Date(2014,2,30,12,14,5),"f":"3/30/2014 12:14:05"},{"v":"wxf...@bedrock.com"},{"v":6.0,"f":"6"}]}]}});

I tried https://docs.google.com/a/google.com/spreadsheets/d/1EbXvQW2IQ3QCoeXj-1tt0Ed_VwULk-YJZ7373MBG8PQ/gviz/tq?tqx=out:html&tq=SELECT%20A,B,E%20WHERE%20E%3E4 but it does not work.

Could you please provide an example on how to export a html page through the query?

Thank you.

Sergey Grabkovsky

unread,

Jun 17, 2014, 1:24:56 PM6/17/14

to google-visua...@googlegroups.com

Hi, this is a known issue with the new sheets. There is an external issue relating to this that you can star to be kept up to date. The Docs team is aware of this and is working on a fix. In the meantime, you can use the Table Visualization.

Scott Sherris

unread,

Jul 2, 2014, 12:13:25 PM7/2/14

to google-visua...@googlegroups.com

HTML is working now, but I think I found a new issue - query doesn't work when querying for a blank cell. I opened a new issue 1652. Please go star it if you find you have this issue too. (https://code.google.com/p/google-visualization-api-issues/issues/detail?id=1652)

This query should return 2 records - Fred Flintstone and Barney Rubble based on the above test Sheet

https://docs.google.com/spreadsheets/d/1EbXvQW2IQ3QCoeXj-1tt0Ed_VwULk-YJZ7373MBG8PQ/gviz/tq?tqx=out:html&gid=0&tq=Select+B,C+where+D=%27%27

Sergey Grabkovsky

unread,

Jul 2, 2014, 12:34:00 PM7/2/14

to google-visua...@googlegroups.com

This is arguable. Docs doesn't treat empty cells as empty strings, it treats them as nulls. As far as I can tell, this has always been the behavior. You can test for cells being null by changing your query to SELECT B, C WHERE D IS NULL, as per our Query Language Reference.

Scott Sherris

unread,

Jul 2, 2014, 12:38:00 PM7/2/14

to google-visua...@googlegroups.com

That works, but this is a change from the behavior of queries from the old spreadsheets. I had begun testing making copies of data from old sheets to new, but if this stands, it looks like I'll also need to rewrite the queries in my apps as well.

Scott Sherris

unread,

Jul 2, 2014, 12:46:48 PM7/2/14

to google-visua...@googlegroups.com

Actually, let me clarify - I actually cannot get the "IS NULL" statement to work on the Old Sheets, so to me this seems like a big change. I can't "flip a switch" to go to the new sheets - I have to coordinate app query changes as well as using new keys and keeping the data synchronized. You're also going to see a lot of breakage if the promised conversion of old sheets to new sheets ever happens.

I don't have a test environment set up that will show this, but if you think you might find that helpful I can create one.

Sergey Grabkovsky

unread,

Jul 2, 2014, 12:54:42 PM7/2/14

to google-visua...@googlegroups.com

This is the behavior that I see for old sheets as well. I have this old sheet here: https://docs.google.com/spreadsheet/ccc?key=0Ap-GB-Crk0g3dHBsRkdNbFRkVUxKemhmN3Z2S1VFX2c&usp=drive_web#gid=0

The query SELECT A,B WHERE B='' returns 0 rows

https://spreadsheets.google.com/tq?key=0Ap-GB-Crk0g3dHBsRkdNbFRkVUxKemhmN3Z2S1VFX2c&tq=SELECT+A,B+WHERE+B=%27%27

The query SELECT A,B WHERE B IS NULL returns 1 row

https://spreadsheets.google.com/tq?key=0Ap-GB-Crk0g3dHBsRkdNbFRkVUxKemhmN3Z2S1VFX2c&tq=SELECT+A,B+WHERE+B+IS+NULL

On 1404319609885, Scott Sherris <scotts...@gmail.com> wrote:

Scott Sherris

unread,

Jul 2, 2014, 1:03:53 PM7/2/14

to google-visua...@googlegroups.com

Try this example:
https://docs.google.com/spreadsheet/tq?tqx=out:html&key=0AmYaCDUmHzF8dFEtcjZDQTlkanNkVVpCYzVlRXFpWVE&gid=0&tq=Select+B,C+where+D%20is%20null
https://docs.google.com/spreadsheet/tq?tqx=out:html&key=0AmYaCDUmHzF8dFEtcjZDQTlkanNkVVpCYzVlRXFpWVE&gid=0&tq=Select+B,C+where+D=%27%27
For the old sheet: https://docs.google.com/spreadsheet/ccc?key=0AmYaCDUmHzF8dFEtcjZDQTlkanNkVVpCYzVlRXFpWVE#gid=0

Compare to:
https://docs.google.com/spreadsheet/tq?tqx=out:html&key=1EbXvQW2IQ3QCoeXj-1tt0Ed_VwULk-YJZ7373MBG8PQ&gid=0&tq=Select+B,C+where+D%20is%20null
https://docs.google.com/spreadsheets/d/1EbXvQW2IQ3QCoeXj-1tt0Ed_VwULk-YJZ7373MBG8PQ/gviz/tq?tqx=out:html&gid=0&tq=Select+B,C+where+D=%27%27
For the new sheet: https://docs.google.com/spreadsheets/d/1EbXvQW2IQ3QCoeXj-1tt0Ed_VwULk-YJZ7373MBG8PQ

Sergey Grabkovsky

unread,

Jul 2, 2014, 2:59:09 PM7/2/14

to google-visua...@googlegroups.com

Hmm I'm not sure why they are behaving differently. You could just do a query like SELECT B,C WHERE D IS NULL OR D = ''

Reply all

Reply to author

Forward