Print JSON array output to Google Sheet

Question

I am trying to parse the output of a JSON array in Google Script and print it to a sheet. Expected output in the sheet:

Row 1: AUD | CNY | GBP | HKD
Row 2: 0.98 | 5.01 | 0.56 | 5.60
Row 3: 0.91 | 8.01 | 0.93 | 2.61

I am currently using the below script which is giving me the output as

Row 1: {AUD=0.98, HKD=5.01, GBP=0.56, CNY=5.60}
Row 2: {AUD=0.91, HKD=8.01, GBP=0.93, CNY=2.61}

Can anyone help with what I'm missing here?

    function addObject(){
     var obj = {
        "base": "CAD",
        "date": "2017-05-05",
        "rates": [{
            "AUD": "0.98",
            "CNY": "5.01",
            "GBP": "0.56",
            "HKD": "5.60"
        },
                 {
            "AUD": "0.91",
            "CNY": "8.01",
            "GBP": "0.93",
            "HKD": "2.61"
        }]
    }
    var inputArray = []
    var rates = obj.rates
    for (var j in rates){
      inputArray.push([rates[j]])
    }
    Logger.log(inputArray)
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Sheet1');
    sheet.getRange(sheet.getLastRow()+1, 1,inputArray.length,inputArray[0].length).setValues(inputArray)

    }

score 3 · Answer 1 · answered Feb 08 '20 at 15:31

You need to convert the array to a 2 dimensional one:

let obj = {
    "base": "CAD",
    "date": "2017-05-05",
    "rates": [{
        "AUD": "0.98",
        "CNY": "5.01",
        "GBP": "0.56",
        "HKD": "5.60"
    },
             {
        "AUD": "0.91",
        "CNY": "8.01",
        "GBP": "0.93",
        "HKD": "2.61"
    }]
}

let headers = Object.keys(obj.rates[0]);
let out = obj.rates.map(({AUD,CNY,GBP,HKD})=> [AUD,CNY,GBP,HKD]);
out.unshift(headers);
console.log(out);//setvalues this array: out

Thanks. I was able to figure it out with the following modification: ` var employee_data = obj.employee_data; for (var i in employee_data){ inputArray_UniqueID.push(["Active",employee_data[i].user_unique_id]) } ` — user9877823, Feb 08 '20 at 16:42

score 2 · Answer 2 · answered Feb 08 '20 at 17:51

2

Pre: V8

function doit() {
  var obj = {
    "base": "CAD",
    "date": "2017-05-05",
    "rates": [{
      "AUD": "0.98",
      "CNY": "5.01",
      "GBP": "0.56",
      "HKD": "5.60"
    },
              {
                "AUD": "0.91",
                "CNY": "8.01",
                "GBP": "0.93",
                "HKD": "2.61"
              }]
  }

  var headers=Object.keys(obj.rates[0]);
  var out=obj.rates.map(function({AUD:AUD,CNY:CNY,GBP:GBP,HKD:HKD}){ return [AUD,CNY,GBP,HKD]});
  out.unshift(headers);
  //Logger.log(out);
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  //sh.clearContents();
  sh.getRange(sh.getLastRow()+1,1,out.length,out[0].length).setValues(out)
}

answered Feb 08 '20 at 17:51

Cooper

59,616
6
23
54

I had no idea rhino supported this object destructuring: `function({AUD:AUD,CNY:CNY,GBP:GBP,HKD:HKD}){`. Could've reduced a lot of code in my previous answers. Oh well... – TheMaster Feb 08 '20 at 18:22
1

I've been reading es6 stuff for last few days and I can't remember the reference but the error said I missing was a `:` and that struck a memory. I think it may have been in a video on YT. I think this is a good time for me to learn because we're going start getting a lot answers and questions with the new syntax and I'll get the benefit of answers from people who have known this information for a while. – Cooper Feb 08 '20 at 18:52

Print JSON array output to Google Sheet

2 Answers2