How to retrieve nested JSON Data?

[Scott Holcomb]

I am attempting to retrieve data from an API with a JSON structure of nested data. I am able to gather the first tier of data and push/pull to a Google Sheets back-end, however I cannot do the same for any objects that are nested deeper. The closest code snippet I've stumbled upon is below but renders the output "undefined". I'm hoping this is just some obvious oversight in the code and any help would be greatly appreciated.

API JSON Data layout Example - 

 {

        "siteName": "XXX",

        "brand": "XXX,

        "timestamp": "2019-04-25 14:20:23",

        "h2sTrafficUtilizationMbps": 33.706497873,

        "s2hTrafficUtilizationMbps": 8.603756497,

        "h2sRequestedMbps": 72.671550363,

        "s2hRequestedMbps": 9.406809277,

        "latitude": "9.96554",

        "longitude": "76.1724",

        "heading": 266.12,

        "speedKnots": 0,

        "antennas": {

            "S Dac 2202 Ku Band": {

                "status": "Error",

                "azimuth": "-234.08",

                "elevation": "-288.34",

                "heading": "-114.51",

                "modems": [

                    "XXX Ku-9252"

                ]

            },

            "I 240M": {

                "status": "Unlocked",

                "azimuth": "98.022",

                "elevation": "29.81",

                "heading": "266.12",

                "modems": [

                    "XXX Intellian 9s C-2344",

                    "XXX Intellian Ku-34894"

                ]

            }

        },

        "modems": [

            {

                "name": "XXX Ku-9252",

                "s2hSnr": "6.3",

                "s2hLock": "In Network",

                "s2hPower": "-5.5",

                "cpuPercentUtilized": "8.4",

                "h2sCirMbps": 15,

                "s2hCirMbps": 5,

                "h2sUtilizationMbps": "0.006776383",

                "s2hUtilizationMbps": "0.004839437",

                "h2sRequestedMbps": "0.006776383",

                "s2hRequestedMbps": "0.004839437",

                "networkName": "APSTAR9_N1_IDRCT_WestV_WestV",

                "plannedPriority": "2",

                "h2sQosAllocated": [

                    {

                        "name": "Total",

                        "min_mbps": "0.005886",

                        "avg_mbps": "0.00677638",

                        "max_mbps": "0.007145"

Code Snippet being used to gather first tier of data successfully - 

  var response = UrlFetchApp.fetch(url, options);

  var data = JSON.parse(response.getContentText());

    if (!data.length) {

        return [];

    }

  var rows = [];

  var ParentHeaders = Object.keys(data[0]);

    rows.push(ParentHeaders);

    for (var i=0; i<data.length; i++) {

        var row = [];

        for (var j=0; j<ParentHeaders.length; j++) {

            row.push(data[i][ParentHeaders[j]]);

        }

        rows.push(row);

    }

//  var sheet = SpreadsheetApp.getActiveSheet();

  var sheet = SpreadsheetApp.getActive().getSheetByName('JSON_RESPONSE')

  var appendRange = sheet.getRange(sheet.getLastRow() + 1,1,rows.length,13);

      

  appendRange.clearContent();

  appendRange.setValues(rows);

 

}

function displayShipData(rows) {

  

  var output = ImportJSON(url);

  rows.forEach(function(elem,i) {

    elem.unshift(i + 1);

  });

  var len = output.length;

  sheet.getRange(15,1,102,12).clearContent();

  sheet.getRange(15,1,102,12).setValues(rows);

}

Code Snippet that should allow for nested item retrieval but pulls in "undefined" V1-

  var response = UrlFetchApp.fetch(url,options); 

  var dataAll = JSON.parse(response.getContentText());

  var dataSet = dataAll.data.modems;

  var rows = [],data;

for (var i in dataSet) {

  data = dataSet[i];

    rows.push([data.siteName, data.brand, data.timestamp, data.latitude, data.longitude, data.name, data.s2hLock]);

 

    

  };

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1')

  var range = sheet.getRange(1,1,100,7);

  range.clearContent();

  range.setValue(rows)

  } 

Code Snippet that should allow for nested item retrieval but pulls in "undefined" V2- 

  var response = UrlFetchApp.fetch(url,options);

  var dataAll = JSON.parse(response.getContentText()); //

  var dataSet = dataAll;

  Logger.log(dataSet)

  var rows = [],

      data;

  Logger.log(rows)

  for (i = 0; i < dataSet.length; i++) {

    data = dataSet[i];

    rows.push([data.siteName,data.brand,data.modems,[modems.networkName]]); 

  }

  

  var len = rows.length;

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');

  sheet.getRange(1,1,102,13).clearContent();

  var appendRange = sheet.getRange(sheet.getLastRow() + 1,1,len,3);

  

  appendRange.setValues(rows);

}

[Reno Blair]

You've set data to response.modems[i] in v1 and v2. Per your API sample, the modems object does not have the properties siteName, brand, modems, or [modems.networkName].

What is the complete result format you are trying to achieve in this conversion?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ea72ab79-24f5-46d1-a7ba-f2faeed2337c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Reno Blair

​

Educational Technology Services​

Grossmont Union High School District

619 644 8250

[Riël Notermans]

Can you post the whole JSON file?

Riël Notermans

Op woensdagen vrij • Zzapps B.V. • High Tech Campus 9, 5656 AE Eindhoven • T 040 711 41 94 • E ri...@zzapps.nl • www.zzapps.nl

WIJ MAKEN HET ONDENKBARE MOGELIJK

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAOg4DjDheZuVz_%3D5C4akKC0f_RkMjL3TiGGmwd6bWMpbkd5AcA%40mail.gmail.com.

[Michael O'Shaughnessy]

I just recently ran across this article:

https://thisdavej.com/fetching-filtering-and-sorting-json-apis-in-google-sheets-the-missing-functions/

I have not had a chance to try it out, but it looks promising.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHjvmF5dGvEL%2BKuZQRzMm0F4EZeprCNb6K05%2Bs7we3qFAZF8Ug%40mail.gmail.com.

[Faustino Rodriguez]

There is a library from Bruce cFlatten that convert any deep JSON data into an Array of JSON flattened into a single level, with keys/headers ready for a Google Sheets update

Check this link for full details, code and samples

p.s. not sure if that's what you need, but I hope it helps, as it does for me

Thanks to @Bruce Mcpherson

[Adam Morris]

I wrote Dotmitizer.gs which does this as well.

https://github.com/classroomtechtools/modularLibraries.gs/tree/master/Dotmitizer

————————————————————————————

Adam Morris | IT Systems & English Teacher | IGB International School
Jalan Sierramas Utama, Sierramas,
47000 Sungai Buloh, Selangor DE, Malaysia

t    +60 3 6145 4688
f    +60 3 6145 4600
w   www.igbis.edu.my
e    adam....@igbis.edu.my

————————————————————————————

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ad53f312-8a99-4cba-914e-3af659553458%40googlegroups.com.