8

My app accesses private Google spreadsheet documents on behalf of an authorized user. It seems that Google's API expects developers to first request a list of all the spreadsheet documents available to an authorized user before they can get at a particular spreadsheet's keys. I wanted to find a workaround to this, and eventually did by extracting the key parameter value from URLs spreadsheet URLs that look like this:

https://docs.google.com/spreadsheet/ccc?key={some long key here}&usp=drive_web#gid=0

It was simple enough to just break down the string to point where I could retrieve key's value fairly easy without the need of a regex.

Recently, though I don't know how recent, I notice URLs to newly created Google Drive spreadsheets come in this form:

https://docs.google.com/spreadsheets/d/{some long key here}/edit#gid=0

I was also able to extract the key from this URL string, but am just curious about the difference between the two URLs:

  1. What is the significance between the two URLs.
  2. Why does Google's API force devs to first get a list of all available docs, when a dev might just want to extract a key from a direct URL to a Google Drive spreadsheet doc.

Thanks!

ariestav
  • 2,799
  • 4
  • 28
  • 56

3 Answers3

9

Old style sheets

They work online only and limited to about 400,000 cells per spreadsheet.

Old style URL

 https://docs.google.com/spreadsheet/ccc?key={some long key here}&usp=drive_web#gid=0

New style sheets

Released about mid Dec 2013

Works offline and (if I remember) up to 2,000,000 cells per spreadsheet.

https://docs.google.com/spreadsheets/d/{some long key here}/edit#gid=0

Spreadsheet KEY

I get the key using Google-apps-script, as described here: Get the spreadsheet key that is in the URL. Not ss.getId()

Community
  • 1
  • 1
eddyparkinson
  • 3,680
  • 4
  • 26
  • 52
  • Thank you for clarifying the signifigance b/w the two URLs. In the answer of your referenced SO thread, the author is making a new document with the code, so of course the id is retrievable. However, what if all you have is the URL itself? Apparently there is no best practice to extract a key from the URL? I'm using plain vanilla javascript in my app. – ariestav Apr 25 '14 at 12:53
  • Another way would be to just list all the users spreadsheets. List the spreadsheets by name. .... But I agree with Cheryl Simon, extracting the ID from the URL is a messy hack and hard to maintain as a solution. – eddyparkinson Apr 30 '14 at 02:28
  • Yes, I ended up implementing it like that, but now am running into an issue when retrieving the worksheets. When I make a request to get the worksheets Google's API will tell me that the file has been deleted even when it hasn't. Apparently it's a known issue? Here's a question I started as a result of implementing Cheryl's method: http://stackoverflow.com/questions/23377824/google-drive-api-says-some-files-do-not-exist-when-in-fact-they-do – ariestav Apr 30 '14 at 02:37
0

Where are you getting the URL from? You shouldn't rely on specific URL formats, these are subject to change and not intended to be reliable. You should be able get just the id by specifying the "fields" parameter in your request. See https://developers.google.com/drive/v2/reference/files/list

Cheryl Simon
  • 46,552
  • 15
  • 93
  • 82
  • 1
    I'm getting the URL from the web browser's address bar. I do not want users to have to dig through all of their files to get at a single spreadsheet's data. I want them to just paste a URL into my app's UI. Seems like it's the most direct way to go about it. Again my questions: what is the significance between the two formats? why are we forced to obtain spreadsheet data only after retreiving a list of docs? – ariestav Apr 24 '14 at 23:56
  • If you are operating on a single spreadsheet, it sounds like you should maybe be using appscript and the new add-ons to directly tie into the spreadsheet. See https://developers.google.com/apps-script/reference/spreadsheet/ – Cheryl Simon Apr 25 '14 at 15:20
  • I'm not sure I understand your suggestion. I understand that the app scripts are executed on Google's servers. Is it possible to call an app script from my javascript running on in a "desktop" app that has been Authorized? Can I make that call through some type of API endpoint? – ariestav Apr 25 '14 at 15:58
  • I don't know what you are trying to accomplish, so its hard to say what the right solution is here. As I said though, you should *not* depend on specific URL formats, so those are subject to change without notice. If you don't care about your app being randomly broken if it changes, then go for it. Better options include: using one of the provided file pickers to have the user select a file, using the listing capability to give the user some to select from, using appscript to integrate a menu within the spreadsheet app. – Cheryl Simon Apr 25 '14 at 16:41
  • Thanks, I'll look into changing the UI of my app so users search to pick the doc instead of pasting a URL into a field. – ariestav Apr 25 '14 at 19:34
0

Cloudward has solved this through Cloud Snippets. Here's two that may be of help, there are lots of others to explore as well.

Publish Simple List from Google Sheet: https://snippets.cloudward.com/app_listing.espx?template_id=0d367025e8b5f402cd510905cade1d29&account_id=&cat_id=c478885bb325028151eaa9060422c67f

Publish Google Doc by ID: https://snippets.cloudward.com/app_listing.espx?template_id=51925e7ed2166d7d83a8c32fa1ee88dd&account_id=

Hope this helps.

Bob

Thordarson
  • 1