CODEWITHSUNDEEP
jsongoogle-apps-scriptgoogle-sheets
Thaw-Zin
Thaw-Zin

Reputation: 3

How to Import into Google Sheet from API response with nested JSON objects

I am trying to import from API data into Google Spreadsheet.

API Response Code is like below:

{
  "2020-05-05": {
"downloads": 43,
"re_downloads": 8,
"uninstalls": 18,
"updates": 192,
"returns": 0,
"net_downloads": 43,
"date": "2020-05-05"
  },
  "2020-05-06": {
"downloads": 45,
"re_downloads": 7,
"uninstalls": 0,
"updates": 196,
"returns": 0,
"net_downloads": 45,
"date": "2020-05-06"
  },
}

I am using this ImportJSON.gs Script

My problem is that when I am trying to call with query parameter object key for "2020-05-05" like: =ImportJSONBasicAuth("https://api.appfigures.com/v2/sales/dates/-7/0/?client_key=3120ef28b3dc4967ad602e81411a89d2","my_username","my_password","/2020-05-05")

I got the right format that I wanted in Google Spreadsheet for "2020-05-05" date object. enter image description here

But, When I try to get all date object keys by adding rows by date, like without date field parameter, =ImportJSONBasicAuth("https://api.appfigures.com/v2/sales/dates/-7/0/?client_key=3120ef28b3dc4967ad602e81411a89d2","my_username","my_password"),

It is showing all object fields by adding columns in Google Sheet: enter image description here

I want all date objects fields by adding rows in Google Sheet. As I am not script writer, I don't know much more where need to update script ing Script Editor. Anyone can help me to show all object dates by adding rows by its specific date? Any suggestion or what need to chage in script editor? Thanks in advance.

Upvotes: 0

Views: 422

Answers (1)

Cooper
Cooper

Reputation: 64140

Is this what your looking for?

function myFunction() {
  const js='{"2020-05-05": {"downloads": 43,"re_downloads": 8,"uninstalls": 18,"updates": 192,"returns": 0,"net_downloads": 43,"date": "2020-05-05"  },  "2020-05-06": {"downloads": 45,"re_downloads": 7,"uninstalls": 0,"updates": 196,"returns": 0,"net_downloads": 45,"date": "2020-05-06"  }}';
  const data=JSON.parse(js);
  const sh=SpreadsheetApp.getActiveSheet();
  var oA=Object.keys(data);
  var vA=[];
  //var html="";
  oA.forEach(function(key){
    var kA=Object.keys(data[key]);
    var nA=new Array(kA.length-1);
    vA.push([key].concat(nA));
    vA.push(kA);
    //html+=Utilities.formatString('<br /><strong>%s</strong>',key);
    var row=[];
    kA.forEach(function(e){
      //html+=Utilities.formatString('<br />data[%s][%s]= %s',key,e,data[key][e]);
      row.push(data[key][e]);
    });
    vA.push(row);
    //html+='<hr>';
  });
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), "Test");
  sh.clear();
  sh.getRange(1,1,vA.length,vA[0].length).setValues(vA);
}

This is probably a more standard approach. The function with a couple of tweaks.

function myFunction() {
  const js='{"2020-05-05": {"downloads": 43,"re_downloads": 8,"uninstalls": 18,"updates": 192,"returns": 0,"net_downloads": 43,"date": "2020-05-05"  },  "2020-05-06": {"downloads": 45,"re_downloads": 7,"uninstalls": 0,"updates": 196,"returns": 0,"net_downloads": 45,"date": "2020-05-06"  }}';
  const data=JSON.parse(js);
  const sh=SpreadsheetApp.getActiveSheet();
  var oA=Object.keys(data);
  var vA=[];
  //var html="";
  oA.forEach(function(key,i){
    var kA=Object.keys(data[key]);
    var nA=new Array(kA.length-1);
    //vA.push([key].concat(nA));
    if(i==0)vA.push(kA);
    //html+=Utilities.formatString('<br /><strong>%s</strong>',key);
    var row=[];
    kA.forEach(function(e){
      //html+=Utilities.formatString('<br />data[%s][%s]= %s',key,e,data[key][e]);
      row.push(data[key][e]);
    });
    vA.push(row);
    //html+='<hr>';
  });
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), "Test")
  sh.clear();
  sh.getRange(1,1,vA.length,vA[0].length).setValues(vA);
}

Upvotes: 1

Related Questions