JSON to cell google sheet array work but object not working by binanace API

Question

this is Response:

{
 "makerCommission": 15,
 "takerCommission": 15,
 "buyerCommission": 0,
 "sellerCommission": 0,
 "canTrade": true,
 "canWithdraw": true,
 "canDeposit": true,
 "updateTime": 123456789,
 "accountType": "SPOT",
 "balances": [
   {
  "asset": "BTC",
  "free": "4723846.89208129",
  "locked": "0.00000000"
   },
   {
  "asset": "LTC",
  "free": "4763368.68006011",
  "locked": "0.00000000"
   }
            ],
  "permissions": [
  "SPOT"
  ]
 }

This is my code :

   var jsonResponce = JSON.parse(data); // To JSON
   Logger.log(jsonResponce.accountType); // Return SPOT

   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var rows = [],
       balanc;

  for (i = 0; i < jsonResponce.length; i++) {
     balanc = jsonResponce[i];
     rows.push([balanc.accountType]);
    }
  Logger.log(rows); /////////////// return []  why not return SPOT
  dataRange = sheet.getRange(1, 1, rows.length, 1);
  dataRange.setValues(rows);

The JSON return accountType if run without array but after add in array give me [] empty any help pls.

this is picture:

enter image description here

Where did you define `balancearray`? Please provide the complete code with sample input and your expected output — Ron M, Aug 02 '21 at 21:52
I'm sorry the `balancearray` is `jsonResponce` I change the code error thanks for helping — john, Aug 02 '21 at 22:21

Ron M · Answer 1 · 2021-08-03T16:24:25.980

The reason why your rows is empty is because your jsonResponce is not an array. If you will log jsonResponce.length it will return null.

Sample code:

function myFunction() {
  var jsonData = [{
 "makerCommission": 15,
 "takerCommission": 15,
 "buyerCommission": 0,
 "sellerCommission": 0,
 "canTrade": true,
 "canWithdraw": true,
 "canDeposit": true,
 "updateTime": 123456789,
 "accountType": "SPOT",
 "balances": [
   {
  "asset": "BTC",
  "free": "4723846.89208129",
  "locked": "0.00000000"
   },
   {
  "asset": "LTC",
  "free": "4763368.68006011",
  "locked": "0.00000000"
   }
            ],
  "permissions": [
  "SPOT"
  ]
 }];
  var data = JSON.stringify(jsonData);
  var jsonResponce = JSON.parse(data); // To JSON
  Logger.log(jsonResponce); 
  Logger.log(Array.isArray(jsonResponce))
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   Logger.log(jsonResponce.length);
   var rows = [],
       balanc;

  for (i = 0; i < jsonResponce.length; i++) {
     balanc = jsonResponce[i];
     rows.push([balanc.accountType]);
    }
  Logger.log(rows); /////////////// return []  why not return SPOT
  dataRange = sheet.getRange(1, 1, rows.length, 1);
  dataRange.setValues(rows);
  
}

Modifications done:

create a json array. (encapsulate original json data with [])

Output:

6:37:31 AM  Notice  Execution started
6:37:31 AM  Info    [{canDeposit=true, sellerCommission=0.0, balances=[{locked=0.00000000, asset=BTC, free=4723846.89208129}, {locked=0.00000000, asset=LTC, free=4763368.68006011}], takerCommission=15.0, permissions=[SPOT], canTrade=true, makerCommission=15.0, updateTime=1.23456789E8, buyerCommission=0.0, accountType=SPOT, canWithdraw=true}]
6:37:32 AM  Info    1.0
6:37:32 AM  Info    [[SPOT]]
6:37:34 AM  Notice  Execution completed

Note:

You can use Array.isArray(), to check if the variable is an array or not then read the data accordingly.

Sample:

  if(Array.isArray(jsonResponce)){
    for (i = 0; i < jsonResponce.length; i++) {
     balanc = jsonResponce[i];
     rows.push([balanc.accountType]);
    }
  }else{
    rows.push([jsonResponce.accountType])
  }

(UPDATE)

If you want to make the Binance API response into an array, you can append "[" and "]" on your data.

