Skip to content

Google Scripts – Combine multiple arrays in vlookup type fashion

I am trying to get an array to print out information from two different sources. I am looking for something that will look up one set of values, print the array. then take a value from the first array, use this in a vlookup type fashion, find the value in the second array, and then create a new array that has all the values I need.

So take this code below. I am grabbing the fields I care about in the ‘contents’ variable, and printing it to an array oA. But in ‘contents2’ there is another value that I also care about, which I am currently printing to oA2. Based on what I’ve done so far I have two different arrays, that I basically need to append together. Since the first array could be 10 items or so, and the list on contents2 could be 100 items, I’m looking for a way to take the value I’ve seen in oA and use that as a vlookup value in contents2 and print that in a new array.

function myfunction5() {

 var contents = '{"pagination":{"ending_before":null,"starting_after":null,"previous_ending_before":null},"data":[{"name":"NMR Wallet","balance":{"amount":"0.00000000","currency":"NMR"},"created_at":"2021-02-14T16:46:46Z"},{"name":"SNX Wallet","balance":{"amount":"0.00000000","currency":"SNX"},"created_at":"2021-02-14T16:46:46Z"},{"name":"UMA Wallet","balance":{"amount":"2.00000000","currency":"UMA"},"created_at":"2021-02-14T16:46:46Z"}]}'

 var contents2 = '{"data":[{"currency":"USD","rates":{"NMR":"3668.31071","UMA":"0.034456619116532285","UNI":"0.033166560810060086"}}]}'

 var obj = JSON.parse(contents);

 var obj2 = JSON.parse(contents2);

  let oA=[];
  obj.data.forEach((o,i)=>{
    if(o.balance.amount>0) {
      oA.push([o.balance.currency,o.balance.amount]);
    }
  });

  let oA2=[];
  obj2.data.forEach((o,i)=>{
      oA2.push([o.rates.UMA]);
  });

Logger.log(oA);
Logger.log(oA2)

}

This will produce the following:

10:53:38 AM Notice  Execution started
10:53:38 AM Info    [[UMA, 2.00000000]]
10:53:38 AM Info    [[0.034456619116532285]]
10:53:38 AM Notice  Execution completed

But I really want it to produce:

[[UMA, 2.00000000, 0.034456619116532285]]

Any pointers on the best way to accomplish this?

Answer

Solution:

You can use bracket notation to reference a property using a variable. Note that in the sample code I changed the NMR balance to 1 so it will be displayed as well.

function myfunction() {

 var contents = '{"pagination":{"ending_before":null,"starting_after":null,"previous_ending_before":null},"data":[{"name":"NMR Wallet","balance":{"amount":"1.00000000","currency":"NMR"},"created_at":"2021-02-14T16:46:46Z"},{"name":"SNX Wallet","balance":{"amount":"0.00000000","currency":"SNX"},"created_at":"2021-02-14T16:46:46Z"},{"name":"UMA Wallet","balance":{"amount":"2.00000000","currency":"UMA"},"created_at":"2021-02-14T16:46:46Z"}]}'

  var contents2 = '{"data":[{"currency":"USD","rates":{"NMR":"3668.31071","UMA":"0.034456619116532285","UNI":"0.033166560810060086"}}]}'

  var obj = JSON.parse(contents);

  var obj2 = JSON.parse(contents2);

  let oA=[];
  obj.data.forEach((o,i)=>{
    if(o.balance.amount>0) {
      let curr = o.balance.currency;
      oA.push([curr,o.balance.amount]);
      obj2.data.forEach((o,i)=> {
        oA[oA.length-1].push(o.rates[curr]);
      });
    }
  });

console.log(oA);

}

Sample Result:

enter image description here

References:

Property Accessors

Addendum:

If you want to use the new format in your comment, you need to check each obj2.base like this:

 obj2.data.forEach((o,i)=> {
   if (o.base == curr) {
        oA[oA.length-1].push(o.amount);
   }
 });