Bug: New Sheets do not properly process Queries

[orwant]

What steps will reproduce the problem? Please provide a link to a
demonstration page if at all possible, or attach code.

Create a query based on the new spreadsheet URLs (ie "https://docs.google.com/spreadsheets/d/{key}/").
 Using a query in the form:

new Query('https://docs.google.com/spreadsheets/d/{key}/').send(responseHandler);

or the form:

new Query('https://docs.google.com/tq?key={key}').send(responseHandler);

the request is redirected to "https://docs.google.com/spreadsheets/d/{key}/tq?tqx=reqId:0"
which returns a 404 error.

What component is this issue related to (PieChart, LineChart, DataTable,
Query, etc)?
Query, Google Docs Spreadsheets

Are you using the test environment (version 1.1)?
(If you are not sure, answer NO)
No.

What operating system and browser are you using?
Windows 7 x64 Chrome

*********************************************************
For developers viewing this issue: please click the 'star' icon to be
notified of future changes, and to let us know how many of you are
interested in seeing it resolved.
*********************************************************

Original issue reported on code.google.com by drew_gallant@abtassoc.com on 2014-02-11 15:31:40

[orwant]

Perhaps your URL is somehow malformed? I really can't imagine why requesting the spreadsheet
from the browser would work and it would not work from PHP. The only remaining things
that I would check is that your spreadsheet is publicly viewable and I would try requesting
it without a query, to see if that's the issue.

Original issue reported on code.google.com by grabks@google.com on 2014-06-09 13:48:53

[orwant]

I used Google php client api's "Google_Http_Request" class instead of curl request in
php code. Still we are getting the same response. New version Spreadsheet returns responseHttpCode
= 404 and Old version returns 200.

Original issue reported on code.google.com by shermiep on 2014-06-10 14:32:39

[orwant]

OK. I am out of ideas to try hypothetically. If you would like to create a test spreadsheet
and share that or email me directly at grabks@google.com with your real spreadsheet
data, perhaps we could figure something out together.

Original issue reported on code.google.com by grabks@google.com on 2014-06-10 14:35:55

[orwant]

Is there any documentation/example for visualization api(in php) for querying google
spreadsheet?

Original issue reported on code.google.com by roopesh.naik on 2014-06-13 14:57:55

[orwant]

I am unable to get mine to work (public w/ URL).  Perhaps the sheet (gid) notation is
problematic?:

https://docs.google.com/spreadsheets/d/1R3vSXBlB4IRDsIZpiI20b05Qr9G5OlD5piBf5QnbYAY/edit#gid=1298400824/tq?tq=select%20A

Original issue reported on code.google.com by mthenne11 on 2014-06-13 22:51:04

[orwant]

