Parsing Keenio Extraction API query data into google spreadsheet

Question

How do I parse a JSON like this? This is Keenio Extraction API query data to be fetched into a Google Spreadsheet.

I tried using the following code to parse the returned JSON, but don't know how to get to access the nested objects and arrays.

function myFunction() {
  var URL = "https://api.keen.io/3.0/projects/" + 
    PROJECT_KEY + "/queries/extraction?api_key=" + 
    API_KEY + "&event_collection=" + 
    EVT_COL + "&timezone=" + 
    TIMEZONE + "&latest=" + 
    LATEST + "&property_names.........."

  var response = UrlFetchApp.fetch(URL);
  var json = response.getContentText();
  var rawdata = JSON.parse(response.getContentText());
  Logger.log(rawdata.result);
  var data = [];
  data.push(rawdata.result);
  SpreadsheetApp.getActiveSheet().appendRow(data);
}

The above simple function I wrote to parse this JSON, but I am not able to grab the nested objects and arrays using dot notation.

Another thing I observed when I was using the famous IMPORTJSON code is that the order of the columns used to appear shuffled every time, so the formulas I wrote on the returned data, used to result in reference errors.

{
  "result": [
    {
      "sg_event_id": "92-OndRfTs6fZjNdHWzLBw",
      "timestamp": 1529618395,
      "url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
      "ip": "192.168.1.1",
      "event": "click",
      "keen": {
        "timestamp": "2018-06-21T21:59:55.000Z",
        "created_at": "2018-06-21T22:00:28.532Z",
        "id": "555c1f7c5asdf7000167d87b"
      },
      "url_offset": {
        "index": 38,
        "type": "text"
      },
      "sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
      "useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
      "email": "[email protected]"
    }, {
      "sg_event_id": "bjMlfsSfRyuXEVy8LndsYA",
      "timestamp": 1529618349,
      "url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
      "ip": "192.168.1.1",
      "event": "click",
      "keen": {
        "timestamp": "2018-06-21T21:59:09.000Z",
        "created_at": "2018-06-21T21:59:39.491Z",
        "id": "555c1f7c5asdf7000167d87b"
      },
      "url_offset": {
        "index": 36,
        "type": "text"
      },
      "sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
      "useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
      "email": "[email protected]"
    }, {
      "sg_event_id": "fru_s2s1RtueuqBMNoIoTg",
      "timestamp": 1529618255,
      "url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
      "ip": "192.168.1.1",
      "event": "click",
      "keen": {
        "timestamp": "2018-06-21T21:57:35.000Z",
        "created_at": "2018-06-21T21:58:20.374Z",
        "id": "555c1f7c5asdf7000167d87b"
      },
      "url_offset": {
        "index": 29,
        "type": "text"
      },
      "sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
      "useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
      "email": "[email protected]"
    }
  ]
}

Answer 1

The JSON data you provided consists of an object with a single property, result. result contains an array of objects, each representing a record with field->value properties.

In your function, you're creating a row array and pushing the entire result array as a single element of the array.

In sheets, a row is represented by an array with one element per cell.

You need to process the result array and convert each element from an object with field->value properties to an array with one field value per element.

Here's a start:

var rawdata = JSON.parse(json);

// Flatten each record.
for (var i = 0; i < rawdata.result.length; i++) {
  rawdata.result[i] = flatten(rawdata.result[i]);  
}

// Extract headers by scanning all results.
var headers = [];
for (var i = 0; i < rawdata.result.length; i ++) {
  for (var field in rawdata.result[i]) {
    if (!rawdata.result[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);

// Convert each result object into a row array and append to output.
for (var i = 0; i < rawdata.result.length; i++) {
  var row = [];
  for (var j = 0; j < headers.length; j++) {
    if (!rawdata.result[i].hasOwnProperty(headers[j])) {
      row.push("");
    } else {
      row.push(rawdata.result[i][headers[j]]);
    }
  }
  data.push(row);
}

And here's the implementation of flatten():

function flatten(obj, opt_prefix) {
  if (typeof obj !== 'object' || obj === null) {
    Logger.log(obj);
    throw "Cannot flatten non-object";
  }
  var prefix = opt_prefix ? opt_prefix + '.' :  '';
  var result = {};
  for (var k in obj) {
    if (!obj.hasOwnProperty(k)) continue;
    if (typeof obj[k] === 'object') {
      var nested = flatten(obj[k], k);
      for (var n in nested) {
        if (!nested.hasOwnProperty(n)) continue;
        result[prefix + n] = nested[n];
      }
      continue;
    }
    result[prefix + k] = obj[k];
  }
  return result;
}

This implementation only flattens nested objects. If a nested array is present, it will cause the execution to fail. It works against your sample data, since no nested arrays are present.

Also note that in Javascript objects properties are unordered. If you want to order them, you need to do so in your code, either by sorting (e.g. see the headers.sort() call), predefining the order if the field names are known or a combination of the two.