retrieve object from array WHERE another object matches another array

Question

please note: this is NOT a duplicate: different needs are involved.

I am working on a Google Sheets based portfolio for cripto assets. A summary page provides balances for all the assets in any exchange.

the first function connects to the account of the first exchange (say Bitstamp) and downloads an up-to-date list of all the assets'names in your wallet and writes them down in the first column, along the corresponding balance under the exchange column .

Then a second function retrieves the assets' amounts from the second exchange's account (let's say binance) and writes them on the corresponding column but of COURSE this time the script needs to match the amount with the assets' names which are already written down from the first script.

There's no fixed cells here!!!

In other words the second script needs to understand in WHAT ROW is set, say, BTC in the first column and needs to write the corresonding btc amount in the binance column in the correspoding row..

if we were on sql you could write somithing like


set btc_amount FROM binance in BINANCE column WHERE btc_asset === cell(i+2, 1).getValue()

unfortunately this would be too easy so I guess we can't do it..

Now, what do you suggest? Arrays' comparison? Arrays of arrays of arrays? maps of arrays? filters of arrays? filter.this?

Here's what I've tried so far: nothing works 100%. Something doesn't work at all.

...
  var data = UrlFetchApp.fetch(url, params);
  var data = JSON.parse(data.getContentText());
  var data = data.balances;

  //binance api returns also assets with zero balance, so we need to filter
  var data = data.filter(function (el) {
     return el.free > 0; 
});



  var i;
  for (i=0; i < data.length; i++) {

  var asset = data[i].asset;
  //this bizarre back and forth is needed in order to BOTH get rid of trailing zeros AND render the value a number format
  var free  = data[i].free;   var a = parseFloat(free);   var a = a.toString();    var free = parseFloat(a);

  //my first choice was to convert the list from the 1st column into an array and try to perform some sort of comparison or mapping  
  var cont  = sheet.getRange(2, 1, 16).getValues();
  //var cont  = cont.toLowerCase()

  //then flattening the array from 2d to 1d    
  function flatten(tdArray){
  return [].concat.apply([], tdArray);
}   
  var cont  = flatten(cont);

  var corr = sheet.getRange(i + 2, 1).getValue();

  var check = data.map(function (value){
    return value.asset == cont; 
  }); 



   if (check == true) {sheet.getRange(i + 2, 8).setValue(free)}


  //var check = check[i];

 // if (check == true){sheet.getRange(i + 2, 8).setValue(free);}  

    //sheet.getRange(i + 2, 1).setValue(asset);
    //sheet.getRange(i + 2, 8).setValue(free);
  }//fine del for

  Logger.log(corr);  
}

var data = UrlFetchApp.fetch(url, params);
  var data = JSON.parse(data.getContentText());
  var data = data.balances;

  var data = data.filter(function (el) {
     return el.free > 0; 
});



  var i;
  for (i=0; i < data.length; i++) {

  var asset = data[i].asset;
  var free  = data[i].free;   var a = parseFloat(free);   var a = a.toString();    var free = parseFloat(a);

  var cont  = sheet.getRange(2, 1, 16).getValues();
  //var cont  = cont.toLowerCase()

  function flatten(tdArray){
  return [].concat.apply([], tdArray);
}   
  var cont  = flatten(cont);

  var h;
    for (h=0; h<cont.length; h++){if (cont[h] === asset){sheet.getRange(i + 2, 8).setValue(free)}}




//  Logger.log(corr);  
  }}

those were two of the solutions I've been trying so far. thanks

EDIT: new similar case with different Json structure

I have applied the same pattern suggested by ziganotschka to the Bitstamp case.

Now, as you can see Bitstamp returns a different kind of Json response compared to the one from binance, which made it harder for me to retrieve objects and properties as it doesn't behave and look the same way the binance one does, i.e.: as a typical array:

Bitstamp Json Data

{btc_available=0.00000684, eth_reserved=0.00000000, eur_balance=0000.63, btcusd_fee=0.500, xrpeur_fee=0.500, btc_balance=0.00000684, xrp_withdrawal_fee=0.02000000, ethusd_fee=0.500, ltceur_fee=0.500, eth_balance=0.11033051, xrp_reserved=0.00000000, bchusd_fee=0.500, eur_reserved=0.00, bch_available=0.00000000, usd_available=0.18, xrp_available=0.75000000, xrpusd_fee=0.500, ltcbtc_fee=0.500, bcheur_fee=0.500, ltc_available=0.00000000, btc_reserved=0.00000000, ltc_withdrawal_fee=0.00100000, usd_reserved=0.00, btc_withdrawal_fee=0.00050000, eurusd_fee=0.500, xrp_balance=0.75000000, ltcusd_fee=0.500, ltc_balance=0.00000000, bch_reserved=0.00000000, bch_withdrawal_fee=0.00010000, eur_available=0000.63, ltc_reserved=0.00000000, bchbtc_fee=0.500, ethbtc_fee=0.500, etheur_fee=0.500, usd_balance=0.18, eth_available=0.11033051, btceur_fee=0.500, eth_withdrawal_fee=0.00100000, bch_balance=0.00000000, xrpbtc_fee=0.500}

