google-sheetssourceforge-appscript

Convert google sheet Row and column in JSON using app script


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

Solution

  • 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

    enter image description here

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