json google-apps-script google-sheets sendgrid keen-io

Extract Keenio Data into google spreadsheet

I am currently using ImportJSON to import Sendgrid Email with data Keenio Extraction Query API URL by calling the ImportJSON function in a Google Spreadsheet cell of Sheet DATA.

=ImportJSON("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..........", PTDATA!$AB$1)

In Sheet PTDATA, in the last column cell i am setting a random number for ImportJSON to recalculate. The function runs on Spreadsheet open event. I have also added a custom menu to call the ReCalcCell custom function.

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  // Or DocumentApp or FormApp.
  ui.createMenu('IMPORT DATA')
  .addItem('KEENIO DATA', 'ReCalcCell')
  .addToUi();
}


function ReCalcCell(){
  var min = Math.ceil(0);
  var max = Math.floor(9999);
  var randomNum = Math.floor(Math.random() * (max - min + 1)) + min 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("PTDATA");
  sh.getRange("$AB$1").setValue(randomNum);
}

PTDATA sheet has specific column header names for which i want to pull the data from DATA sheet. Towards the right of these columns, i have other calculation columns which work on these specific columns.

Since the columns in DATA sheet always appear in a random / shuffled order, i had to write a small custom function GCL which takes in a header name and returns its datarange address from DATA sheet as a string.

function GCL(header,dummy) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("DATA");
  var headings = sheet.getRange(1, 1, 1, sheet.getLastColumn()); // get the range representing the whole sheet
  var width = headings.getWidth();
  var lrow = sheet.getLastRow();

    // search every cell in row 1 from A1 till the last column
    for (var i = 1; i <= width; i++) {
        var data = headings.getCell(1,i).getValue();
        if (data == header) {
          return ((sheet.getSheetName() + "!" + columnToLetter(i)+"2:" + columnToLetter(i) + lrow).toString()); // return the column range if we find it
           break; // exit when found
        }
    }
    return(-1); // return -1 if it doesn't exist
}

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

Then i use the custom function GCL in each specific column to get it's datarange. Once data is populated, the PDATA sheet is used to create different Pivots for reporting purposes.

=ARRAYFORMULA(INDIRECT(GCL(A1,$AB$1)))

The problems i am facing is that though the ImportJSON data populates the DATA sheet:

DATA Sheet:

The columns appear shuffled everytime, so my calculation columns cannot calculate as the references go away. This renders the pivots useless! To counter this issue, i had to create the PDATA sheet to pull in specific columns using the custom function GCL.
The custom function GCL does not always refresh and most of the time shows #Ref error.

PDATA Sheet:

BTW, my JSON output from Keenio looks like this:

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

My questions are:

Is there a way to parse the JSON result without use of ImportJSON, which has to be entered as a custom function in a cell that also depends on recalculation? ImportJSON sometimes doesn't work properly.
How can this code be refactored or optimized so that it can always return data to PDATA sheet columns?
Is there a better way of accomplishing what i want without resorting to custom functions like GCL in the PDATA Sheet or ImportJSON in DATA sheet?

Solution

How about this sample script? This script parses the values retrieved from API using UrlFetchApp and put them to the sheet "DATA". You can run this at the menu of spreadsheet. Before you run this, please put the endpoint.

Sample script :

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  // Or DocumentApp or FormApp.
  ui.createMenu('IMPORT DATA')
  .addItem('KEENIO DATA', 'ReCalcCell')
  .addItem('main', 'main')
  .addToUi();
}

function main() {
  var url = "###"; // Please put the endpoint with your token.

  var res = UrlFetchApp.fetch(url).getContentText(); // Modified
  var values = JSON.parse(res);
  var putData = values.result.map(function(e) {return [e.useragent, e.sg_event_id, e.timestamp, e.ip, e.url, e.event, e.keen.timestamp, e.keen.created_at, e.keen.id, e.url_offset.index, e.url_offset.type, e.sg_message_id, e.email]});
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("DATA");
  sheet.getRange(sheet.getLastRow() + 1, 1, putData.length, putData[0].length).setValues(putData);
}

Note :

When you use this, please put the endpoint including your token to url.
I confirmed this script using the JSON object in your question. So if the structure of the object is changed, it is required to also modify the script. Please be careful this.

Reference :

UrlFetchApp.fetch()

If I misunderstand about your issue, please tell me. I would like to modify it.

Edit 1 :

Pattern 1 :

var putData = values.result.map(function(e) {return [e.useragent, e.sg_event_id, e.timestamp, e.ip, e.url, e.event, e.keen.timestamp, e.keen.created_at, e.keen.id, JSON.parse(e["url_offset"]).index, JSON.parse(e["url_offset"]).type, e.sg_message_id, e.email]});

Pattern 2 :

var putData = values.result.map(function(e) {return [e.useragent, e.sg_event_id, e.timestamp, e.ip, e.url, e.event, e.keen.timestamp, e.keen.created_at, e.keen.id, e["url_offset"].index, e["url_offset"].type, e.sg_message_id, e.email]});

Edit 2 :

Could you please run this script and provide the values of the created file? Of course, please remove the personal information. But please don't modify the structure of the object. If you cannot do it, I would like to think of other ways.

var url = "###"; // Please put the endpoint with your token.

var res = UrlFetchApp.fetch(url).getContentText();
DriveApp.createFile("sample.txt", res, MimeType.PLAIN_TEXT)

Edit 3 :

Please copy and paste this script in your script editor, run myFunction(). Then, please show the values of file. When you run this function, please confirm whether there are NOT the same function name in your project.

function myFunction() {
  var url = "###"; // Please put the endpoint with your token.
  var res = UrlFetchApp.fetch(url).getContentText();
  DriveApp.createFile("sample.txt", res, MimeType.PLAIN_TEXT)
}

Edit 4 :

Please copy and paste this script in your script editor, run myFunction2(). Then, please show the results. When you run this function, please confirm whether there are NOT the same function name in your project.

Please confirm whether the keys and values of keen and url_offset are retrieved.

function myFunction2() {
  var url = "###";
  var res = UrlFetchApp.fetch(url).getContentText();
  var values = JSON.parse(res);
  for (var key in values.result[0]) {
    Logger.log("key: %s, value: %s", key, values.result[0][key])
    if (typeof values.result[0][key] == "object") {
      for (var dkey in values.result[0][key]) {
        Logger.log("key: %s, dkey: %s, value: %s", key, dkey, values.result[0][key][dkey])
      }
    }
  }
}

Edit 5 :

Please copy and paste this script in your script editor, run myFunction3(). Then, please show the results. When you run this function, please confirm whether there are NOT the same function name in your project.

function myFunction3() {
  var url = "###"; // Please set this.
  var res = UrlFetchApp.fetch(url).getContentText();
  var values = JSON.parse(res);
  var obj = [];
  for (var i = 0; i < values.result.length; i++) {
    var temp = {};
    var v = values.result[i];
    for (var key in v) {
      temp[key.replace(/_/g, "")] = v[key];
      if (typeof v[key] == "object") {
        for (var dkey in v[key]) {
          temp[key.replace(/_/g, "") + dkey.replace(/_/g, "")] = v[key][dkey];
        }
      }
    }
    obj.push(temp);
  }
  var putData = obj.map(function(e) {return [e.useragent, e.sgeventid, e.timestamp, e.ip, e.url, e.event, e.keentimestamp, e.keencreatedat, e.keenid, e.urloffsetindex, e.urloffsettype, e.sgmessageid, e.email]});
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("DATA");
  sheet.getRange(sheet.getLastRow() + 1, 1, putData.length, putData[0].length).setValues(putData);
}