CODEWITHSUNDEEP
google-apps-script
Bigmacbook
Bigmacbook

Reputation: 105

Parsing XML Data that I receive from UrlFetch

I want to parse the data I get from UrlFetch into a spreadsheet, but all I'm getting is undefined can someone show me what i'm doing wrong

The xml is at the address https://dl.dropbox.com/u/11787731/Minecraft/bans.xml

function runevery15mins() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MC Bans");
 sheet.clearContents();
 var banURL = "https://dl.dropbox.com/u/11787731/Minecraft/bans.xml";

 var banXML = UrlFetchApp.fetch(banURL).getContentText();
 var banDOC = Xml.parse(banXML, false);
 var mcuser = banDOC.bans;

 var x = 0;
 for(var c=0; c>mcuser.length;c++){
   var name = mcuser.getElement("username")[c].getText();
   var date = mcuser.getElement("date")[c].getText();
   var reason = mcuser.getElement("reason")[c].getText();
   var duration = mcuser.getElement("duration")[c].getText();



 } 
 sheet.appendRow([name, date, reason, duration]);
}

Upvotes: 3

Views: 4100

Answers (2)

propjk007
propjk007

Reputation: 695

You were almost there.

Looks like there was another array you needed to drill down into. Also, your call back to the spreadsheet should be in the loop. Try this:

...
var mcuser = banDOC.bans.user;

for(var i in mcuser){
   var name = mcuser[i].getElement("username").getText();
   var date = mcuser[i].getElement("date").getText();
   var reason = mcuser[i].getElement("reason").getText();
   var duration = mcuser[i].getElement("duration").getText();
   sheet.appendRow([name, date, reason, duration])
 }

Upvotes: 1

Juan Pablo Gonzalez
Juan Pablo Gonzalez

Reputation: 103

You have some small errors in your code.

For example, the second argument in the for loop needs to be c<mcuser.length.

Using the Xml service documentation, this worked for me

function runevery15mins() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MC Bans");
  sheet.clearContents();
  var banURL = "https://dl.dropbox.com/u/11787731/Minecraft/bans.xml";

  var banXML = UrlFetchApp.fetch(banURL).getContentText();
  var banDOC = Xml.parse(banXML, false);
  // Get all the child nodes from the document element that are 'user' nodes
  var mcusers = banDOC.getElement().getElements('user');

  for(var c=0; c<mcusers.length;c++){
    var user = mcusers[c];
    var name = user.getElement('username').getText();
    var date = user.getElement('date').getText();
    var reason = user.getElement('reason').getText();
    var duration = user.getElement('duration').getText();

    sheet.appendRow([name, date, reason, duration]);
 } 
}

Note for example that the sheet.appendRow line is INSIDE the loop, not outside as you had it before. I also deleted the X variable, since I didn't see any purpose for it.

I also created a user variable, which is an XmlElement, to make it easier to understand how to get the different contents of each node.

Upvotes: 3

Related Questions