Parse XML to Google sheets in google Apps Script-CodePudding

I need to parse a API XML data to Google sheet. I need all the data from each row.

XML File, example:

<directory>
    <fieldset>
        <field id="displayName">Display name</field>
        <field id="firstName">First name</field>
        <field id="lastName">Last name</field>
        <field id="gender">Gender</field>
        <field id="jobTitle">Job title</field>
        <field id="workPhone">Work Phone</field>
        <field id="workPhoneExtension">Work Extension</field>
        <field id="skypeUsername">Skype Username</field>
        <field id="facebook">Facebook URL</field>
    </fieldset>
    <employees>
        <employee id="123">
            <field id="displayName">John Doe</field>
            <field id="firstName">John</field>
            <field id="lastName">Doe</field>
            <field id="gender">Male</field>
            <field id="jobTitle">Customer Service Representative</field>
            <field id="workPhone">555-555-5555</field>
            <field id="workPhoneExtension"/>
            <field id="skypeUsername">JohnDoe</field>
            <field id="facebook">JohnDoeFacebook</field>
        </employee>
    </employees>
</directory>

Apps Script code that i am using:

function myFunction() {

    var url = "https://api.bamboohr.com/api/gateway.php/empgtest/v1/employees/directory";
    var apiKey = "****************************";
    var authHeader = "Basic "   Utilities.base64Encode(apiKey   ":x");
    var res = UrlFetchApp.fetch( url, { "headers":{ "TRN-Api-Key": apiKey, "Authorization": authHeader } } );
  
    if (!res.getResponseCode() === 200 ) throw new Error ('failed to get data from api '   res.getContentText());

    var type = res.getHeaders()["Content-Type"];
    var text = res.getContentText();
  
    Logger.log(text);
    var document = XmlService.parse(text); //have the XML service parse the document
  
    var root = document.getRootElement(); //get the root element of the document
    Logger.log(root);
  
    var fieldset = root.getChild("employees").getChildren("row");  
    Logger.log(fieldset);

    const list = [] //we create an array that will hold the data
    fieldset.forEach(function (row) {
                list.push([row.getAttribute("id").getValue(), row.getAttribute("displayName").getValue(), row.getAttribute("firstName").getValue(), row.getAttribute("lastName").getValue()])
                })
            Logger.log(list);
        }

After doing research and different examples i was unable to collect the values from a row and save them to google sheet.

Any help will be highly appreciated.

CodePudding user response：

Try

function myEmployees() {
  const xmlString = `
<directory>
    <fieldset>
        <field id="displayName">Display name</field>
        <field id="firstName">First name</field>
        <field id="lastName">Last name</field>
        <field id="gender">Gender</field>
        <field id="jobTitle">Job title</field>
        <field id="workPhone">Work Phone</field>
        <field id="workPhoneExtension">Work Extension</field>
        <field id="skypeUsername">Skype Username</field>
        <field id="facebook">Facebook URL</field>
    </fieldset>
    <employees>
        <employee id="123">
            <field id="displayName">John Doe</field>
            <field id="firstName">John</field>
            <field id="lastName">Doe</field>
            <field id="gender">Male</field>
            <field id="jobTitle">Customer Service Representative</field>
            <field id="workPhone">555-555-5555</field>
            <field id="workPhoneExtension"/>
            <field id="skypeUsername">JohnDoe</field>
            <field id="facebook">JohnDoeFacebook</field>
        </employee>
    </employees>
</directory>`
  var document = XmlService.parse(xmlString);
  var root = document.getRootElement();
  var headers = []
  root.getChild("fieldset").getDescendants().filter(function (c) {
    var element = c.asElement();
    try { headers.push(element.getText()); } catch (e) { }
  });
  var rows = []
  root.getChild("employees").getChild("employee").getDescendants().filter(function (c) {
    var element = c.asElement();
    try { rows.push(element.getText()); } catch (e) { }
  });
  return [headers,...[rows]]
}

CodePudding user response：

There is no row attribute. You may try it like this:

const testXml = () => {
  const xml = `<directory>
    <fieldset>
        <field id="displayName">Display name</field>
        <field id="firstName">First name</field>
        <field id="lastName">Last name</field>
        <field id="gender">Gender</field>
        <field id="jobTitle">Job title</field>
        <field id="workPhone">Work Phone</field>
        <field id="workPhoneExtension">Work Extension</field>
        <field id="skypeUsername">Skype Username</field>
        <field id="facebook">Facebook URL</field>
    </fieldset>
    <employees>
        <employee id="123">
            <field id="displayName">John Doe</field>
            <field id="firstName">John</field>
            <field id="lastName">Doe</field>
            <field id="gender">Male</field>
            <field id="jobTitle">Customer Service Representative</field>
            <field id="workPhone">555-555-5555</field>
            <field id="workPhoneExtension"/>
            <field id="skypeUsername">JohnDoe</field>
            <field id="facebook">JohnDoeFacebook</field>
        </employee>
    </employees>
</directory>`;
  const document = XmlService.parse(xml); //have the XML service parse the document
  const root = document.getRootElement(); //get the root element of the document
  const employees = root.getChild('employees').getChildren();
  const list = employees.map((employee) => [
    employee.getAttribute('id').getValue(),
    ...employee
      .getChildren()
      .filter((field) =>
       ['displayName','firstName', 'lastName'].includes(field.getAttribute('id').getValue())
      )
      .map((field) => field.getValue()),
  ]);
  console.log(list);
};

However, probably the easiest way is to add a Accept header and set it to application/json:

var res = UrlFetchApp.fetch( url, { "headers":{ "TRN-Api-Key": apiKey, "Authorization": authHeader, "Accept": "application/json" } } );

CodePudding user response：

try regex

const getFieldValue = (node, id) => { 
    const re = RegExp(`<field id="${id}">(.*)<\/field>`, 'g')
    return re.exec(node)[1]
}

not ideal way to go, but works. It's because this parsing module doesn't work in aps scripts. If you want to do this civil way just pick other solution.