CODEWITHSUNDEEP
ajaxxmlgoogle-sheetsget
Arsenic_Gooner
Arsenic_Gooner

Reputation: 21

Extracting data from Google Sheet XML File using AJAX

I am relatively new to dealing with XML files from Google Sheets and have an XML file generated from a Google Sheet that I want to take data from and display it in a table. The XML file that Google Sheets generates displays each entry as follows:

<entry>
    <id>https://spreadsheets.google.com/feeds/list<MyID>/2/public/values/cokwr</id>
    <updated>2020-09-08T10:27:43.003Z</updated>
    <category scheme='http://schemas.google.com/spreadsheets/2006' term='http://schemas.google.com/spreadsheets/2006#list'/>
    <title type='text'>1</title>
    <content type='text'>name: Joe Bloggs, totalpoints: 0</content>
    <link rel='self' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/list/<MyID>/2/public/values/cokwr'/>
    <gsx:pos>1</gsx:pos>
    <gsx:name>Joe Bloggs</gsx:name>
    <gsx:totalpoints>0</gsx:totalpoints>
</entry>

And my html file looks like this:

<body>
  <table id = "league_data">
  <tr><th>Pos</th><th>Name</th><th>Points</th>
  </tr>
  </table>

  <script>
     $(document).ready(function(){
           $.ajax({
               type: "GET",
               url: "https://spreadsheets.google.com/feeds/list/<MyID>/2/public/values",
               dataType: "html",
               success: function(xml){
                 console.log("here");$
                   $(xml).find('entry').each(function(){
                       var Pos = $(this).find('gsx:name').text();
                       var Name = $(this).find('gsx:name').text();
                       var Points = $(this).find('gsx:totalpoints').text();
                       $('<tr></tr>').html('<th>' +Pos+ '</th><td>$' +Name+ '</td><td>$' +Points+ '</td>').appendTo('#league_data');
                   });
               }
           });
   });
  </script>

</body>

Is it possible to retrieve the data that is wrapped in the gsx:pos, gsx:name and gsx:totalpoints tags? My code does not seem to work when those tags are used. Any help would be great.

Upvotes: 0

Views: 118

Answers (1)

Alessandro
Alessandro

Reputation: 2998

Solution

You will have to parse the XML as a DOM in order to access to the tag names like that.

Here is an example for your case without JQuery:

// inside the success callback
const parser = new DOMParser();
let xmlDom = parser.parseFromString(xml, 'text/xml');
let Pos = xmlDom.getElementsByTagName('gsx:pos')[0].textContent;
let Name = xmlDom.getElementsByTagName('gsx:name')[0].textContent;
let Points = xmlDom.getElementsByTagName('gsx:totalPoints')[0].textContent;
$('<tr></tr>').html('<th>' +Pos+ '</th><td>$' +Name+ '</td><td>$' +Points+ '</td>').appendTo('#league_data');
// ...

Reference

DOMParser

Upvotes: 1

Related Questions