How to split nested arrays in a JSON into a table

问题:

I've finally connected via OAuth2 to pull a JSON of a performance report (of shares) and I'm wondering how I can load this object into Google Sheets so it's in a matrix format?

{
    "id": "ID-23035",
    "total_gain": 11795.72,
    "holdings": 
     [
        {
            "symbol": "APX",
            "total_gain": 11525.72,
        },
        {
            "symbol": "MP1",
            "total_gain": 270,
        }
    ]
}

The whole JSON is stored in a report object.

I know how to extract the 'id' and the 'total gain' from the 1st two rows, using:

  var id= report_data.id
  var total_gain = report_data.total_gain

However I'd like to take the holdings component, i.e.

  var holdings = report_data.holdings

And then import it into Google Sheets in a matrix style table - to make it look like:

|-----symbol-----|----total gain-----|

|------APX-------|-----11525.72------|

|------MP1-------|--------270--------|

Is there some sort of code/function/script that can loop through and create this matrix/table? I'm guessing Google App Script uses JavaScript?

回答1:

You could use a simple for-loop to convert it into a 2 Dimentional array or Array.map

var arr2D = holdings.map(function(e){
          return [e.symbol,e.total_gain]});

• Array#map
• For-loop
• setValues

回答2:

You can set headers in google sheet with the name symbol and total_gain ,then make data key-pairs with symbol with their total_gain value, now you can directly insert the data in the sheet.

var data = {
    "id": "ID-23035",
    "total_gain": 11795.72,
    "holdings": 
     [
        {
            "symbol": "APX",
            "total_gain": 11525.72,
        },
        {
            "symbol": "MP1",
            "total_gain": 270,
        }
    ]
}  

var formatdata =[];
    data.holdings.forEach(function(val,index){  
      formatdata[val.symbol] = val.total_gain; 
    });
    console.log(formatdata)

回答3:

data = {
    "id": "ID-23035",
    "total_gain": 11795.72,
    "holdings": 
     [
        {
            "symbol": "APX",
            "total_gain": 11525.72,
        },
        {
            "symbol": "MP1",
            "total_gain": 270,
        }
    ]
}

let TwoDArray =  data.holdings.map(({symbol, total_gain})=> [symbol, total_gain])
console.log(TwoDArray)

回答4:

(Posted solution on behalf of the question author).

This is the resulting code I used. Note that I have ended up returning 3 fields so the colNumber changed from 2 to 3.

  var arr2D = holdings.map(function(e){
          return [e.symbol,e.name, e.total_gain]});
  var range = SpreadsheetApp.getActiveSheet().getRange(8,1,arr2D.length,3)       
  range.setValues(arr2D);