Google App Script; how to push data from nested JSON into array then to sheets

Question

Edit 18th Aug; I have updated my script below to include the additions that allowed it to grab the nested arrays within the JSON response.

I cannot figure out how to extract data in non-null format from nested JSON arrays. My script executes and pulls the top-level data, but as soon as I get into a nested array it logs null and doesn't show anything in the push or append.

My overall goal is to loop through a list of URLs from a sheet, send an API request, receive a response and log the information in another sheet. I am able to do everything successfully except obtain the data from the nested arrays in the JSON response.

I have been trying to solve this for a few days but I am a noob at parsing JSON results into arrays and haven't been able to identify a solution. Any tips would be greatly appreciated! Thanks a ton.

An example of one of the JSON files; they are all identical in format.

{
  "kind": "youtube#channelListResponse",
  "etag": "MjhfUO2Z_x1Njr9Rw7uDjA1-bvM",
  "pageInfo": {
    "totalResults": 1,
    "resultsPerPage": 5
  },
  "items": [
    {
      "kind": "youtube#channel",
      "etag": "uHgaADZBzVjfmmqUFEHIy5RFmIk",
      "id": "UCuKkFu9WVxCRoj2EbWzIj3Q",
      "snippet": {
        "title": "AhnaldT101",
        "description": "Just a guy who loves to play anything Star Wars while having fun making other sorts of content that is sure to make you laugh and put a smile on your face!",
        "customUrl": "ahnaldt101",
        "publishedAt": "2012-12-09T04:34:18Z",
        "thumbnails": {
          "default": {
            "url": "https://yt3.ggpht.com/ytc/AKedOLTk3v99OfR76ONLecJpy80h4qaDQ2m9RGYRFPdgww=s88-c-k-c0x00ffffff-no-rj",
            "width": 88,
            "height": 88
          },
          "medium": {
            "url": "https://yt3.ggpht.com/ytc/AKedOLTk3v99OfR76ONLecJpy80h4qaDQ2m9RGYRFPdgww=s240-c-k-c0x00ffffff-no-rj",
            "width": 240,
            "height": 240
          },
          "high": {
            "url": "https://yt3.ggpht.com/ytc/AKedOLTk3v99OfR76ONLecJpy80h4qaDQ2m9RGYRFPdgww=s800-c-k-c0x00ffffff-no-rj",
            "width": 800,
            "height": 800
          }
        },
        "localized": {
          "title": "AhnaldT101",
          "description": "Just a guy who loves to play anything Star Wars while having fun making other sorts of content that is sure to make you laugh and put a smile on your face!"
        },
        "country": "US"
      },
      "contentDetails": {
        "relatedPlaylists": {
          "likes": "",
          "favorites": "",
          "uploads": "UUuKkFu9WVxCRoj2EbWzIj3Q"
        }
      },
      "brandingSettings": {
        "channel": {
          "title": "AhnaldT101",
          "description": "Just a guy who loves to play anything Star Wars while having fun making other sorts of content that is sure to make you laugh and put a smile on your face!",
          "showRelatedChannels": true,
          "showBrowseView": true,
          "unsubscribedTrailer": "9hXIxPXngCo",
          "country": "US"
        },
        "image": {
          "bannerExternalUrl": "https://lh3.googleusercontent.com/I9Ffei-ZhVZ116pR61k_kP40J2OqlUx6LmToadolqzZ9vaPs7j9a-y0Jdr2LMOyKUCjQgV-cJw"
        }
      }
    }
  ]
}

My script so far;

