Googel Apps Script - API Query - XML Parse and Loop add to rows of a Sheet?

fission 0
Viewed 25

I have a API query bringing back xml like this

  <?xml version="1.0" encoding="UTF-8"?>
  <computers>
    <size>3830</size>
    <computer>
      <id>6</id>
      <name>A user </name>
      <managed>false</managed>
      <username/>
      <model>Computer 1</model>
      <department/>
      <building/>
      <mac_address>78:4F:XXXXX</mac_address>
      <udid>A3E2C80A-XXXXX</udid>
      <serial_number>C02TGXXXX</serial_number>
      <report_date_utc>2022-04-19T13:23:00.404+0000</report_date_utc>
      <report_date_epoch>165037458XXX</report_date_epoch>
    </computer>
    <computer>
      <id>13</id>
      <name>C1MRXXXX</name>
      <managed>true</managed>
      <username>my.user</username>
      <model>Mac</model>
      <department/>
      <building/>
      <mac_address>98:01:XXXXXX</mac_address>
      <udid>A4177C40-0B6B-57CD-932A-XXXXXX</udid>
      <serial_number>C1MRV3XXXXXX</serial_number>
      <report_date_utc>2022-09-21T14:07:19.421+0000</report_date_utc>
      <report_date_epoch>1663769239421</report_date_epoch>
    </computer>

I'm trying to get some of that XML data into a Google Sheet.

The script I have works for the first set of data between <computer> </computer> and I get my selected items as a row in my Sheet :)

But how do I make it loop through all the output data and add a new row for each selection within <computer> </computer>?

This is my script.

function GetMyData() {

//Query jamf 
  var url = 'https://MYSERVER.mydomain.com/computers/subset/basic';
  var myXml = UrlFetchApp.fetch(url, {
    "method": "GET",
    "headers": {
      "Authorization": "Basic ENCODED_BASIC_CREDS_HERE",
      
      "Content-Type": "application/xml"
    },
  }).getContentText();

  var document = XmlService.parse(myXml);
  var root = document.getRootElement();

  //set variables to data from myXml
  var id = root.getChild('computer').getChild('id').getText();
  var name = root.getChild('computer').getChild('name').getText();
  var managed = root.getChild('computer').getChild('managed').getText();
  var username = root.getChild('computer').getChild('username').getText();
  var model = root.getChild('computer').getChild('model').getText();
  var serial_number = root.getChild('computer').getChild('serial_number').getText();

  // Populate sheet with variable data
  SpreadsheetApp.getActiveSheet().getRange(2,1).setValue(id);
  SpreadsheetApp.getActiveSheet().getRange(2,2).setValue(name);
  SpreadsheetApp.getActiveSheet().getRange(2,3).setValue(managed);
  SpreadsheetApp.getActiveSheet().getRange(2,4).setValue(username);  
  SpreadsheetApp.getActiveSheet().getRange(2,5).setValue(model);  
  SpreadsheetApp.getActiveSheet().getRange(2,6).setValue(serial_number);  

  // Logger items
  Logger.log(id);
  Logger.log(name);
  Logger.log(managed);
  Logger.log(username);
  Logger.log(model);
  Logger.log(serial_number);
  
}
xml loops google-apps-script
1 Answers

Here's a way to do it:

  //your query

  var document = XmlService.parse(myXml)
  var root = document.getRootElement()
  var computers = root.getChildren("computer")
  var list=[]

  computers.forEach(function(item){
    var computer=[]
    item.getChildren().forEach(function(details){
      computer.push(details.getValue())
    })
    list.push(computer)
  })

  var sheet = SpreadsheetApp.getActiveSheet()
  sheet.getRange(sheet.getLastRow()+1,1,list.length,list[0].length).setValues(list)

And the result (I added the headers manually): enter image description here

This grabs all the <computer> nodes and loops through its children to build a 2d array, then uses setValues() to write everything to the sheet at once. Google recommends using batch operations in their best practices to improve performance. Writing each value from a file that could contain thousands of rows would be very slow compared to writing them from an array. Also, the range is being selected dynamically with getLastRow() so you can just keep feeding XML files to the script and they'll be added at the end.

If you only want certain values from each <computer> node you can specify them manually and replace the second loop.

userzyDP0
Related