Fetch not outputting all data from json into Google sheet

Question

I am using fetch to output data from a json to a Google sheet which I got from here by @Tanaike and added to from here by @Rubén

function GETCONNECTIONS(url) {
  var response = UrlFetchApp.fetch(url);
  var responseText = response.getContentText();
  var responseJson = JSON.parse(responseText);
  var connectionKeys = Object.keys(responseJson.connections[0]);
 
  var data = responseJson.connections.map(e => connectionKeys.map(f => {
      return e[f] instanceof Array ? e[f].join("|") : e[f];
  })); 
  data.unshift(connectionKeys);

Data comes from surveys, this is an example of a general issue with the output I have

The json looks like (A)

 "connections": [
    {
      "Id": 1024034,
      "From": 4806,
      "To": 3817,
      "Name From": "Sian Young",
      "Name To": "Richard Iron Cloud",
      "Initial Date": "7/11/2017 20:32",
      "Last Date": "9/27/2017 17:38",
      "Type": "person-person",
      "Weight": 2,
      "Checkbox": ""
    },

Later in the file (B)

{
      "Id": 1053141,
      "From": 28264,
      "To": 28267,
      "Name From": ". Arts & Science School",
      "Name To": ". Helpline Center",
      "Initial Date": "10/05/2019 19:56",
      "Last Date": "10/05/2019 19:56",
      "Type": "organization-organization",
      "Weight": 0,
      "Tell us about the relationship between your organization and this organization": [
        "We give funds to this organization",
        "We have the same parent organization"
      ],
      "Other options": ""
    },

and later (C)

{
      "Id": 2000002,
      "From": 3824,
      "To": 28265,
      "Name From": "Adrienne Benjamin",
      "Name To": ". New Vision Foundation",
      "Initial Date": "6/28/2019 18:30",
      "Last Date": "6/28/2019 18:30",
      "Type": "person-organization",
      "Weight": 0,
      "Your personal connection to this organization": "I work there",
      "Your personal feelings about this organization": ""
    },

Only the data common to the first section (A) is being outputted (showing just the headers of the output)

Id | From | To | Name From | Name To | Initial Date | Last Date | Type | Weight Checkbox

This from section (B) is not being outputted in data

"Tell us about the relationship between your organization and this organization": [
        "We give funds to this organization",
        "We have the same parent organization"
      ],

This from section (C) is not being outputted in data

"Your personal connection to this organization": "I work there",
      "Your personal feelings about this organization": ""

I need (showing just the headers)

Id | From | To | Name From | Name To | Type | Weight | Checkbox | Tell us about the relationship between your organization and this organization | Other options | Your personal connection to this organization | Your personal feelings about this organization | I currently have no role in this workstream, but I'm interested in learning more and potentially joining it | My role in relation to this Network | Other - Optional | ig_Initial Date | ig_Last Date

Json https://script.googleusercontent.com/macros/echo?user_content_key=wdu1jsOZR1FXIfljo3adgITKSaGdmtr1XIlQod-5LkFJWKz8bKUXkpou62XfN7sJmOU2ZL5mbB-wDd1NnpkpSFeY76BiaFOWOJmA1Yb3SEsKFZqtv3DaNYcMrmhZHmUMWojr9NvTBuBLhyHCd5hHa8TJX5ydsDPljxBccW800JGJdC8wte9w6JgdINX__ZvEr9Orxvk8od_4WhINrS1UdLSF_Mvn60p3tS3F0PU0G3gjE2x5FoVILaIZk7KcgdZtoRLOZR3Hw6iE83c7Od3ZCxsVPxPhxNqP0kDfbpY946Xf1vOmg1loOnII8WbYyB8pF1RKHM0quOFuNx6rQg6xpZxZOYNDLHNpptndeU9o4Ltwxj0PG1J6Gi6JRSk24HcIK3NRq9Lx1wjBamNAgHqD0QjxXNn1u2wARfAJMFmXoJFD90btlBFN0NmArM7a8XstaiDzN7sfEhqeAd4e6zlqwKKjICN9fnW0pS3kNK_FXA-SMsrpnGMp1g&lib=MDmgpdXHxuQ1dJ2wjGRxaDhUC8xcaHA7W

How to output ALL data from the json

Thank you

question from:https://stackoverflow.com/questions/65598893/fetch-not-outputting-all-data-from-json-into-google-sheet

Answer 1

I did it a little differently but hopefully this will help you to visualize the data:

function getDataFromJSON() {
  const url="https://script.googleusercontent.com/macros/echo?user_content_key=wdu1jsOZR1FXIfljo3adgITKSaGdmtr1XIlQod-5LkFJWKz8bKUXkpou62XfN7sJmOU2ZL5mbB-wDd1NnpkpSFeY76BiaFOWOJmA1Yb3SEsKFZqtv3DaNYcMrmhZHmUMWojr9NvTBuBLhyHCd5hHa8TJX5ydsDPljxBccW800JGJdC8wte9w6JgdINX__ZvEr9Orxvk8od_4WhINrS1UdLSF_Mvn60p3tS3F0PU0G3gjE2x5FoVILaIZk7KcgdZtoRLOZR3Hw6iE83c7Od3ZCxsVPxPhxNqP0kDfbpY946Xf1vOmg1loOnII8WbYyB8pF1RKHM0quOFuNx6rQg6xpZxZOYNDLHNpptndeU9o4Ltwxj0PG1J6Gi6JRSk24HcIK3NRq9Lx1wjBamNAgHqD0QjxXNn1u2wARfAJMFmXoJFD90btlBFN0NmArM7a8XstaiDzN7sfEhqeAd4e6zlqwKKjICN9fnW0pS3kNK_FXA-SMsrpnGMp1g&lib=MDmgpdXHxuQ1dJ2wjGRxaDhUC8xcaHA7W";
  const jsn=UrlFetchApp.fetch(url).getContentText();
  const obj=JSON.parse(jsn);
  const keys=Object.keys(obj.connections[0]);
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(keys.join('<br />')), "Keys");
  var html=""; 
  obj.connections.forEach(function(c){
    keys.forEach(function(k){
      html+=Utilities.formatString('<br />%s:%s',k,c[k] instanceof Array?c[k].join("|"):c[k]);
    });
    html+='<hr>';
  }); 
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), "Connnections");
  
}

