Google apps script array issues

Question

I am working on an apps script that loads xml data into a function and parses the data based on user input.

The xml is simple and straightforward:

<?xml version="1.0" encoding="UTF-8"?>
<Records>
    <Record>
        <username>jjohnson</username>
        <firstName>John</firstName>
        <lastName>johnson</lastName>
        <email>[email protected]</email>
    </Record>
    <Record>
        <username>bsimmons</username>
        <firstName>ben</firstName>
        <lastName>simmons</lastName>
        <email>[email protected]</email>
    </Record>
    ...etc
</Records>

The function takes the XML and loads the data into an array of objects and attempts to parse the data based on username and display the content as a row in google sheets.

function XMLCONTACT(username) {
var url = '<XML source>';
var ContactXml = UrlFetchApp.fetch(url).getContentText();
var document = XmlService.parse(ContactXml);
var root = document.getRootElement();
var arr = [];
var XMLChildren = root.getChildren("Record");

for (var i = 0; i < XMLChildren.length; i++) {    
    arr.push({ 
        username: XMLChildren[i].getChild("username").getText(),
        firstName: XMLChildren[i].getChild("firstName").getText(),
        lastName: XMLChildren[i].getChild("lastName").getText(),
        email: XMLChildren[i].getChild("email").getText()
   });
}

var newarr = []

for(var j= 0; j < arr.length; j++){
  if(arr[j].username === username ){
        newarr.push(arr[j]);
  }
}
    return newarr;
}

The problem is that when the function runs, nothing is returned. I can get sheets to display the contents of the array without objects, but not the filtered output.

Thanks for your help!

Answer 1

You want to put the returned values from XMLCONTACT() to Spreadsheet. If my understand what you want, how about this modification? From your script, I thought that you may want to use XMLCONTACT() as a custom function for Spreadsheet. So please modify as follows.

It returns the values as 2 dimensional array.

From :

var newarr = []
for(var j= 0; j < arr.length; j++){
  if(arr[j].username === username ){
    newarr.push(arr[j]);
  }
}
return newarr;

To :

var newarr = [];
for(var j= 0; j < arr.length; j++){
  if(arr[j].username === username ){
    newarr.push([arr[j].username, arr[j].firstName, arr[j].lastName, arr[j].email]);
  }
}
return newarr;

Note :

• It supposes that this script is the container-bound script of Spreadsheet.
• When you use this, for example, please put =XMLCONTACT2("jjohnson") to a cell in spreadsheet.
• If you want to change the arrangement of values, please modify newarr.push([arr[j].username, arr[j].firstName, arr[j].lastName, arr[j].email]);. If you cannot understand this, please tell me.

If I misunderstand what you want to do, please tell me. I would like to modify my answer.