Thursday, June 21, 2012

Return All Worksheet Names in a Google Spreadsheet

Digging more into working with Google spreadsheets and JSON, I started looking for a way to view the sheets in a specific spreadsheet. For the worksheet with the key "0Ak1itrncGIGldHNvZk9tUkhBWFJ5LXlhekxfcF9yTHc", the following textarea shows the names of the worksheets that are contained within.





First off, any spreadsheet that will be returning JSON data needs to have been made public.  This is not a hard process.  The instruction can be found here.

Next, the JSON call to the spreadsheet is a little different. Normally, it would be a call to "values". Instead, it is a call to "basic". Here is what the call looks like:

http://spreadsheets.google.com/feeds/worksheets/0Ak1itrncGIGldHNvZk9tUkhBWFJ5LXlhekxfcF9yTHc/public/basic?alt=json&callback=loadWorksheets

The code to generate the example above follows.

<script>
function loadWorksheets(json) 
{
   $(document).ready
   (
      function() 
      {

         var jsonText = JSON.stringify(json);
   
         var jsonOBJ = $.parseJSON(jsonText);
   
         var aHold = jsonOBJ.feed.entry;
   
         var sHold = "";
   
         for (var i = 0; i < aHold.length; i++)
         {
            sHold = $("#tSheets").val();
            $("#tSheets").val(sHold + '\n' + aHold[i].title.$t);
         }
      }
   )
}
</script>

<script src="http://spreadsheets.google.com/feeds/worksheets/0Ak1itrncGIGldHNvZk9tUkhBWFJ5LXlhekxfcF9yTHc/public/basic?alt=json&callback=loadWorksheets"></script>

<br />
<br />

<textarea name="tSheets" 
   id="tSheets" 
   rows="5" 
   cols="50"></textarea>


Next up is using this method combined with the information here to dynamically load JSON by picking a spreadsheet.

The following textarea contains the whole JSON structure that the worksheets are retrieved from.

No comments:

Post a Comment