4

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.

suman j
  • 6,710
  • 11
  • 58
  • 109

3 Answers3

20

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

suman j
  • 6,710
  • 11
  • 58
  • 109
  • thanks for the great answer, have you come across a way to know how many sheets are in a spreadsheet? Eg at the moment my user has 2 but I want them to be able to keep adding to it, so I want to run a loop saying for each sheet give me the data. Do you know if this is possible? – ak85 Apr 24 '15 at 13:26
  • 1
    @ak85 I did not proceed with worksheet approach for my requirement. I wanted some `json` content and worksheets approach resulted in lot of bytes exchange over network for small amounts of json data. Instead I wrote a simple python application to return me the required json. Sorry this does not answer your question in the comment. I didn't explore much as the worksheet solution didn't meet my requirement – suman j Apr 25 '15 at 00:01
3

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.

Kyle Hale
  • 7,912
  • 1
  • 37
  • 58
  • Thanks for the response. I do not see the other worksheets information in the feeds. I updated the question with actual URL which you can hit to verify the feed response. – suman j Jul 09 '14 at 22:36
  • That URL is not the spreadsheet feed, that is the list feed for a specific worksheet, TopicA. – Kyle Hale Jul 10 '14 at 04:36
  • Here is the URL for the spreadsheet feed https://spreadsheets.google.com/feeds/worksheets/1c53H0piyduOV6zRwS54A7MDZ3SooJOmj4O52Xd0dyIA/public/full – Kyle Hale Jul 10 '14 at 04:39
  • I am sorry. I did not understand `Your other worksheetids are in that url's response`. URL you mentioned lists the worksheet names. I need the data in those worksheets like the list api response. the Url I mentioned gives me the data from worksheet-1. – suman j Jul 10 '14 at 04:53
  • In the URL I sent you are some *id* elements. Each of those corresponds to a worksheet feed URL which you can use to get the list feed URLs. – Kyle Hale Jul 10 '14 at 15:23
  • 1
    How would you return your suggestion as json? Answer: just append ?alt=json – IrishGringo Dec 19 '16 at 16:16
1

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.
});
mattgilbertnet
  • 13
  • 4