Spreadsheet display JSON data into columns and rows

Question

I'd like to handle a bunch of data in Google Spreadsheets so I can easily create charts and all that stuff. The data come in Json format from an URL. I can successfully get those data in a Spreadsheet but I cannot figure out an easy way to distribute those data into columns of the sheet.

This is a sample of the JSON structure:

Is there any easy way to achieve my goal?

Thanks a lot.

Answer 1

How about this sample script? This sample script retrieves all elements in JSON data using the recursive call, and import them to Spreadsheet.

In order to use this script, please set as follows. 1. Use this script as a bound script for Spreadsheet. 1. Define json data in main() or as a global variable.

Sample JSON :

var json = {
  keyA: {
    ArrayA1: ["ArrayA1_val1", "ArrayA1_val2", "ArrayA1_val3"],
    keyA1: "keyA1_val",
    keyA2: "keyA2_val",
  },
  keyB: {
    ArrayB1: ["ArrayB1_val1", "ArrayB1_val2", "ArrayB1_val3"],
    keyB1: "keyA1_val",
    keyB2: "keyA2_val",
    keyB3: {
      ArrayB31: ["ArrayB31_val1", "ArrayB31_val2", "ArrayB31_val3"],
      ArrayB32: ["ArrayB32_val1", "ArrayB32_val2", "ArrayB32_val3"],
    }
  }
};

Sample Script :

function getElements(v, callback) {
  for (var k in v) {
    callback(
      k,
      Object.prototype.toString.call(v[k]).slice(8, -1).toLowerCase() === "object"
        ? JSON.stringify(v[k], null, "\t") : v[k],
      Object.prototype.toString.call(v[k]).slice(8, -1).toLowerCase()
    );
    if (typeof v[k] === "object") {
      getElements(v[k], callback);
    }
  }
}

function main() {
  var ar = [];
  getElements(json, function(key, value, type) {ar.push([key, value, type])});

  var ss = SpreadsheetApp.getActiveSheet();
  ss.getRange(ss.getLastRow() + 1, 1, ar.length, ar[0].length).setValues(ar);
}

Result :

If I misunderstand your question, I'm sorry.