如何将JSON中的嵌套数组拆分为Google表格中的表格(使用Javascript?) [英] How to split nested arrays in a JSON into a table in Google Sheets (using Javascript?)

问题描述

我终于通过OAuth2连接以提取(共享的)绩效报告的JSON，我想知道如何将该对象加载到Google表格中，使其呈矩阵格式?

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,
        }
    ]
}

整个JSON存储在报告对象中.

The whole JSON is stored in a report object.

我知道如何使用以下方法从前两行提取"id"和"total gain":

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

然后将其导入矩阵样式表的Google表格中-使其看起来像:

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--------|

是否存在可以循环并创建此矩阵/表的某种代码/函数/脚本?我猜Google App脚本使用JavaScript吗?

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?

推荐答案

您可以使用简单的for循环将其转换为2维数组或Array.map

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
• 循环
• setValues
• Array#map
• For-loop
• setValues

这篇关于如何将JSON中的嵌套数组拆分为Google表格中的表格(使用Javascript?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！