Google Apps Script: Parsing XML results in error: "Cannot find function getChildren in object"

Question

I am trying to parse the following XML document with Google Apps Script in a Google Spreadsheet. In particular I want to parse the values of the lineItems and the values of its attributes coaCode, e.g. RTLR = 50211.000000:

<ReportFinancialStatements Major="1" Minor="0" Revision="1">
      <FinancialStatements>
      <COAMap></COAMap>
         <AnnualPeriods>
         <FiscalPeriod Type="Annual" EndDate="2009-06-30" FiscalYear="2009">
            <Statement Type="INC">
                <FPHeader>
                    <PeriodLength>12</PeriodLength>
                    <Source Date="2009-09-23">ARS</Source>
                </FPHeader>
                <lineItem coaCode="SREV">50211.000000</lineItem>
                <lineItem coaCode="RTLR">50211.000000</lineItem>
                <lineItem coaCode="SCOR">12001.000000</lineItem>
            </Statement>
        </FiscalPeriod>

This is my code snippet:

function parse(txt) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var document = XmlService.parse(txt);
  var root = document.getRootElement();
  var elements = root.getChild('FinancialStatements').
         getChild('AnnualPeriods').getChildren('FiscalPeriod');  

  var columnOffset = 2;
  var rowOffset = 5;
  for (var i = 0; i < elements.length; i++) {
    var column = elements.length - i + columnOffset;
    var endDate = elements[i].getAttribute('EndDate').getValue();
    var fiscalYear = elements[i].getAttribute('FiscalYear').getValue();
    var fiscalPeriodType = elements[i].getAttribute('Type').getValue();

    // ***** SCRIPT CRASHES HERE: *****
    var values = elements[i].getChildren('Statement').getChildren('lineItem');

   for (var j = 0; j < values.length; j++) {
     var row = j + rowOffset;
     var value = values[j].getValue();
     sheet.getRange(row,column).setValue(value);
   }
  }
}

When I run the code, it crashes at the line

var values = elements[i].getChildren('Statement').getChildren('lineItem');

with the error message:

TypeError: Cannot find function getChildren in object [Element: <Statement/>],
   [Element: <Statement/>],[Element: <Statement/>].

I tried to find the error for a couple hours, but without success (I tried different variations of .getChildren combinations).

Do you have any idea on what might be wrong with my code?

wchiquito · Accepted Answer · 2013-11-08 22:08:37Z

2

For an XML like this:

<ReportFinancialStatements Major="1" Minor="0" Revision="1">
  <FinancialStatements>
    <COAMap />
    <AnnualPeriods>
      <FiscalPeriod Type="Annual" EndDate="2009-06-30" FiscalYear="2009">
        <Statement Type="INC">
          <FPHeader>
            <PeriodLength>12</PeriodLength>
            <Source Date="2009-09-23">ARS</Source>
          </FPHeader>
          <lineItem coaCode="SREV">50211.000000</lineItem>
          <lineItem coaCode="RTLR">50211.000000</lineItem>
          <lineItem coaCode="SCOR">12001.000000</lineItem>
        </Statement>
      </FiscalPeriod>
    </AnnualPeriods>
  </FinancialStatements>
</ReportFinancialStatements>

Change:

...
//var values = elements[i].getChildren('Statement').getChildren('lineItem');
var values = elements[i].getChildren('Statement')[0].getChildren('lineItem');
...

answered Nov 8, 2013 at 22:08

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AlexR Over a year ago

This works great! A question regarding the index [0]: Do I need a separate for-loop to iterate over these Statement-items? What would be an easy way to get the maximum number of the index (= the number of statements)?

wchiquito Over a year ago

@AlexR: That's right, you need to iterate over getChildren('Statement'). To get the length, use getChildren('Statement').length.

AlexR Over a year ago

Is there any way I could specify which Statement Type's children I would like to return? For example: Return only children of statements with Type = "INC".

wchiquito Over a year ago

@AlexR: I see no options to do so directly.

jinay gala · Accepted Answer · 2016-08-29 15:56:48Z

0

Alternatively, you can make the below changes :

//var values = elements[i].getChildren('Statement').getChildren('lineItem');
 var values = elements[i].getChild('Statement').getChild('lineItem');

This and the code change mentioned by @wchiquito will work when the data is present in the first "Statement" Node.

answered Aug 29, 2016 at 15:56

jinay gala

1

Collectives™ on Stack Overflow

Google Apps Script: Parsing XML results in error: "Cannot find function getChildren in object"

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related