Parse received information into columns/rows

Question

I have a script to get all the data from a Binance and push out parsed information. Here's my script:

function myFunction() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();

  var data = UrlFetchApp.fetch("https://www.binance.com/assetWithdraw/getAllAsset.html");
  var first = JSON.parse(data)[0];
  var out = JSON.stringify(first).split(",");

  for(var i =0;i<out.length;i++){
    Logger.log(i);
    Logger.log(out[i]);
    ss.getRange(1,i+1).setValue(out[i]);
  }
}

I received information that looks like "title":"value". I need to get all the "title" values as column headers and then the values as the rows. I'm not sure where to go from here.

Answer 1

How about this modification?

Modification points :

• Retrieve each data by separating "title" and "values".
• Create an array for importing data to Spreadsheet and import it using setValues().

Modified script :

function myFunction() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  var data = UrlFetchApp.fetch("https://www.binance.com/assetWithdraw/getAllAsset.html");

  // Modified
  var out = JSON.parse(data.getContentText());
  var title = [];
  for (var i in out[0]) {
    title.push(i);
  }
  var res = [];
  res.push(title);
  for (var i in out) {
    var values = [];
    for (var j in out[i]) {
      values.push(out[i][j]);
    }
    res.push(values);
  }
  ss.getRange(ss.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
}

Reference :

• setValues()

If I misunderstand your question, please tell me. I would like to modify it.

Edit 1 :

In order to retrieve keys from object which is out[0], I used for (var i in out[0]) {. Please see the following samples.

Sample 1 :

var sample = [{key1: "value1", key2: "value2"}];
for (var i in sample[0]) {
  Logger.log("%s, %s", i, sample[0][i])
}

Result

key1, value1
key2, value2

In this script, sample[0] is an object of {key1: "value1", key2: "value2"}. for (var i in sample[0]) { can retrieve keys which are key1 and key2. Using retrieved keys, value1 and value2 can be retrieved by sample[0][i].

On the other hand, please see the next sample script.

Sample 2 :

var sample = [{key1: "value1", key2: "value2"}];
for (var i = 0; i < sample[0].length; i++) {
  Logger.log("%s, %s", i, sample[0][i])
}

In this script, sample[0].length becomes undefined. Because object.length cannot retrieve the length. So this for loop doesn't work.

If you want to retrieve keys and values using for loop like the sample 2. Please use the next sample script.

Sample 3 :

var sample = [{key1: "value1", key2: "value2"}];
var keys = Object.keys(sample[0]);
for (var i=0; i<keys.length; i++) {
  Logger.log("%s, %s", keys[i], sample[0][keys[i]])
}

Result

key1, value1
key2, value2

In this script, keys from object can be retrieved by Object.keys(). Using this, keys and values are retrieved.

Reference :

• for...in
• Object.keys()

Edit 2 :

function myFunction() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  var data = UrlFetchApp.fetch("https://www.binance.com/assetWithdraw/getAllAsset.html");

  // Modified
  var out = JSON.parse(data.getContentText());
  var title = [];
  for (var i in out[0]) {
    if (i == "assetCode" || i == "transactionFee") {
      title.push(i);
    }
  }
  var res = [];
  res.push(title);
  for (var i in out) {
    var values = [];
    for (var j in out[i]) {
      if (j == "assetCode" || j == "transactionFee") {
        values.push(out[i][j]);
      }
    }
    res.push(values);
  }
  ss.getRange(ss.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
}