Print JSON array output to Google Sheet
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
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)}