title: Extract Keenio Data into google spreadsheet
body: <p>I am currently using <strong><a href="https://github.com/bradjasper/ImportJSON" rel="nofollow noreferrer">ImportJSON</a></strong> to import Sendgrid Email with data Keenio Extraction Query API URL by calling the ImportJSON function in a Google Spreadsheet cell of <strong>Sheet DATA</strong>.   </p>
  
  <pre><code>=ImportJSON("https://api.keen.io/3.0/projects/"&amp; PROJECT_KEY &amp; "/queries/extraction?api_key=" &amp; API_KEY &amp; "&amp;event_collection=" &amp; EVT_COL &amp; "&amp;timezone=" &amp; TIMEZONE &amp; "&amp;latest=" &amp;  LATEST &amp; "&amp;property_names..........", PTDATA!$AB$1)
  </code></pre>
  
  <p>In <strong>Sheet PTDATA</strong>, 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 <strong>ReCalcCell</strong> custom function.</p>
  
  <pre><code>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);
  }
  </code></pre>
  
  <p><strong>PTDATA sheet</strong> has specific column header names for which i want to pull the data from <strong>DATA sheet</strong>. Towards the right of these columns, i have other calculation columns which work on these specific columns. </p>
  
  <p>Since the columns in <strong>DATA sheet</strong> always appear in a random / shuffled order, i had to write a small custom function <strong>GCL</strong> which takes in a header name and returns its datarange address from <strong>DATA sheet</strong> as a string.</p>
  
  <pre><code>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 &lt;= 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 &gt; 0)
    {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  }
  </code></pre>
  
  <p>Then i use the custom function <strong>GCL</strong> in each specific column to get it's datarange. Once data is populated, the <strong>PDATA sheet</strong> is used to create different Pivots for reporting purposes.</p>
  
  <blockquote>
    <p>=ARRAYFORMULA(INDIRECT(GCL(A1,$AB$1)))</p>
  </blockquote>
  
  <p>The problems i am facing is that though the ImportJSON data populates the <strong>DATA sheet</strong>:</p>
  
  <p><strong>DATA Sheet:</strong>
  <a href="https://i.stack.imgur.com/vehh3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vehh3.png" alt="enter image description here"></a></p>
  
  <ul>
  <li>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 <strong>PDATA sheet</strong> to pull in specific columns using the <strong>custom function GCL</strong>.</li>
  <li>The <strong>custom function GCL</strong> does not always refresh and most of the time shows <strong>#Ref error</strong>.</li>
  </ul>
  
  <p><strong>PDATA Sheet:</strong>
  <a href="https://i.stack.imgur.com/x3Cjc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/x3Cjc.png" alt="enter image description here"></a></p>
  
  <p>BTW, my <strong>JSON output</strong> from Keenio looks like this:</p>
  
  <pre><code>{
  "result":
  [
  {
  "sg_event_id": "92-OndRfTs6fZjNdHWzLBw",
  "timestamp": 1529618395,
  "url": "https://noname.com?utm_campaign=website&amp;utm_source=sendgrid.com&amp;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": "no.name@noname.com"
  }, {
  "sg_event_id": "bjMlfsSfRyuXEVy8LndsYA",
  "timestamp": 1529618349,
  "url": "https://noname.com?utm_campaign=website&amp;utm_source=sendgrid.com&amp;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": "no.name@noname.com"
  }, {
  "sg_event_id": "fru_s2s1RtueuqBMNoIoTg",
  "timestamp": 1529618255,
  "url": "https://noname.com?utm_campaign=website&amp;utm_source=sendgrid.com&amp;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": "no.name@noname.com"
  }
  ]
  }
  </code></pre>
  
  <p>My questions are:</p>
  
  <ul>
  <li>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.</li>
  <li>How can this code be refactored or optimized so that it can always return data to PDATA sheet columns?</li>
  <li>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?</li>
  </ul>