Wednesday, March 9, 2011

od6 and Finding Other Worksheet IDs

If you dissect the URLs that Google provides when publishing a Doc to the web, you may come across "od6".

"od6" is the worksheet-id of the 'default' sheet within your Google Spreadsheet. That is, the leftmost sheet visible within the interface.

If you have tried publishing all worksheets within the Doc, or any specific worksheet other than the default, as an ATOM or RSS feed, you would have noticed that only the first/default sheet is included in the feed.

To further complicate things, the interface that you use to publish from Google Docs grays-out the drop-down that otherwise would allow you to choose which sheet to publish when ATOM or RSS is selected as your output format. I find this very confusing, don't you?

To address this, you can grab the link that Google provides for your ATOM or RSS feed and edit the "0d6" part to represent the worksheet-id of the worksheet you would like to see. What do you change it to? Well, that's the hairy part. There doesn't seem to be a simple way to find that info.

To determine the worksheet IDs for your spreadsheet, you have to find the data within on of your feeds.
Do this:

Ask for your worksheet feed by going to:
https://spreadsheets.google.com/feeds/worksheets/YOUR_SPREADSHEET_ID/private/full

Review the results and look for the various nodes describing your worksheets. Within each node, you should find the actual worksheet ID for each respective sheet. Worksheet IDs always start with "od" which  will usually be followed by a number.

Use the desired worksheet ID in place of "od6" and whalla! All better!

16 comments:

  1. I cannot find an easier way to get at this information. Thanks!

    ReplyDelete
  2. Ahh. Dejligt du deler. Det var enormt brugbart.

    ReplyDelete
  3. Hello,
    I am using the below URL to access my data but am getting invalid URL msg.
    Please guide me
    https://spreadsheets.google.com/feeds/list/1PDKO3PK-ZvRmZddpqMlEdjsOhXAJngw7ajU3opY-J1M/od6/public/values?alt=json-in-script&callback=importGSS

    ReplyDelete
  4. This is by far the most useful post I could find about how to get a worksheet ID, thanks for sharing!

    ReplyDelete
  5. https://code.google.com/p/gdata-python-client/issues/detail?id=698#c6

    Hi, I was facing the same problem and using the worksheet_id as the number of sheet solved my issue.
    Sheet 2 -> worksheet_id = 2
    Sheet 3 -> worksheet_id = 3 and so on...

    ReplyDelete
  6. https://spreadsheets.google.com/feeds/list//od6/public/values?alt=json
    Alternatively for a json feed, do this! Thanks for the help

    ReplyDelete
  7. Hi, it seems that the worksheet id is no more in https://spreadsheets.google.com/feeds/worksheets/YOUR_SPREADSHEET_ID/private/full

    Do you know it there is another way to do it?

    Thank you

    ReplyDelete
    Replies
    1. You can simple use numbers 1, 2, 3 etc as worksheet IDs

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Thank you, I found the answer to my question

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete

Please email files to bla@bla.com