Parse XML to Google sheets in google Apps Script

Question

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.

expected JSON, but got response of type: text/xml; charset=UTF-8 — OZIE
– OZIE, Commented Jul 6, 2022 at 11:06
@OZIE The API gives you the option to set the content type of the response payload. Just set the Accept header to application/json. — TheAddonDepot
– TheAddonDepot, Commented Jul 6, 2022 at 12:57

Mike Steelson · Accepted Answer · 2022-07-06 20:02:24Z

Try

function myEmployees() {
  var document = XmlService.parse(xmlString);
  var root = document.getRootElement();
  var headers = []
  headers.push('id')
  root.getChild("fieldset").getDescendants().filter(dsc => dsc.asElement()).forEach(el => {
    headers.push(el.getText())
  })
  var rows = []
  root.getChild("employees").getChildren("employee").forEach(emp => {
    var prov=[]
    prov.push(emp.getAttribute('id').getValue())
    emp.getDescendants().filter(dsc => dsc.asElement()).forEach(el => {
      prov.push(el.getText())
    })
    rows.push(prov)
  })
  return ([headers, ...rows])
}
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>
        <employee id="456">
            <field id="displayName">John2 Doe</field>
            <field id="firstName">John2</field>
            <field id="lastName">Doe2</field>
            <field id="gender">Male2</field>
            <field id="jobTitle">Customer Service Representative2</field>
            <field id="workPhone">555-555-5555</field>
            <field id="workPhoneExtension"/>
            <field id="skypeUsername">JohnDoe2</field>
            <field id="facebook">JohnDoeFacebook2</field>
        </employee>
    </employees>
</directory>`

Thank You @Mike Steelson. It's working. However i need to get all the rows. It's getting single row. Much appreciated

TheMaster · Accepted Answer · 2022-07-06 13:42:05Z

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" } } );

Collectives™ on Stack Overflow

Parse XML to Google sheets in google Apps Script

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related