Binance Json Data

 {balances=[{asset=BTC, free=0.00966600, locked=0.00000000}, {asset=LTC, free=0.00000000, locked=0.00000000}, {asset=ETH, free=0.00001251, locked=0.00000000}, {asset=NEO, free=0.00000000, locked=0.00000000}, {asset=BNB, free=0.07496873, locked=0.00000000}, {asset=QTUM, free=0.00000000, locked=0.00000000}, {asset=EOS, free=0.00000000, locked=0.00000000}, {asset=SNT, free=0.00000000, locked=0.00000000}, {asset=BNT, free=0.00000000, locked=0.00000000}, {asset=GAS, free=0.00000000, locked=0.00000000}, ...

So I ended up building two new arrays altogheter and then joining them, filtering the array in the very beginning:

var data = JSON.parse(data.getContentText());

var keys    = Object.keys(data);
var values  = Object.keys(data).map(function(e){return data[e]});

var result = [];
var k;
for (k=0; k<keys.length; k++){
  if (keys[k].slice(4, 13) == "available") {result.push([keys[k], values[k]]);}}

    var i;
  for (i=0; i<result.length; i++) {

  var key     = result[i][0].slice(0,3);
  var key     = key.toUpperCase();


  var row     = rows.indexOf(key)+2;
  var value   = result[i][1];

     if (row>=2){
       sheet.getRange(row, 5).setValue(value);

     }  
  }

It does work, but I am sure there's a more elegant or straight-forward way to do this. I was thinking of converting the Json into an array in the first place, to make it look (and work) like the one from binance code but I wasn't able to. Any suggestion?

Is your question about how to assign your data entries into the correct row (the one where the entry in column A is equal to data[i].free? — ziganotschka
– ziganotschka, Commented Dec 12, 2019 at 15:33
to be precise, the one where the entry in column A is equal to data[i].asset, which corresponds to one and only one data[i].free from the same json object. thanks — John Galassi
– John Galassi, Commented Dec 12, 2019 at 16:03

ziganotschka · Accepted Answer · 2019-12-12 17:00:57Z

1

You can use indexOf()

This method allows you to find the positions of a string within an array.

Sample code snippet based on your data:

function myFunction() {
...
  var oldAsset=sheet.getRange(2, 1, 16).getValues(); //Is the range fix or dynamic?
  var rows=[];
  for (j=0; j < oldAsset.length; j++) {
    rows.push(oldAsset[j][0]);
  }
  var data=...//here comes your data processing part
  for (i=0; i < data.length; i++) {
    var newAsset=data[i].asset;
    var free  = data[i].free;  // process as necessary
    var row=rows.indexOf(newAsset)+2; //THIS THE CRUCIAL PART TO FIND THE CORRECT ROW
     if (row>0){
       sheet.getRange(row, 8).setValue(free);
    }   
  }
}

I did not implement in this code snippet processing steps like toLowerCase(), parseFloat() etc. since I do not know what your data looks like.

edited Dec 12, 2019 at 17:00

answered Dec 12, 2019 at 16:10

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

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

7 Comments

John Galassi Over a year ago

thanks!! why didn't I think of that: find the position .. I'll try it straight away and get back to you asap, thanks

John Galassi Over a year ago

thanks man, it works!! only thing: I guess you meant var row=rows.indexOf(newAsset)+2; . Thanks very much, I wouldn't have been able to produce it on my own. I'll reserve some time to study it and in case ask for further explanation if you wish. thanks

ziganotschka Over a year ago

Yes, indexOf(newAsset)! Glad it helped you!

ziganotschka Over a year ago

The easiest way to convert your data into a JSON object I could think of right now would probably be Regex. E.g. data=data.replace(/=/g,":"); and something similar to insert the quotes.

John Galassi Over a year ago

thanks but I switched to coinmarketcap api altogether, instead of coingecko: it provides a much more common array structure, so coinverting to json is not needed anymore for the time being. thanks anyway

|

Collectives™ on Stack Overflow

retrieve object from array WHERE another object matches another array

1 Answer 1

You can use indexOf()

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

You can use indexOf()

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related