Pull Data from Knowi into a Google Sheet

You can use a custom function in Google Sheets to pull in data from Knowi. Here are the steps: 

1. From your sheet that you'd like to import data from go to Extensions --> App Script. 

2. Copy and paste the following function: 

function onOpen(e) {
  var response = UrlFetchApp.fetch("https://knowi.com/api/data/{apikey}?entityName={Dataset Name}&exportFormat=csv&c9SqlFilter=select * limit 10000");
  Logger.log(response.getContentText());
  var fact = response.getContentText();
  var sheet = SpreadsheetApp.getActiveSheet();
  var csvData = Utilities.parseCsv(fact, ',');
  sheet.clearContents().clearFormats();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Replace {apikey} and {Dataset Name} with your values (API Key can be obtained from the Knowi User settings). 

3. Save it. Then click Triggers. Add a new trigger that runs this on document Open. 

4. Save Trigger. Authorize the script upon pop up. 

5. When the sheets opens, the values will be replaced with the latest from that dataset (may take a few seconds). 

Video: https://www.loom.com/share/46c758e1d3e9470f9aaabedfcbe85d59

Video run through: 

 Jay