google spreadsheet feeds with multiple worksheets

Question

How to get feeds from multiple sheets using google spreadsheet feeds api? Below URL fetches the feeds from first sheet only. In my spreadsheet I have 3 work sheets and I want to fetch remaining sheets data as well.

https://spreadsheets.google.com/feeds/list/XXXXMYKEYXXXX/od6/public/values?alt=json-in-script

How to fetch them?

I tried below without success:

https://spreadsheets.google.com/feeds/list/XXXXMYKEYXXXX/od7/public/values?alt=json-in-script

Note od7 instead of od6

UPDATE URL for the spreadsheet feed is https://spreadsheets.google.com/feeds/list/1c53H0piyduOV6zRwS54A7MDZ3SooJOmj4O52Xd0dyIA/od6/public/values?alt=json-in-script

In the spreadsheet I have TopicA,TopicB,TopicC sheets. Feeds response contains TopicA information only.

Answer 1

It seems worksheet IDs are numbered from 1 to n and not like od6, od7. Using below urls, I was able to get individual worksheet's data
https://spreadsheets.google.com/feeds/list/1c53H0piyduOV6zRwS54A7MDZ3SooJOmj4O52Xd0dyIA/1/public/values?alt=json fetches first worksheet
https://spreadsheets.google.com/feeds/list/1c53H0piyduOV6zRwS54A7MDZ3SooJOmj4O52Xd0dyIA/2/public/values?alt=json 2nd sheet
https://spreadsheets.google.com/feeds/list/1c53H0piyduOV6zRwS54A7MDZ3SooJOmj4O52Xd0dyIA/3/public/values?alt=json 3rd sheet and so on

Note the /1, /2 and /3 after the key (1c53H0piyduOV6zRwS54A7MDZ3SooJOmj4O52Xd0dyIA) in the url

Answer 2

Per the Google Spreadsheets API:

To determine the URL of a given spreadsheet's worksheets feed, find that spreadsheet's entry in the spreadsheets feed, as described in the previous section. Then examine the link element that has rel="http://schemas.google.com/spreadsheets/2006#tablesfeed". That element's href value provides the URL for that spreadsheet's worksheets feed.

To request a list of worksheets in a given spreadsheet, issue a GET request to that URL, with an appropriate authorization header:

GET https://spreadsheets.google.com/feeds/worksheets/key/private/full

The returned result contains a URL for each worksheet in that spreadsheet, which you can then query to pull information from each worksheet you want to get data from. So in your example, od6 is a valid worksheetId, but you must discern the other worksheetIds from the spreadsheet's feed.

Answer 3

Building on suman j's answer, here's a way to load multiple sheets, regardless of how many there are, using jQuery.

Load the spreadsheet's main JSON feed, find the url for each sheet in the JSON that's returned, tweak the url a bit, then load that one as well. Then use ajaxStop to take any action that needs to happen after they all finish loading.

$(function(){
    var feedurl_start = "https://spreadsheets.google.com/feeds/";
    var feedkey = "1c53H0piyduOV6zRwS54A7MDZ3SooJOmj4O52Xd0dyIA";

    $.getJSON(feedurl_start+"worksheets/"+feedkey+"/public/basic?alt=json", function(sheetfeed) {
        $.each(sheetfeed.feed.entry, function(k_sheet, v_sheet){
            var sheeturl = v_sheet.link[0]["href"]+"?alt=json";
            sheeturl = sheeturl.replace("basic", "values");
            var sheet_title = v_sheet.content["$t"]; // to know which sheet you're dealing with
            console.log(sheet_title);
            $.getJSON(sheeturl, function(sheetjson){
                console.log(sheetjson);
            });
        });
    });
});

// Since you're making multiple AJAX calls, use ajaxStop
// to do whatever you need to do AFTER everything is loaded
$( document ).ajaxStop(function() {
    // now all your data is loaded, so you can use it here.
});