Sample:

 var jsonData = {
 "makerCommission": 15,
 "takerCommission": 15,
 "buyerCommission": 0,
 "sellerCommission": 0,
 "canTrade": true,
 "canWithdraw": true,
 "canDeposit": true,
 "updateTime": 123456789,
 "accountType": "SPOT",
 "balances": [
   {
  "asset": "BTC",
  "free": "4723846.89208129",
  "locked": "0.00000000"
   },
   {
  "asset": "LTC",
  "free": "4763368.68006011",
  "locked": "0.00000000"
   }
            ],
  "permissions": [
  "SPOT"
  ]
 };

  var data = JSON.stringify(jsonData);

  data = "["+data+"]";
  var jsonResponce = JSON.parse(data); // To JSON
  Logger.log(jsonResponce);
  Logger.log(Array.isArray(jsonResponce))

Output:

12:21:48 AM Info    [{makerCommission=15.0, canDeposit=true, balances=[{free=4723846.89208129, locked=0.00000000, asset=BTC}, {free=4763368.68006011, asset=LTC, locked=0.00000000}], takerCommission=15.0, updateTime=1.23456789E8, accountType=SPOT, canTrade=true, sellerCommission=0.0, buyerCommission=0.0, canWithdraw=true, permissions=[SPOT]}]
12:21:48 AM Info    true

I'm not sure if your expected json string in binanace API is an array or just a single json data in your original post. But based on your for loop you might be expecting a json array. Let me know if my assumption is incorrect so i could update the answer accordingly. — Ron M, Aug 02 '21 at 22:43
I try to do this `encapsulate original json data with []` but I don't find the true code. can you write this code pls — john, Aug 03 '21 at 15:35
I see your change in code JSON, but the code JSON is from binance API like that code in first post. I tell you if you have any method to change it like you do in code — john, Aug 03 '21 at 16:18
See the updated answer. You just need to append "[]" in your data string. `data = "["+data+"]";` — Ron M, Aug 03 '21 at 16:25

score 1 · Accepted Answer · answered Aug 02 '21 at 22:38

1

function myfunk() {
  const data = '{"makerCommission":15,"takerCommission":15,"buyerCommission":0,"sellerCommission":0,"canTrade":true,"canWithdraw":true,"canDeposit":true,"updateTime":123456789,"accountType":"SPOT","balances":[{"asset":"BTC","free":"4723846.89208129","locked":"0.00000000"},{"asset":"LTC","free":"4763368.68006011","locked":"0.00000000"}],"permissions":["SPOT"]}';
  const obj = JSON.parse(data);
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const vs = obj.balances;
  const oA = vs.map(obj => [obj.asset,obj.free,obj.locked]);
  sh.getRange(1,1,oA.length, oA[0].length).setValues(oA);
}

Sheet1:

Col1	Col2	Col3
BTC	4723846.892	0
LTC	4763368.68	0

answered Aug 02 '21 at 22:38

Cooper

59,616
6
23
54

thanks This code is work but why about line `"makerCommission":15,"takerCommission":15,"buyerCommission":0,"sellerCommission":0,"canTrade":true,"canWithdraw":true,"canDeposit":true,"updateTime":123456789,"accountType":"SPOT"` but how if you want to add `"updateTime":123456789` or another item. I try like that but not work `const oS = (obj => [obj.accountType]); sh.getRange(5,5,oS.length, oS[0].length).setValues(oS);` give me error `TypeError: Cannot read property 'length' of undefined` – john Aug 03 '21 at 10:34
obj is no longer json it's just a javascript so just like any other object update time is obj.updatetime. accountType is obj.accountType. I'm not familiar with this code structure `(obj => [obj.accountType]):` it makes no sense to me – Cooper Aug 03 '21 at 15:23
Yes. your are right so do you know any method to put object `accountType` in sheet. – john Aug 03 '21 at 15:33
`sheet.getRange('A1').setValue(obj.accountType)` – Cooper Aug 03 '21 at 16:09

JSON to cell google sheet array work but object not working by binanace API

2 Answers2

Sample code:

Modifications done:

Output:

Note:

Sample:

(UPDATE)

Sample:

Output: