Google JS Script error on accessing protected Sheet

[adilyalcin]

Hi,

Google JS Script throws an internal execution error it received a request into a protected Google Sheet. It should fail gracefully and provide error information on response object, but the official GoogleSheet data endpoint currently returns an invalid function call, and JavaScript execution fails completely.

The issue seems to be missing reqId parameter on the response. reqId is available when the sheet is public, but it is not included when sheet is private. This seems to be a bug on Google's implementation.

To reproduce:

1. Set a sample sheet to private mode.
2. Make a request using default API: (Replace XYZ with sheet id)

var q=  new google.visualization.Query("https://docs.google.com/spreadsheets/d/XYZ/gviz/tq");
q.send(function(){ ... });

3. The console shows execution error "Missing query for request id: undefined". The query callback function isn't called because of API error in executing the injected script / response.

After tracing the problem, I found that the undefined value is "reqId" parameter to
the google.visualization.Query.setResponse function call, which GoogleSheet API returns.

More specifically, a call to https://docs.google.com/spreadsheets/d/XYZ/gviz/tq returns (if XYZ is protected sheet)

google.visualization.Query.setResponse({"version":"0.6","status":"error","errors":[{"reason":"access_denied","message":"ACCESS_DENIED","detailed_message":"This spreadsheet is not publicly viewable and requires an OAuth credential.  For more information, see https://support.google.com/docs/?p=gviz_tq_auth"}]});

Notice that reqId is not set in this response, but it is expected to be set.

And, if I set the sheet to public, the response is proper, AND includes reqId parameter.

google.visualization.Query.setResponse({"version":"0.6","reqId":"10","status":"ok", ... });

I tried to set the reqId parameter explicitly, but the response does not recognize / include this parameter. So, https://docs.google.com/spreadsheets/d/XYZ/gviz/tq?tqx=reqId:1 does not work either

Any help to gracefully detect sheets that are protected, or a solution a fix to Google API implementation, would be appreciated!

Thanks,

[dlaliberte]

Thanks for your detailed report.

Do you know if this is a new problem? Nothing has changed regarding the Query class, so I would suspect the problem involves the docs side, where they have made changes recently regarding security. Perhaps there is a change we need to make on the charts side to accommodate docs changes.

[adilyalcin]

I have recently updated to new Google APIs, to support loading private sheets. I don't know how long the problem has existed with the new APIs.

The issue seems to be what docs.google.com/spreadsheets/... endpoint returns. If it had only included reqId variable when there is an error, I think the rest would work fine. There would be no execution errors, and I can implement on top of the API to process the error and recover from it.

I found a work-around by loading the google docs query as a script with a custom callback function. However, this only works when the user is logged into a Google account. In other words, it doesn't work when the user is not logged into Google. Reason? If not logged in, the docs request returns an HTML file instead of JavaScript. So, for now, my workaround only works in special circumstances, by explicitly avoiding letting Google API execute the return string by taking control of the callback. So many hoops I had to jump through to have a partial solution.

If you have a way to contact people who works on Google Docs services, they may want to hear about this issue, and inconsistencies in their API. It should include reqId variable on error, and it should not return HTML, if we are to build applications that expect JavaScript.

Thanks!

Adil

[dlaliberte]

Thanks. The lack of a reqId in error responses is now acknowledged by the docs team, and they will be working on a fix. I don't know how long it will be until the fix shows up, but I suspect it will be a relatively easy fix.

[adilyalcin]

Wonderful. Thanks for your help!

[talkoren]

Also waiting for an update on this. Thanks!

[JeanRemiDelteil]

Also waiting on a fix on the reqId for error !
To add to this issue, as it's related to the spreadsheet server response:

The Google Spreadsheet server are not optimizing their answer if we make a call using the tqx.sig parameter.
Related documentation can be found here:
https://developers.google.com/chart/interactive/docs/dev/implementing_data_source#optimizingrequests

Here is the quoted documentation:

Optimizing JSON Responses

If a client makes two requests, and the data has not changed between requests, it makes sense not to resend the data--doing so would waste bandwidth. To make requests more efficient, the protocol supports caching the data on the client, and sending a signal in the response if the data has not changed since the last request. Here's how this works:

1. The client sends a request to the data source.
2. The data source generates a DataTable as well as a hash of the DataTable object, and returns both in its response (the hash is returned in the tqx.sig parameter). The Google Visualization API client caches the DataTable and sig value.
3. The client sends another request for data, including the cached tqx.sig value.
4. The data source can respond in one of two ways:
   • If the data has changed from the previous request, the data source sends back the new DataTable and new sig value hash.
   • If the data has not changed from the previous request, the data source sends back status=error, reason=not_modified, sig=old_sig_value.
5. In either case, the page hosting the chart gets a successful response, and can retrieve the DataTable by calling QueryResponse.getDataTable(). If the data is the same, it will simply be the cached version of the table.

Note that this only works for JSON requests from charts built on the Google Visualization API.

The issue is that when sending a request with a tqx.sig parameter, the server does not answer with described error status if the data did not changed.
However if the data changed, the sig value is different.

This is an issue for heavy answers: for big spreadsheets and bad networks the answer can take up to 30 secondes to be received.
The described mechanism would optimize this, but does not work.

To reproduce the issue:

1. define custom response handler:
   function handleResp(data){ console.log(data) }
2. make a request to a spreadsheet:
   var scriptDOM = document.createElement('script');
scriptDOM.src = "https://docs.google.com/spreadsheets/d/10-87akOyDA9jgwdlHARgKMBGB5fUTnhZ3dK3JajgWYA/gviz/tq?headers=2&range=A1:F&sheet=Fruits&tqx=reqId:0;responseHandler:handleResp";
document.head.appendChild(scriptDOM);
3. If the data did not changes, sig should be egal to '50862399', if not change it to value of the first query above:
   var scriptDOM = document.createElement('script');
scriptDOM.src = "https://docs.google.com/spreadsheets/d/10-87akOyDA9jgwdlHARgKMBGB5fUTnhZ3dK3JajgWYA/gviz/tq?headers=2&range=A1:F&sheet=Fruits&tqx=reqId:0;sig:50862399;responseHandler:handleResp";
document.head.appendChild(scriptDOM);
4. examine the answer. From the documentation, it should have a status=error field and reason=not_modified
   However it has not and the full response is received.