function listYTChannels() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("YTChannel");
  const otherSheet = SpreadsheetApp.getActive().getSheetByName('YTChannelResults')
  var sheetLR = sheet.getLastRow();
  var data = sheet.getRange(1,2,sheetLR).getValues();


  data.forEach(function (row,index) {
    Logger.log(row, index);
    var response = UrlFetchApp.fetch(row);
    Logger.log(response.getContentText());
    var responseParse = JSON.parse(response.getContentText());
    Logger.log(responseParse);

    var responseData=[];  // this is an empty array to hold the data from responseParse
    var date = new Date(); // create new date for timestamp
    responseData.push(date); // this will use the timestamp created above
    responseData.push(responseParse.items[0].id);  // this works, follow this format
    responseData.push(responseParse.items[0].snippet.title);
    responseData.push(responseParse.items[0].snippet.description); 
    responseData.push(responseParse.items[0].snippet.customUrl);
    responseData.push(responseParse.items[0].snippet.publishedAt);
    responseData.push(responseParse.items[0].snippet.thumbnails.high.url);
    responseData.push(responseParse.items[0].snippet.thumbnails.high.width);
    responseData.push(responseParse.items[0].snippet.thumbnails.high.height);
    responseData.push(responseParse.items[0].contentDetails.relatedPlaylists.uploads);
    responseData.push(responseParse.items[0].brandingSettings.channel.title);

    Logger.log(responseData);
    otherSheet.appendRow(responseData);  // no issues here

  });

}

It's just a sheet with an amount of rows added based on the amount of queries ran - but I did not specify the columns I wanted. I will update my original post shortly with the final script and output results. I believe I have the solution I was looking for in the below comments. — mattuscainsgfrnc
– mattuscainsgfrnc, Commented Aug 18, 2021 at 6:17

Mike Steelson · Accepted Answer · 2021-08-18 04:23:05Z

2

The easiest way to explore a full json, especially when it's fully nested, is:

//Mike Steelson
let result = []; 
function getAllDataJSON(url) {
  if (url.match(/^http/)){var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())}
  else{var data = JSON.parse(url)}
  getAllData(1,eval(data),'data')
  return result
}
function getAllData(niv,obj,id) {
  const regex = new RegExp('[^0-9]+');
  for (let p in obj) {
    var newid = (regex.test(p)) ? id + '.' + p : id + '[' + p + ']';
    if (obj[p]!=null){
      if (typeof obj[p] != 'object' && typeof obj[p] != 'function'){
        result.push([niv, (newid), p, obj[p]]);
      }
      if (typeof obj[p] == 'object') {
        if (obj[p].length){
          result.push([niv, (newid), p + '[0-' +(obj[p].length-1)+ ']', 'tableau']);
        }else{
          //result.push([niv, (newid), p, 'parent']);
        }
        niv+=1;
        getAllData(niv, obj[p], newid );
        niv-=1
      }
    }
  }
}

the result is an array that you can rework as you wish (by filter, query, script). https://docs.google.com/spreadsheets/d/1JuQHBAqI_Y1jI7N9XPA3yNGJEZDQ7Sf9FgVruji4Tpo/copy If you need more specific information out of your json, pls tell me.

answered Aug 18, 2021 at 4:23

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mattuscainsgfrnc Over a year ago

This is extremely useful, I am going to use this with another upcoming project and parts of it for this project. Thanks so much!

Lod Over a year ago

@Mike Steelson thanks for the great script. Just an issue with this testing data: jsonplaceholder.typicode.com/posts How to get the output as this web.archive.org/web/20240125112020/https://paste.c-net.org/… My tests so far: web.archive.org/web/20240125113605/https://paste.c-net.org/… Thanks again!

Mike Steelson · Accepted Answer · 2021-08-18 05:57:27Z

1

Try for instance, or pick out from my other answer ...

responseData.push(JSON.stringify(responseParse.items[0].snippet.description));

answered Aug 18, 2021 at 5:57

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

1 Comment

mattuscainsgfrnc Over a year ago

THANK YOU! This is the part I didn't understand... so very helpful. I am using this with your other answer below and will update my original comment with the full solution once I have finished it. Thanks again!! Very educational for me.

Collectives™ on Stack Overflow

Google App Script; how to push data from nested JSON into array then to sheets

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related