I am new to scripting and trying to get XML content and attributes

What I have is an api that gives json and converted it into XML:

{
    "teams": [
        {
            "id": "3692463",
            "name": "ABC",
            "color": "#f42c2c",
            "avatar": null,
            "members": [
                {
                    "user": {
                        "id": 32288100,
                        "username": "User1",
                        "email": "[email protected]",
                        "color": "#7c4dff",
                        "profilePicture": null,
                        "initials": "SS",
                        "role": 3,
                        "custom_role": null,
                        "last_active": "1659511910470",
                        "date_joined": "1658927583740",
                        "date_invited": "1658927309161"
                    },
                    "invited_by": {
                        "id": 5952953,
                        "username": "User2",
                        "color": "#02579b",
                        "email": "[email protected]",
                        "initials": "FC",
                        "profilePicture": null
                    }
                }

What I want is to put that into the cells in google spreadsheet.

Thanks to stackoverflow community i have following code:

const clickupToken = "pk_***********************"
const clickupReqBody = { "Authorization": clickupToken }
const clickupUrl = "https://api.clickup.com/api/v2/"


function getClickupTeam() {
    let response = UrlFetchApp.fetch(clickupUrl   "team", {
        "method": "GET",
        "contentType": "application/json",
        "muteHttpExceptions": true,
        "headers": {
            "Authorization": clickupToken
        }
    }
)

var InputJSON = response;
var output = eval("OBJtoXML(" InputJSON ");")
var content = output; 

var gsheet= SpreadsheetApp.getActiveSpreadsheet();
var mainSheet = gsheet.getSheetByName("MAIN");

const document = XmlService.parse(content); //have the XML service parse the document
Logger.log(document)

const root = document.getRootElement().getChild("members"); //get the root element of the document
Logger.log(root)

const user = root.getChild('user').getChildren();
Logger.log(user)

const list = user.map((username) => [
    username.getAttribute('id').getValue(),
    ...username
      .getChildren()
      .filter((field) =>
       ['username','email', 'role', 'last_active','date_joined','date_invited'].includes(field.getAttribute('id').getValue())
      )
      .map((field) => field.getValue()),
  ]);

var len = list.length;

mainSheet.getRange(2,1,9000,9).clearContent();
mainSheet.getRange(2,1,len,9).setValues(list);

console.log(output);
}

function OBJtoXML(obj) {
  var xml = '';
  for (var prop in obj) {
    xml  = obj[prop] instanceof Array ? '' : "<"   prop   ">";
    if (obj[prop] instanceof Array) {
      for (var array in obj[prop]) {
        xml  = "<"   prop   ">";
        xml  = OBJtoXML(new Object(obj[prop][array]));
        xml  = "</"   prop   ">";
      }
    } else if (typeof obj[prop] == "object") {
      xml  = OBJtoXML(new Object(obj[prop]));
    } else {
      xml  = obj[prop];
    }
    xml  = obj[prop] instanceof Array ? '' : "</"   prop   ">";
  }
  var xml = xml.replace(/<\/?[0-9]{1,}>/g, '');
  return xml
}

The above code gives TypeError: Cannot read property 'getValue' of null at

const list = user.map((username) => [
        username.getAttribute('id').getValue(),

Did I make a mistake in the script? Is there an easier way to put all the json result into google sheet? Is there an example where I can look at to learn about what I'm trying to do?

Thank you very much.

CodePudding user response：

Try, using JSON.parse

function myFunction() {
  const content = `{
   "teams":[
      {
         "id":"3692463",
         "name":"ABC",
         "color":"#f42c2c",
         "avatar":null,
         "members":[
            {
               "user":{
                  "id":32288100,
                  "username":"User1",
                  "email":"[email protected]",
                  "color":"#7c4dff",
                  "profilePicture":null,
                  "initials":"SS",
                  "role":3,
                  "custom_role":null,
                  "last_active":"1659511910470",
                  "date_joined":"1658927583740",
                  "date_invited":"1658927309161"
               },
               "invited_by":{
                  "id":5952953,
                  "username":"User2",
                  "color":"#02579b",
                  "email":"[email protected]",
                  "initials":"FC",
                  "profilePicture":null
               }
            }
         ]
      }
   ]
}`
  const json = JSON.parse(content)
  let list = []
  json.teams.forEach(t => t.members.forEach(m => list.push(
    [m.user['id'],
    m.user['username'],
    m.user['email'],
    m.user['role'],
    m.user['last_active'],
    m.user['date_joined'],
    m.user['date_invited']]
  )))
  var len = list.length;
  var gsheet = SpreadsheetApp.getActiveSpreadsheet();
  var mainSheet = gsheet.getSheetByName("MAIN");
  mainSheet.getRange(2, 1, 9000, 7).clearContent();
  mainSheet.getRange(2, 1, len, 7).setValues(list);
}