Get JSON feed from published Google sheet

7
votes

I have thorougly researched this, found many answers on this site, but just can't get it to work. I must be missing something.

I have the following public sheet: https://docs.google.com/spreadsheets/d/11CwVCfIiJHlC-PhaT_hD8aB1q77VwecCgOuwzyuwZkY/edit?usp=sharing I paste in my key https://spreadsheets.google.com/feeds/list/11CwVCfIiJHlC-PhaT_hD8aB1q77VwecCgOuwzyuwZkY/od6/public/values?alt=json

Get an error that the document is not published.

2
jsongoogle-sheets
I just want to point out that this API will expire on April 20, 2015. The communique from Google can be found here - Google Apps Updates. You're encouraged to switch to the new Drive API. – ThisClark

2 Answers

15
votes

As agershun already pointed out, you need to publish the document. Follow these steps:

1) File menu > Publish to the web...

enter image description here

2) Click the Start Publishing button

enter image description here

3) Load your URL and you'll see it in JSON

Note: Here is my working example, the URL formed exactly like yours, except this one is published: https://spreadsheets.google.com/feeds/list/1qym45VIwM4ruaWDkqndaOBzgOMAhOsjZ7rL76wJrTkY/od6/public/values?alt=json

If after you've done all this and you receive the error Invalid query parameter value for grid_id. then refer to this post for a solution: Retrieve Google Spreadsheet Worksheet JSON

5
votes

Probably you need to publish the spreadsheet, because when I tried to open the spreadsheet with your key, Google says: 

We're sorry. This document is not published.

To fix it choose "File / Publish to the web..." menu of Google Spreadsheets and publish the sheet. Copy the url from the dialog and use it in your code.

See the working snippet below to open similar spreadsheet with Tabletop library.

var url='https://docs.google.com/spreadsheets/d/12VlQDuE1hgArpsJFBHlVbe4XN3CX5qwHvo-58ClMGzU/pubhtml';
alasql('SELECT * INTO HTML("#res",{headers:true}) FROM TABLETOP(?)',[url]);
<script src="https://cdn.rawgit.com/jsoma/tabletop/master/src/tabletop.js"></script>
<script src="http://alasql.org/console/alasql.min.js"></script>
<div id="res"></div>