Question 277580 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

parse json information in google script with no recurring first tag

In google sheet, I’ve got a script to retrieve JSon from an url

function getTeamData() {
  var DATA_SPREADSHEET_ID = "xxxx";
  var ss = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
  var TeamDataSheet = ss.getSheetByName("rawTeam");
  TeamDataSheet.clearContents();

  var url = 'xxxx';

  var response = UrlFetchApp.fetch(url);

  var content = response.getContentText();
  var data = JSON.parse(content);
  //Logger.log(JSON.stringify(data));

The problem I’ve got is that the JSon content look like this

{
    "03wi4": {
        "statsIncId": 5546,
        "rotowireId": 3795,
        "name": "Saunders, Wesley",
        "fantraxId": "03wi4",
        "team": "NY",
        "position": "SG"
    },
    "059gh": {
        "statsIncId": 6321,
        "rotowireId": 4919,
        "name": "Booth, Phil",
        "fantraxId": "059gh",
        "team": "(N/A)",
        "position": "PG"
    },
    "02nfe": {
        "statsIncId": 5020,
        "rotowireId": 3308,
        "sportRadarId": "c555e067-c4d5-43f6-99af-716b6005cbba",
        "name": "Henson, John",
        "fantraxId": "02nfe",
        "team": "NY",
        "position": "PF"
    },
    "033b7": {
        "statsIncId": 5235,
        "rotowireId": 3538,
        "name": "Raduljica, Miroslav",
        "fantraxId": "033b7",
        "team": "(N/A)",
        "position": "C"
    },
...
}

I would like to be able to return the team for each of the entries but I don’t have a common first tag name…
Could you please help, the result should look like this :

059gh    (N/A)
02nfe    NY
033b7    (N/A)```
Tags:

2

Answers


  1. 0

    I believe your goal is as follows.

    • You want to retrieve the values like [["03wi4","NY"],["059gh","(N/A)"],["02nfe","NY"],["033b7","(N/A)"]] from your showing JSON object.
    • Your showing JSON object is data of var data = JSON.parse(content);.

    In this case, how about the following modification?

    Modified script:

    function getTeamData() {
  var DATA_SPREADSHEET_ID = "xxxx";
  var ss = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
  var TeamDataSheet = ss.getSheetByName("rawTeam");
  TeamDataSheet.clearContents();

  var url = 'xxxx';
  var response = UrlFetchApp.fetch(url);
  var content = response.getContentText();
  var data = JSON.parse(content);

  // I added the below script.
  var res = Object.entries(data).map(([k, { team }]) => [k, team]);
  TeamDataSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

    • When your previous question is considered, please modify the above script as follows.

      • From

          TeamDataSheet.getRange(1, 1, res.length, res[0].length).setValues(res);

      • To

          TeamDataSheet.getRange(1, 1, res.length, res[0].length).setNumberFormat("@").setValues(res);

    • Or,

      • From

          var res = Object.entries(data).map(([k, { team }]) => [k, team]);
  TeamDataSheet.getRange(1, 1, res.length, res[0].length).setValues(res);

      • To

          var res = Object.entries(data).map(([k, { team }]) => [`'${k}`, team]);
  TeamDataSheet.getRange(1, 1, res.length, res[0].length).setValues(res);

    Note:

    • In this modification, it supposes that the value of data is your showing JSON object. Please be careful about this.

    References:

    Login or Signup to reply.
  2. 0

    Use Object.keys to iterate and Array.map :

    /*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/ 
const json = {
"03wi4": {
    "statsIncId": 5546,
    "rotowireId": 3795,
    "name": "Saunders, Wesley",
    "fantraxId": "03wi4",
    "team": "NY",
    "position": "SG"
},
"059gh": {
    "statsIncId": 6321,
    "rotowireId": 4919,
    "name": "Booth, Phil",
    "fantraxId": "059gh",
    "team": "(N/A)",
    "position": "PG"
},
"02nfe": {
    "statsIncId": 5020,
    "rotowireId": 3308,
    "sportRadarId": "c555e067-c4d5-43f6-99af-716b6005cbba",
    "name": "Henson, John",
    "fantraxId": "02nfe",
    "team": "NY",
    "position": "PF"
},
"033b7": {
    "statsIncId": 5235,
    "rotowireId": 3538,
    "name": "Raduljica, Miroslav",
    "fantraxId": "033b7",
    "team": "(N/A)",
    "position": "C"
},
}
const values = Object.keys(json).map(k => [/*prepend ' for maintaining text format in sheets*/"'"+k, json[k].team])
console.table(values)
    <!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search