Is there any way to convert this=> google sheet into the below given JSON object using AppScript. I'm new to AppScript I dont have idea how to make this row and column in the below given object. Thanks in advance.
{
"data":[
{
"insurer":"CompanyName1",
"products":[
{
"name":"product1",
"UIN":"104N079V01"
},
{
"name":"product2",
"UIN":"104N079V02"
}
]
},
{
"insurer":"CompanyName2",
"products":[
{
"name":"product1",
"UIN":"104N079V01"
},
{
"name":"product2",
"UIN":"104N079V02"
}
]
}
]
}
Try
function data2json() {
var sh = SpreadsheetApp.getActiveSheet()
var values = sh.getRange('A1').getDataRegion().getValues()
// Logger.log(values)
var jsn = {}
jsn['data'] = []
var n = -1
var m = 0
values.forEach(function (r, i) {
if (i > 0) {
if (r[1] != '') {
n++
jsn['data'][n] = {}
jsn['data'][n]['insurer'] = r[1]
jsn['data'][n]['products'] = []
m = -1
}
m++
jsn['data'][n]['products'][m] = {}
jsn['data'][n]['products'][m]['name'] = r[2]
jsn['data'][n]['products'][m]['UIN'] = r[3]
}
})
Logger.log(JSON.stringify(jsn))
}
example
result
{"data":[{"insurer":"CompanyName1","products":[{"name":"product1","UIN":"A"},{"name":"product2","UIN":"B"},{"name":"product3","UIN":"C"},{"name":"product4","UIN":"D"}]},{"insurer":"CompanyName2","products":[{"name":"product5","UIN":"E"},{"name":"product6","UIN":"F"},{"name":"product7","UIN":"G"},{"name":"product8","UIN":"H"}]},{"insurer":"CompanyName3","products":[{"name":"product9","UIN":"I"},{"name":"product10","UIN":"J"},{"name":"product11","UIN":"K"}]}]}