mthene1: You are using the wrong URL to Query new Sheets. Everything after the hash
symbol (#) is ignored, so your /tq?tq=select%20A isn't even registered. You are actually
hitting the /edit URL, which gives you all the HTML for the Sheets in-browser editor.
Your URL needs to say /gviz/tq instead of edit. However, if you use the google.visualization.Query,
it should take care of that for you. Here is the correct URL that you need to hit:
https://docs.google.com/a/google.com/spreadsheets/d/1R3vSXBlB4IRDsIZpiI20b05Qr9G5OlD5piBf5QnbYAY/gviz/tq?tq=SELECT%20A

Original issue reported on code.google.com by grabks@google.com on 2014-06-16 13:58:48

[orwant]

Thanks for the quick reply.  I'm trying to use that with tqx=out:html, but it continues
to give the text to the google.visualization.Query.setResponse() call, with or without
the tqx spec.

https://docs.google.com/a/google.com/spreadsheets/d/1R3vSXBlB4IRDsIZpiI20b05Qr9G5OlD5piBf5QnbYAY/gviz/tq?tqx=out:html&gid=1298400824&tq=SELECT%20A

If there is a doc for doing this with the new format, I would appreciate a link.  I've
been looking, but have only been able to locate things like this:

http://www.bohyunkim.net/blog/archives/2831/comment-page-1

Mark

Original issue reported on code.google.com by mthenne11 on 2014-06-16 18:09:07

[orwant]

mthenne11: The docs team is looking into this. The out:csv case should be fixed within
the week, but I'm not sure if that will fix out:html as well. If it does not, I will
report it to the Docs team.

Original issue reported on code.google.com by grabks@google.com on 2014-06-16 18:13:17

[orwant]

I see that they've fixed it for the csv version, but not html.

This works:
https://docs.google.com/a/google.com/spreadsheets/d/1R3vSXBlB4IRDsIZpiI20b05Qr9G5OlD5piBf5QnbYAY/gviz/tq?tqx=out:csv&gid=1298400824&tq=SELECT%20*%20where%20A%20%3D%20'A1'

This doesn't:
https://docs.google.com/a/google.com/spreadsheets/d/1R3vSXBlB4IRDsIZpiI20b05Qr9G5OlD5piBf5QnbYAY/gviz/tq?tqx=out:html&gid=1298400824&tq=SELECT%20*%20where%20A%20%3D%20'A1'

I appreciate the effort.

Mark

Original issue reported on code.google.com by mthenne11 on 2014-06-20 20:43:37

[orwant]

The fix for out:html will be out in a week or two.

Original issue reported on code.google.com by grabks@google.com on 2014-06-20 21:14:53

[orwant]

The html fix is in, and working properly.  Huzzah!

Is there a way to specify a title for the generated html sheet?

Thanks!

Mark

Original issue reported on code.google.com by mthenne11 on 2014-07-02 17:14:20

[orwant]

I don't believe there is a way to specify the title.

Original issue reported on code.google.com by grabks@google.com on 2014-07-02 17:20:47

[orwant]

This thread has been very helpful as regards restructuring the query, but I'm wondering
if the html output issue has been fully fixed when it comes to the pivot function.

This query, for example: 

https://docs.google.com/spreadsheets/d/10bcRLuOTkjWNqlQstyFbnOJDE6ZfdhhqxoNFmS_u01Y/gviz/tq?tqx=out:csv&tq=select+E,+sum(F)+where+B+=+%27Medicaid%20Pharmacy%27+%20group+by+E+pivot(G)

Works correctly (*.csv), but this one returns blank cells in the html:

https://docs.google.com/spreadsheets/d/10bcRLuOTkjWNqlQstyFbnOJDE6ZfdhhqxoNFmS_u01Y/gviz/tq?tqx=out:html&tq=select+E,+sum(F)+where+B+=+%27Medicaid%20Pharmacy%27+%20group+by+E+pivot(G)

Any assistance would be appreciated. Thanks.
vr/Franz

Original issue reported on code.google.com by FranzSF on 2014-09-06 21:28:37

[orwant]

@Fran, I am experiencing similar issue.  It looks like for any aggregate function html
(out:html) returns empty html table. For example simple queries like 'SELECT sum(F)'
or 'SELECT B, sum(F) GROUP BY B' in your spreadsheet also return empty table with correct
number of rows!

https://docs.google.com/spreadsheets/d/10bcRLuOTkjWNqlQstyFbnOJDE6ZfdhhqxoNFmS_u01Y/gviz/tq?tqx=out:html&tq=select%20sum(F)

Any help from Google team is much appreciated,
Shiva

Original issue reported on code.google.com by shivamy on 2014-09-08 02:31:39

[orwant]

ok, so I got this to work to pull the data from a spreadsheet to html and csv but can't
get it to actually open in a google spreadsheet.  Is this possible with this method?
 make the out: googlespreadsheet...

Original issue reported on code.google.com by daniel.kariniemi@traxtech.com on 2014-09-10 22:31:55

[orwant]

I have a new bug to report.  When I make a copy of my spreadsheet, sometimes the copy
does not work correctly for URL queries.  Other times I have seen the copy work fine.
 I have a workaround.

Steps to reproduce:
1. copy this spreadsheet to your own account: https://docs.google.com/spreadsheets/d/1R3vSXBlB4IRDsIZpiI20b05Qr9G5OlD5piBf5QnbYAY/edit#gid=1625272076
2. go to the "Scratch" tab and update the red "Spreadsheet ID" cell with that for your
newly copied sheet, using the value from the document URL
3. notice that in the Scratch tab, the column B queries in the "Artist Reports" section
may indicate errors
4. if those cells indicate errors, fix each cell one-by-one with the following:
* Ctrl-C to copy the cell
* Delete to clear the contents
* Ctrl-V to paste back into the cell

Thanks for listening.

Mark

Original issue reported on code.google.com by mthenne11 on 2014-09-12 23:08:19

[orwant]

I thought I had more diagnostics for the bug reported in #71, but after pressing my
browser refresh button, the problem fixed itself.  Here's what I saw during the failure:

Refer to this copy of the spreadsheet:
https://docs.google.com/spreadsheets/d/1v6-hFxenvHzuvV4H_nmovkpzzngoW_FkbUfvFl3sP8o/edit#gid=0

* Go to the Shots tab.
* The NamedRange called ReportTypeShot is defined to be cell Shots!G1
* Cells F2:G6 use ReportTypeShot to generate a URL database query, but fail to work.
 They are similar to the contents of column E, which does work.
* Cell F8 is set =T(ReportTypeShot), but displays nothing in the cell.  It should show:
html

You may be able to reproduce the problem, as I have seen it occur multiple times, but
not every time I copy the sheet.

Spooky.

Mark

Original issue reported on code.google.com by mthenne11 on 2014-09-13 17:57:04

[orwant]

I created a new bug report for my recent postings.
https://code.google.com/p/google-visualization-api-issues/issues/detail?id=1740

Original issue reported on code.google.com by mthenne11 on 2014-09-16 16:25:00

[orwant]

Will the documentation be updated on this? Speaking of https://developers.google.com/chart/interactive/docs/querylanguage#Setting_the_Query_in_the_Data_Source_URL
, which is where I initially went to learn about this feature

Original issue reported on code.google.com by drew@rvt.com on 2014-11-12 17:28:34

[orwant]

@shiva I am getting the same issue as you trying to output html files with aggregate
functions but csv files work OK.

Original issue reported on code.google.com by andrewrossnewell on 2015-03-02 11:01:39

[orwant]

Thanks for the report of aggregate functions not functioning for out:html. I have filed
a bug against the Sheets team, and this will hopefully be fixed soon.

@drew: Which "this" are you referring to? Near as I can tell, everything in this thread
is a bug and should be fixed on the Sheets side, requiring no documentation changes.

Original issue reported on code.google.com by grabks@google.com on 2015-03-02 15:48:07

[orwant]

@gra...@google, #58

Your reply #58 helped me.  I am using google.visualization.Query() but it did not process
the gid sheet parameter with the cut&paste URL from the browser which contained the
url form:  /edit#gid=....   I only received the first sheet of my spreadsheet in the
response and not the request gid sheet.  When I switched to the gviz/tq/?gid=  URL
format I got the correct sheet.

It looks like either Query() needs to be fixed to process the /edit# format or your
documentation on querying a spreadsheet needs to updated https://developers.google.com/chart/interactive/docs/spreadsheets
 (it still recommends a simple cut&paste from the browser which contains the /edit#
format).

Original issue reported on code.google.com by mwrusso on 2015-03-03 19:12:07

[Subhanu]

The old url https://spreadsheets.google.com/tq?tq=.. is automatically now converted to a new url on submission https://docs.google.com/spreadsheets/d/{key}/gviz/tq but the issue that I see that it does not wait for the data on the sheet to get loaded and returns empty immediately. I have importrange and formulas on the sheet that takes a few seconds to recalculate but the https call does not seem to wait for the calculations to complete. Is there a timeout value or any other way to make it wait till the sheet is loaded and ready?

[whughes1950]

My issue is a little different. As of a few days ago (five or so), the response I'm getting back has changed from a direct json string to a file called "json.txt." I am using a proprietary XML language that has some javascrip capability to parse the returning string. My original "Get" tag (the query to the Google Sheet) looks like this:

<get varname="getData">https://docs.google.com/spreadsheets/d/1E-FgX0KiP7iabUCcg1aI1tyzGc9Op_zXSum9OJUL9Qw/gviz/tq?&amp;tq=select%20A%20where%20C%20%3D%20%22x%22%0A</get>

The "varname" attribute creates the variable "getData" which will contain the reponse string, which used to look like this:

google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1460598950","table":{"cols":[{"id":"A","label":"","type":"string"},{"id":"B","label":"","type":"string"},{"id":"C","label":"","type":"string"}],"rows":[{"c":[{"v":"2"},null,{"v":"x"}]}]}});

Instead, it now downloads a file called "json.txt" which contains the right stuff:

/*O_o*/ google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1460598950","table":{"cols":[{"id":"A","label":"","type":"string"},{"id":"B","label":"","type":"string"},{"id":"C","label":"","type":"string"}],"rows":[{"c":[{"v":"2"},null,{"v":"x"}]}]}});

But is unparsable as a string. Why did the response format change? Why is the json now returning as a file rather than just returning a string into my variable? This has broken a number of enterprise applications for us. We will have to cease using Google Sheets and recommend to our 100,000 clients that they do so also.

My full xml query and parser look like this:

`https://docs.google.com/spreadsheets/d/1E-FgX0KiP7iabUCcg1aI1tyzGc9Op_zXSum9OJUL9Qw/gviz/tq?&tq=select%20A%20where%20C%20%3D%20%22x%22%0A

'${getData}'.split(":")[14];

<setvar varname="cycleA" name="setvar_2">'${set1}'.split("}")[0];</setvar>
<setvar varname="cycle" name="setvar_3">'${cycleA}'.split("")[1];</setvar>`

Can anyone help with this?

[saurabbhgupta]

Even we are facing an issue just similar as above.

https://docs.google.com/spreadsheets/d/{key}/tq?tqx=out:html&tq=&gid=1

The url above used to return the sheet in an HTML form, but from last few days the HTML is eliminating all the blank rows while creating this table.

Our application has stopped working because of this update. Any help would be appreciated.

[dlaliberte]

Hi whughes1950,

[From the spreadsheets team.] This is in relation to the addition of some anti-content-sniffing protections to our API. Namely, we now give the response an attachment disposition and security prefix. For well-behaved browsers using <script> tags for their JSONP responses, this should have no effect. Apparently, it does break this guy's proprietary XML language. I would suggest that he get in touch with the language vendor to have it fixed.

For an explanation for why these things were added, I suggest reading https://miki.it/blog/2014/7/8/abusing-jsonp-with-rosetta-flash/, in particular the "Mitigations by website owners" section which describes precisely the things we added.