AlexR AlexR - 3 months ago 11
Javascript Question

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

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?

Answer

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');
...
Comments