1

So, I've gotten to the point in my app where I can retrieve a list of spreadsheet documents from a user's Google Drive account by using the Google Spreadsheet API. I populate the file list in a ui control that users can click on to then retrieve the list of its worksheets. It's working as expected in some cases, but in others it is not. In my request, I use the url that comes back from the file list, and even so, the API responds with:

Sorry, the file you have requested does not exist.
Make sure that you have the correct URL and that the owner of the file hasn't deleted it.

Well, surely the file wasn't deleted. I got it back in the response to my request for the files list. I also can get to the file via a normal web browser. Also, the URL is correct because that's the one the API responded with. My code does not manipulate the url that comes back in that initial files response. In fact, here is the URL that is used to grab the worksheets:

https://spreadsheets.google.com/feeds/worksheets/{long key here}/private/full

So, my question is why does my request for some worksheets come back with a response with the actual list of worksheets, but on others (which I have access to, and I know exists) I get the faulty response.

Thanks, Arie

ariestav
  • 2,799
  • 4
  • 28
  • 56
  • Are you using the same credentials when you browse manually that you use in your code? If not, check permissions carefully. – Eric J. Apr 30 '14 at 00:52
  • Thanks for the suggestion. I will check and make sure. But why would the spreadsheet API return URLs to spreadsheets that the user cannot access? The scope of my app is read only: 'https://spreadsheets.google.com/feeds' – ariestav Apr 30 '14 at 01:21
  • Not sure exactly how Google's permission system works, but for many file systems the permission to list the contents of a directory and the permissions to open a specific file are separate. – Eric J. Apr 30 '14 at 01:25
  • Okay, I verified that I was logged in with the same account as the one that I am checking via the web interface to Google Drive, and sure enough the api states that it is missing. Not sure what to do, but I sense that it may be a bug? – ariestav Apr 30 '14 at 02:06
  • I'm seeing the same thing. Is something up with Google? In fact, I can POST to a specific sheet but I can't query from that same sheet. using the same account info everywhere. On other (older) sheets it does work – user2029890 Apr 30 '14 at 02:40
  • I hope it's something with Google. I just started noticing this behavior today. What about you? – ariestav Apr 30 '14 at 02:45
  • I just ran a test, queries work on my sheets with an older short key, but not on newer ones with a longer key. POSTing seems to work fine. – user2029890 Apr 30 '14 at 02:49
  • How do you get the ID? I ask because there is more then one ID per spreadsheet and some IDs don't work with the spreadsheet API any more (They stopped working around the time they added gdata access to the new spreadsheets). This google apps script method gives an ID that works: http://stackoverflow.com/questions/21842456/get-the-spreadsheet-key-that-is-in-the-url-not-ss-getid ? – eddyparkinson Apr 30 '14 at 03:10
  • The ID comes back as part of a URL for a sheet that is listed in the response after I retrieve and display the logged in users's available sheets. Why would Google want us to first get the list of Sheets if the keys they return in the url to the Sheets are incorrect. The API should return the correct url with the correct key to query it. Also, @eddyparkinson, I cannot use the google apps script as my app is desktop based. I have no environment in my desktop app to run the app script. Is it possible to run a script via an API? – ariestav Apr 30 '14 at 03:16
  • @eddyparkinson Is there a way I can manually get that other ID? I'm using the one taken directly from the URL. I obtain all the details for my sheets from https://spreadsheets.google.com/feeds/spreadsheets/private/full and there is no other key – user2029890 Apr 30 '14 at 03:22
  • @user202980 I wanted to do that, too, but apparently google "insists" that you first retrieve the spreadsheets from that feed you mention. See here: http://stackoverflow.com/questions/23243733/why-are-there-two-different-url-formats-for-google-spreadsheet-documents – ariestav Apr 30 '14 at 03:26
  • Maybe I'm just not understanding all this. I simply want to query against a spreadsheet of which I have the key (obtained from the URL) and it doesn't work, but it does work on older files. What do I need to do? This is a static spreadsheet I will use all the time. I have the query properly built into the URL as instructed here https://developers.google.com/chart/interactive/docs/querylanguage. – user2029890 Apr 30 '14 at 03:42
  • Not sure what to tell you. I think something is going on with google's api. I have run a number of tests, including a command line test with cURL. Newly created sheets in my account are apparently deleted and not available, while older ones are. Hopefully google will respond? – ariestav Apr 30 '14 at 03:58
  • @ariestav just an idea - there is a good chance google apps script uses the drive-sdk to get IDs and so maybe there is a drive-sdk method that will provide a valid spreadsheet/GData style ID – eddyparkinson Apr 30 '14 at 04:13
  • For my issue, it definitely is a clear Google bug as its related to the target queries not working for the new style spreadsheets. See https://code.google.com/p/google-visualization-api-issues/issues/detail?id=1476 – user2029890 Apr 30 '14 at 05:12

2 Answers2

3

My app is using OAuth 2.0 and I ran into the same error with new Google Sheets. What fixed that was making a change in scope param sent during OAuth's authorize call and then reauthorising (reinitiating OAuth flow and obtaining new tokens).

Until now scope in my app was just:

https://spreadsheets.google.com/feeds

Updated scope and solution to the issue in my case:

https://spreadsheets.google.com/feeds https://docs.google.com/feeds
andruszkowski
  • 46
  • 2
  • thank you! it worked perfectly, but google should update their documentation, it's pretty ridiculous to have a service as extensive as Sheets without documenting this change. – ariestav Apr 30 '14 at 12:08
  • Didn't work for me, I had to manually share spreadsheets with user with service account email address like 109XXXXXXXX71-XXX@developer.gserviceaccount.com to make a sheet visible for the API. What's the deal with this? Do I need to share each sheet with the service account so service account could access it? – ZurabWeb Nov 24 '14 at 16:26
1

I'm running into this in my own stuff. At least for what I'm running into, it seems to be an issue with New Sheets. I'm sorry to not have more of a solution (I'm still trying to find out what to fix on my end) but this may help you narrow down the issue.

bgrambo
  • 43
  • 6
  • Interesting, yes. I've noticed it for new sheets, but also old sheets that are newly shared with another account. – ariestav Apr 30 '14 at 01:31
  • I can confirm I am seeing this for newly created sheets. If you get to the bottom of it, please post your solution :) – ariestav Apr 30 '14 at 02:34
  • @ariestav andruszkowski's solution below worked for me. Bizarre that it's necessary, but it _does_ work. – bgrambo Apr 30 '14 at 10:31