It's a little easier to visualize the data in this version:

function getDataFromJSON() {
  const url="https://script.googleusercontent.com/macros/echo?user_content_key=wdu1jsOZR1FXIfljo3adgITKSaGdmtr1XIlQod-5LkFJWKz8bKUXkpou62XfN7sJmOU2ZL5mbB-wDd1NnpkpSFeY76BiaFOWOJmA1Yb3SEsKFZqtv3DaNYcMrmhZHmUMWojr9NvTBuBLhyHCd5hHa8TJX5ydsDPljxBccW800JGJdC8wte9w6JgdINX__ZvEr9Orxvk8od_4WhINrS1UdLSF_Mvn60p3tS3F0PU0G3gjE2x5FoVILaIZk7KcgdZtoRLOZR3Hw6iE83c7Od3ZCxsVPxPhxNqP0kDfbpY946Xf1vOmg1loOnII8WbYyB8pF1RKHM0quOFuNx6rQg6xpZxZOYNDLHNpptndeU9o4Ltwxj0PG1J6Gi6JRSk24HcIK3NRq9Lx1wjBamNAgHqD0QjxXNn1u2wARfAJMFmXoJFD90btlBFN0NmArM7a8XstaiDzN7sfEhqeAd4e6zlqwKKjICN9fnW0pS3kNK_FXA-SMsrpnGMp1g&lib=MDmgpdXHxuQ1dJ2wjGRxaDhUC8xcaHA7W";
  const jsn=UrlFetchApp.fetch(url).getContentText();
  const obj=JSON.parse(jsn);
  const keys=Object.keys(obj.connections[0]);
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(keys.join('<br />')), "Keys");
  var html=""; 
  obj.connections.forEach(function(c,j){
    html+=Utilities.formatString('<br />Connection: %d',j+1);
    keys.forEach(function(k,i){
      html+=Utilities.formatString('<br />%s-<strong>%s</strong>:%s',i+1,k,c[k] instanceof Array?c[k].join("|"):c[k]);
    });
    html+='<br /><hr>';
  }); 
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html).setWidth(600), "Connnections");
}

Into a Spreadsheet:

function getDataFromJSON() {
  const url="https://script.googleusercontent.com/macros/echo?user_content_key=wdu1jsOZR1FXIfljo3adgITKSaGdmtr1XIlQod-5LkFJWKz8bKUXkpou62XfN7sJmOU2ZL5mbB-wDd1NnpkpSFeY76BiaFOWOJmA1Yb3SEsKFZqtv3DaNYcMrmhZHmUMWojr9NvTBuBLhyHCd5hHa8TJX5ydsDPljxBccW800JGJdC8wte9w6JgdINX__ZvEr9Orxvk8od_4WhINrS1UdLSF_Mvn60p3tS3F0PU0G3gjE2x5FoVILaIZk7KcgdZtoRLOZR3Hw6iE83c7Od3ZCxsVPxPhxNqP0kDfbpY946Xf1vOmg1loOnII8WbYyB8pF1RKHM0quOFuNx6rQg6xpZxZOYNDLHNpptndeU9o4Ltwxj0PG1J6Gi6JRSk24HcIK3NRq9Lx1wjBamNAgHqD0QjxXNn1u2wARfAJMFmXoJFD90btlBFN0NmArM7a8XstaiDzN7sfEhqeAd4e6zlqwKKjICN9fnW0pS3kNK_FXA-SMsrpnGMp1g&lib=MDmgpdXHxuQ1dJ2wjGRxaDhUC8xcaHA7W";
  const jsn=UrlFetchApp.fetch(url).getContentText();
  const obj=JSON.parse(jsn);
  const keys=Object.keys(obj.connections[0]);
  let vA=[];
  //var html=""; 
  obj.connections.forEach(function(c,j){
    //html+=Utilities.formatString('<br />Connection: %d',j+1);
    vA[j]=[];//create array for each row
    keys.forEach(function(k,i){
      //html+=Utilities.formatString('<br />%s-<strong>%s</strong>:%s',i+1,k,c[k] instanceof Array?c[k].join("|"):c[k]);
      vA[j].push(c[k] instanceof Array?c[k].join('
'):c[k]);//push in row values
    });
    //html+='<br /><hr>';
  }); 
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html).setWidth(600), "Connnections");
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  sh.clearContents();//clear sheet
  vA.unshift(keys);//put keys in as headers
  sh.getRange(1,1,vA.length,vA[0].length).setValues(vA);//load the data
}