CODEWITHSUNDEEP
javascriptxmlparsinggoogle-apps-script
Munkey
Munkey

Reputation: 956

Parsing XML in Google Scripts to a google sheet

I'm trying to import data from a server, XML format via the server API, which require's a login.

Using information on this question: Cheers MogsDad

I can successful get the external xml file and data shows in the logger.

I cannot for the life of me write any of the info or elements to my spreadsheet. In the link shared, @mogsdad has linked to a parsing XML site. Unfortunately the link is dead. The current code returns an XML file. Normally I would try to use the importxml formula but not had much luck. Have taken out my coding attempts to parse the XML so code doesn't look awful

has anyone got any pointers on how to parse some of all of the file or know a working URL for the XML parsing doc?

Here is my code so far. Thanks in advance

    function importFromXml(){
  var url = 'URL HERE'; // Advance search for macs not encrypted.
  var username = 'USER HERE';
  var password = 'PASSWORD HERE';

  var headers =
  {
    Authorization : "Basic " + Utilities.base64Encode(username+':'+password)
  }

  var options =
  {
    "method" : "get",
    "headers": headers
  };

   var headers =
  {
    Authorization : "Basic " + Utilities.base64Encode(username+':'+password)
  }

  var options =
  {
    "method" : "get",
    "headers": headers
  };

  // Getting "bad request" here - check the username & password
  var result = UrlFetchApp.fetch(url, options);
  var state=result.getContentText(); 

  // You should check state.getResponseCode()

  Logger.log('1: '+state);
  Logger.log(parse(state));
}

function parse(txt) {
  var doc = Xml.parse(txt, true);
  return doc;                            // Return results
}

**** EDIT ****

After a bit more playing, I have some progress.

    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NoFirevault");
var range = ss.getRange(1, 1);

range.setValue(state);

I managed to write the XML contents to my sheet. Albeit in one cell. When I try to split the data into cells, using the data length and use setValues. It bums out on me, will keep on playing.

**** EDIT *****

After a bit more playing around. I can get XML data written to sheet. There's 31 entries, with various attributes. But these all get written to a single cell per entry.

Which is an improvement on ALL 31 entries going to a single cell.

In case it helps, here is the XML layout I'm looking at. I want the computer data, in the computers section.

XML Structure

function importFromJamf(){
  var url = 'URL HERE'; // Advance search for macs not encrypted.
  var username = 'USER HERE';
  var password = 'Password';

  var headers =
  {
    Authorization : "Basic " + Utilities.base64Encode(username+':'+password)
  }

  var options =
  {
    "method" : "get",
    "headers": headers
  };

   var headers =
  {
    Authorization : "Basic " + Utilities.base64Encode(username+':'+password)
  }

  var options =
  {
    "method" : "get",
    "headers": headers
  };


  var result = UrlFetchApp.fetch(url, options);
  var state = result.getContentText(); 



var document = XmlService.parse(state);





 var entries = document.getRootElement().getChild('computers').getChildren(); // Working but values joined into one row




for (i=0;i<entries.length;i++){



var value = entries[i].getValue();


SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(i+1,1).setValue(value);

}





}

Upvotes: 0

Views: 4356

Answers (1)

Munkey
Munkey

Reputation: 956

function importFromJamf(){
  var url = 'url'; 
  var username = 'user';
  var password = 'pw';

  var headers =
  {
    Authorization : "Basic " + Utilities.base64Encode(username+':'+password)
  }

  var options =
  {
    "method" : "get",
    "headers": headers
  };




  var result = UrlFetchApp.fetch(url, options);
  var state = result.getContentText(); 



   var document = XmlService.parse(state);



var array= [];




  var entries = document.getRootElement().getChild('computers').getChildren('computer');


  for(i = 0 ; i < entries.length ; i++){


var a = entries[i].getContent(5).getValue();
var b = entries[i].getContent(8).getValue();
var c = entries[i].getContent(9).getValue();
var d = entries[i].getContent(6).getValue();
var e = entries[i].getContent(11).getValue();
var f = entries[i].getContent(12).getValue();
var g = entries[i].getContent(10).getValue();

var data = [a,b, c,d,e, f,g];
array.push(data);

  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  sheet.getRange("A2:Z").clearContent();
  var range = sheet.getRange(2,1,array.length, array[0].length);
  range.setValues(array);
}

Code above works for what I need, it allows me to grab the values I want into an array I can use to write to a sheet.

.getContent() helped me get the values of y columns of array each loop

But I'm sure there are better ways of going about it.

Upvotes: 1

Related Questions