Spreadsheet display JSON data into columns and rows -
i'd handle bunch of data in google spreadsheets can create charts , stuff. data come in json format url. can data in spreadsheet cannot figure out easy way distribute data columns of sheet.
this sample of json structure:
is there easy way achieve goal?
thanks lot.
how sample script? sample script retrieves elements in json data using recursive call, , import them spreadsheet.
in order use script, please set follows. 1. use script bound script spreadsheet. 1. define json
data in main()
or 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) { (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 misunderstand question, i'm sorry.
Comments
Post a Comment