how to extract multi layered array from json

Question

I have a JSON response resembling the following schema...

{
    "resultCode": 200,
    "message": "Success",
    "generated": "2018-11-14T18:42:02",
    "expires": "2018-11-14T18:50:00",
    "data": {
        "generationByFuel": [
            {
                "date": "2018-11-14T00:00:00",
                "period": 37,
                "generation": [
                    {
                        "fuel": "Hydro",
                        "generation": 1011.298,
                        "capacity": 2000
                    },
                    {
                        "fuel": "Wind",
                        "generation": 2544.788,
                        "capacity": 3500
                    }
                ]
            },
            {
                "date": "2018-11-14T00:00:00",
                "period": 36,
                "generation": [
                    {
                        "fuel": "Hydro",
                        "generation": 1100,
                        "capacity": 2000
                    },
                    {
                        "fuel": "Wind",
                        "generation": 2500,
                        "capacity": 3500
                    }
                ]
            }
        ]
    }
}

From this, I need to extract multiple arrays that will be inserted into a googlesheet ("shtGen") filling the following columns:

| Date | Period | Fuel | Generation | Capacity | Runtime |

I'm guessing what I need to do is write something that outputs (generationByFuel[i](generation[r])) but I'm struggling to figure out how to do that.

I've tried a number of options but below is my last effort where I think I'm on the right track. That said, the function runs without returning an error but doesn't insert any info into the google sheet. Same thing happens when I run the function with the debugger.

// define output to paste into sheet
for (var i in genData) {
  var iInput = [];
  iInput.push(genData[i].date);
  iInput.push(genData[i].period);
  for (var r in fuelData) {
    var rInput = [];
    rInput.push(fuelData[r].fuel);
    rInput.push(fuelData[r].capacity);
    rInput.push(fuelData[r].generation);
    rInput.push(now = new Date());
    shtGen.appendRow(iInput + rInput);
  }
}

Any help or suggestions here would be greatly appreciated. Thanks in advance!

Answer 1

Here's an alternative approach that leverages JSON.parse().

Reviver functions are an optional parameter to JSON.parse(). The cool thing about these functions is that they are invoked with key/value pairs for every node in a JSON object tree using depth-first search.

Run the following snippet to get an idea of how it works:

var jsonString = JSON.stringify({
        "resultCode": 200,
        "message": "Success",
        "generated": "2018-11-14T18:42:02",
        "expires": "2018-11-14T18:50:00",
        "data": {
            "generationByFuel": [
                {
                    "date": "2018-11-14T00:00:00",
                    "period": 37,
                    "generation": [
                        {
                            "fuel": "Hydro",
                            "generation": 1011.298,
                            "capacity": 2000
                        },
                        {
                            "fuel": "Wind",
                            "generation": 2544.788,
                            "capacity": 3500
                        }
                    ]
                },
                {
                    "date": "2018-11-14T00:00:00",
                    "period": 36,
                    "generation": [
                        {
                            "fuel": "Hydro",
                            "generation": 1100,
                            "capacity": 2000
                        },
                        {
                            "fuel": "Wind",
                            "generation": 2500,
                            "capacity": 3500
                        }
                    ]
                }
            ]
        }
    });

JSON.parse(jsonString, function(key, value) {
    console.log("Key:%s, Value:%o", key, value);
    return value;
});

The console log generated by the above script 'walks' the object tree depth-first; it drills down to print out the value of the leaf nodes before it prints the value of the parent nodes. For example: "fuel":"Hydro" is printed before the "generation":[{...}, {...},..] array that contains it.

You can exploit this to generate the requisite data for your spreadsheet as follows:

var jsonString = JSON.stringify({
        "resultCode": 200,
        "message": "Success",
        "generated": "2018-11-14T18:42:02",
        "expires": "2018-11-14T18:50:00",
        "data": {
            "generationByFuel": [
                {
                    "date": "2018-11-14T00:00:00",
                    "period": 37,
                    "generation": [
                        {
                            "fuel": "Hydro",
                            "generation": 1011.298,
                            "capacity": 2000
                        },
                        {
                            "fuel": "Wind",
                            "generation": 2544.788,
                            "capacity": 3500
                        }
                    ]
                },
                {
                    "date": "2018-11-14T00:00:00",
                    "period": 36,
                    "generation": [
                        {
                            "fuel": "Hydro",
                            "generation": 1100,
                            "capacity": 2000
                        },
                        {
                            "fuel": "Wind",
                            "generation": 2500,
                            "capacity": 3500
                        }
                    ]
                }
            ]
        }
    });

