How do I access a JSONP feed for a Google Apps For Your Domain spreadsheet?

Question

I can't work out how to access a google spreadsheet via JSONP when that spreadsheet is part of a Google Apps For Your Domain account.

Google provide a documented way to access google spreadsheet via JSONP that works for normal gmail.com accounts. In short:

• Create a spreadsheet
• Click on the dropdown next to "Share" and select "Publish as a web page"
• Copy and paste out the key from the URL that shows (i.e. the bit after &key=)
• Go to https://spreadsheets.google.com/feeds/list/0AmHYWnFLY1F-dG1oTHQ5SS1uUzhvTnZTSHNzMjdDaVE/od6/public/values?alt=json-in-script&callback=myCallback replacing "0AmHYWnFLY1F-dG1oTHQ5SS1uUzhvTnZTSHNzMjdDaVE" with whatever key you cut out of the url

When I try this on my own domain everything seems to work. But when I visit the URL I have constructed as above google gives the error:

The spreadsheet at this URL could not be found. Make sure that you have the right URL and that the owner of the spreadsheet hasn't deleted it.

I assume there's some permission problem or some kind of alternative url I should be using, but I can't find any documentation otherwise.

Answer 1

The data that comes back is pretty hairy, though. You can use YQL to get cleaner data:

select * from csv 
where url="https://docs.google.com/spreadsheet/pub?key=XXXX&output=csv" 

If you want to name the keys, you can add:

and columns="foo,bar,baz"

Try it here with your spreadsheet; you should get this (I named the column "task"):

cbfunc({
 "query": {
  "count": 3,
  "created": "2013-01-02T21:37:09Z",
  "lang": "en-US",
  "results": {
   "row": [
    {
     "task": "Steal underpants"
    },
    {
     "task": "..."
    },
    {
     "task": "Profit"
    }
   ]
  }
 }
});

Answer 2

Ok, after some testing I found the solution:

• you need to share it, either as Public on the web, or Anyone with the link
• and then you need to publish it

Both steps are needed to allow the JSONP call.