0

I am trying to output a JSON response to a Google Spreadsheet. The JSON response looks like this:

[{
    "city": "Mumbai",
    "cityC": "MUM",
    "cat": {
      "Subs": null,
      "respC": 200,
      "respM": "OK",
      "resp": [{
          "CATID": "ABC53249",
          "CATDSCR": "FRIED ITEMS",
          "SEG": [{
              "SEGID": "DFG00985",
              "SEGDSCR": "FRIED FISH"
            },
            {
              "SEGID": "FKK24091",
              "SEGDSCR": "FRIED VEGGIES"
            },
            {
              "SEGID": "LGH65255",
              "SEGDSCR": "FRIED EGGS"
            }
          ]
        },
        {
          "CATID": "GIY35321",
          "CATDSCR": "BOILED ITEMS",
          "SEG": [{
              "SEGID": "QWW35442",
              "SEGDSCR": "BOILED FISH"
            },
            {
              "SEGID": "FKG35443",
              "SEGDSCR": "BOILED EGGS"
            }
          ]
        }
      ],
      "totRec": 0
    }
  },
  {
    "city": "Pune",
    "cityC": "PUN",
    "cat": {
      "Subs": null,
      "respC": 200,
      "respM": "OK",
      "resp": [{
          "CATID": "EFD53249",
          "CATDSCR": "FRUIT FLAVOURS",
          "SEG": [{
              "SEGID": "SLD00985",
              "SEGDSCR": "FRUIT JUICE"
            },
            {
              "SEGID": "DLS24091",
              "SEGDSCR": "FRUIT SALAD"
            },
            {
              "SEGID": "DDD65255",
              "SEGDSCR": "FRUIT MIX"
            }
          ]
        },
        {
          "CATID": "OLS35321",
          "CATDSCR": "MILK ITEMS",
          "SEG": [{
              "SEGID": "REW35442",
              "SEGDSCR": "MILK CHOCOS"
            },
            {
              "SEGID": "DES35443",
              "SEGDSCR": "MILK JUICE"
            }
          ]
        }
      ],
      "totRec": 0
    }
  }
]

The following code parses the JSON but needs headings to be present in the google spreadsheet. It also uses dot (or undescore) notation which i dont want.

     var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");  
     var oJSON = JSON.parse(response.getContentText());

     // flatten the json
     var rows = oJSON.map(function(d){
        return flatten(d);
      });

      // insert new rows
    //  sheet.insertRowsAfter(1, rows.length);
      
      // getting our headers
      var heads = sheet.getDataRange().offset(0, 0, 1).getValues()[0];
         
      // convert object data into a 2d array 
      var tr = rows.map (function (row) {
        return heads.map(function(cell){
          return row[cell] || "";
        });
      });

      // write result
      sheet.getRange(2, 1, tr.length, tr[0].length).setValues(tr);

    }

  

    /**
     * Flatten an object.
     * https://stackoverflow.com/a/19101235/1027723
     *
     * @param {Object} data object to flatten
     * @return {Object} the flattened result
     */
    function flatten(data) {
        var result = {};
        function recurse (cur, prop) {
            if (Object(cur) !== cur) {
                result[prop] = cur;
            } else if (Array.isArray(cur)) {
                 for(var i=0, l=cur.length; i<l; i++)
    //                 recurse(cur[i], prop ? prop+"."+i : ""+i);
                     recurse(cur[i], prop ? ""+i : ""+i);
                if (l == 0)
                    result[prop] = [];
            } else {
                var isEmpty = true;
                for (var p in cur) {
                    isEmpty = false;
    //                recurse(cur[p], prop ? prop+"."+p : p);
                    recurse(cur[p], prop ? p : p);
                }
                if (isEmpty)
                    result[prop] = {};
            }
        }
        recurse(data, "");
        return result;
    }

My question is how do i modify the code to pull the column headings as well as row data to be outputted to the Google spreadsheet?

My spreadsheet headers should be the JSON key : value pairs that should be outputted, where the Key should be the header and its corresponding value should be each row's cell data.

enter image description here

sifar
  • 1,086
  • 1
  • 17
  • 43
  • From ``var oJSON = JSON.parse(response.getContentText());``, it seems that the object is retrieved using ``UrlFetchApp`` from an URL. If it's so, for example, how about directly putting the values to Spreadsheet using [ImportJSON](https://github.com/bradjasper/ImportJSON) with the URL? If this was not the direction you want, I apologize. – Tanaike Jun 13 '19 at 00:47
  • @Tanaike, I forgot to mention I don't want to use ImportJSON for personal reasons. Also I want to learn, and ImportJSON is too complicated to weed through. I am expecting something like a recursive function like the one shown. Hope you understand. – sifar Jun 13 '19 at 04:51
  • 1
    Thank you for replying. I apologize my comment was not useful for your situation. – Tanaike Jun 13 '19 at 06:33
  • 1
    No need to apologize. Your comments are always helpful and welcome. :) – sifar Jun 13 '19 at 07:21
  • I think this requires something like : If Parent object has Child objects having their own properties, then for each property row, Parent object value should be repeated...and keys should be shown as headers from these parent/child objects only that have their own properties... – sifar Jun 13 '19 at 10:58
  • @Tanaike, this code pulls in the first three headers only. I think this needs to be made into a recursive function. Can you advise please? // Extract headers by scanning all results. var headers = []; for (var i = 0; i < oJSON.length; i ++) { for (var field in oJSON[i]) { if (!oJSON[i].hasOwnProperty(field)) continue; if (headers.indexOf(field) > -1) continue; headers.push(field); } } headers.sort(); // Will contain an array of row arrays. var data = []; data.push(headers); – sifar Jun 16 '19 at 15:30
  • Thank you for replying. Unfortunately, I couldn't understand about the method you want. This is due to my poor English skill. I deeply apologize for this. When I could correctly understand about the process you want and found the solution, I would like to answer it. – Tanaike Jun 16 '19 at 22:23
  • @Tanaike, I have displayed the output where the first row contains the headers and their values are below. – sifar Jun 17 '19 at 02:56
  • Yes. So I proposed to use "ImportJSON". From your replying of ``I forgot to mention I don't want to use ImportJSON for personal reasons.``, I understood that you need other process except for "ImportJSON". But I cannot find about the process you want. This is due to my poor skill. I deeply apologize for my poor skill. I have to study more and more. When I could correctly understand about the process you want and found the solution, I would like to answer it. If you give me a time to find of the process you want, I'm glad. – Tanaike Jun 17 '19 at 03:05
  • @Tanaike, it's feels good to see humility and modesty, such virtues still exist today! Thanks. – sifar Jun 17 '19 at 04:24

0 Answers0