var values = [];
var state = {};

JSON.parse(jsonString, function(key, value) {
    
    switch(key) {
        case "generation":
            if (Array.isArray(value)) break;
        case "date":
        case "period":
        case "fuel":
        case "capacity":
            state[key] = value;
            break;
    }
    
    if (key === "capacity") {
        values.push([
            state.date,
            state.period,
            state.fuel,
            state.generation,
            state.capacity,
            new Date()
        ]);
    }
    
    return value;
});

console.log(values);

The snippet above filters out the desired data as the reviver walks the JSON's object tree and populates the 2D values array in the process. Now all you need to do is call setValues() on your sheet with the values array.

Answer 2

How about this modification? I think that there are several solutions for your situation. So please think of this as one of them.

Modification points:

• fuelData is required to be declared in the 1st for loop.
  • This is mentioned by @Slai.
• iInput + rInput becomes the string type. By this, an error occurs at appendRow().
  • iInput.concat(rInput) is used for this.
• In order to arrange to | Date | Period | Fuel | Generation | Capacity | Runtime |, the order for pushing the value is required to be
  1. rInput.push(fuelData[r].fuel)
  2. rInput.push(fuelData[r].generation)
  3. rInput.push(fuelData[r].capacity)

In this modified script, obj is your json object in your question. And then shtGen supposes the sheet object.

Modified script:

var obj = {### your json object ###};
var genData = obj.data.generationByFuel;
for (var i in genData) {
  var iInput = [];
  iInput.push(genData[i].date);
  iInput.push(genData[i].period);
  var fuelData = genData[i].generation; // Added
  for (var r in fuelData) {
    var rInput = [];
    rInput.push(fuelData[r].fuel); // Modified
    rInput.push(fuelData[r].generation); // Modified
    rInput.push(fuelData[r].capacity); // Modified
    rInput.push(new Date());
    shtGen.appendRow(iInput.concat(rInput)); // Modified
  }
}

I think that when the object is large, appendRow() becomes the reason of the increase of the process cost. So I recommend to use setValues() for putting the values to Spreadsheet as follows.

var obj = {### your json object ###};
var res = [];
var genData = obj.data.generationByFuel;
for (var i in genData) {
  var iInput = [];
  iInput.push(genData[i].date);
  iInput.push(genData[i].period);
  var fuelData = genData[i].generation;
  for (var r in fuelData) {
    var rInput = [];
    rInput.push(fuelData[r].fuel);
    rInput.push(fuelData[r].generation);
    rInput.push(fuelData[r].capacity);
    rInput.push(new Date());
    res.push(iInput.concat(rInput)); // Modified
  }
}
shtGen.getRange(shtGen.getLastRow() + 1, 1, res.length, res[0].length).setValues(res); // Added

Other pattern:

As one of other modifications, you can also use the following script. The cost of this script is lower than that of above scripts.

var obj = {### your json object ###};
var res = obj.data.generationByFuel.reduce(function(ar, e) {
  return ar.concat(e.generation.map(function(f) {
    return [e.date, e.period, f.fuel, f.generation, f.capacity, new Date()];
  }));
}, []);
shtGen.getRange(shtGen.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);

References:

• appendRow()
• setValues()
• concat()
• reduce()
• map

If this was not what you want, please tell me. I would like to modify it.

Answer 3

I think you missed the "genData[i]" within the for loop for var 'r'. Try this.

for (var i in genData) {
  var iInput = [];
  iInput.push(genData[i].date);
  iInput.push(genData[i].period);
  for (var r in fuelData) {
    var rInput = [];
    rInput.push(genData[i].fuelData[r].fuel);
    rInput.push(genData[i].fuelData[r].capacity);
    rInput.push(genData[i].fuelData[r].generation);
    rInput.push(now = new Date());
    shtGen.appendRow(iInput + rInput);
  }
}

Also it can be how you are storing the response in your model class.