Apps script call via Knack API to object records

API & Customization
1

Here's my Apps Script code to pull a Knack object's records into a google spreadsheet. It's not perfect yet but it's progress, and I thought folks might want to see it. This will pull all records for the object when you load the spreadsheet.

  

function get_records(){
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var cell = doc.getRange('a1');
  var url = 'https://api.knackhq.com/v1/objects/object_2/records?format=html';
  var options = {
    'method':'GET',
    'headers':{
      'X-Knack-Application-Id':'your_app_ID_here',
      'X-Knack-REST-API-Key':'your_API_key_here'
    }
  };

var response = UrlFetchApp.fetch(url, options);

var data = JSON.parse(response.getContentText());


var index = 0;

for (i = 0; i < data.total_records; i++) {

val_user = data.records[i].field_9;

val_break = data.records[i].field_8;

cell.offset(index, 0).setValue(val_user);

cell.offset(index, 1).setValue(val_break);

index++;

}


}

  

2

Buenos días Oliver Holloway, me pareció muy interesante como obtienes los datos de los récords para asignarlos a una hoja de calculo, me gustaría saber mas a detalle como es que obtienes únicamente los registros antes de enviarlos a la hoja de calculo, me seria de mucha. Gracias de antemano.

3

apologies - error relates to line 23 NOT line 25

4

Hi really need some help please - total novice on scripts / programming:

my code:

 

function get_records(){

var doc = SpreadsheetApp.getActiveSpreadsheet();

var cell = doc.getRange(‘a1’);

var index = 0;


var url = ‘https://api.knackhq.com/v1/objects/object_30/records?format=html&rows_per_page=1000’;

var options = {

‘method’:‘GET’,

‘headers’:{

‘X-Knack-Application-Id’:‘MyAppId’,

‘X-Knack-REST-API-Key’:‘MyAPI’     }

};


var response = UrlFetchApp.fetch(url, options);

var data = JSON.parse(response.getContentText());


if ((data.total_records) > 0) {

cell.offset(index, 0).setValue(“Knack App One”);

index++;

var index = 0;

for (i = 0; i < (data.total_records-1); i++) {

val_statusBudget = data.records[i].field_294;

if (val_statusBudget == “Approved HAVE PO”)  {

val_parent = data.records[i].field_280;

val_bgtlkup = data.records[i].field_438;

cell.offset(index, 0).setValue(val_parent);

cell.offset(index, 1).setValue(val_bgtlkup);

cell.offset(index, 2).setValue(val_statusBudget);

index++;

}

}

}

}

 I am getting output to the google sheet but first column displays as

<span class="551ffd27891439b562ef1c09">01KEYNOTE</span>


and code returns error:

TypeError: Cannot read property "field_294" from undefined. (line 25, file "Code"

Really appreciate a point in the right direction.

5

Hey, I'm noticing that for some reason my code doesn't fetch the very first line of data. So in the google sheet I have 3149 rows but in Knack I have 3150. Has anyone else had this issue come up? 

function get_records(){

var doc = SpreadsheetApp.getActiveSpreadsheet();

var cell = doc.getRange(‘a1’);

var index = 0;


var url = ‘https://api.knackhq.com/v1/objects/object_13/records?format=html&rows_per_page=10000’;

var options = {

‘method’:‘GET’,

‘headers’:{

‘X-Knack-Application-Id’:‘xxx’,

‘X-Knack-REST-API-Key’:‘xxx’     }

};

var range = doc.getRange(“A1:G10000”);

range.clearContent();


var response = UrlFetchApp.fetch(url, options);

var data = JSON.parse(response.getContentText());


var index = 0;

for (i = 0; i < data.total_records-1; i++) {

val_name = data.records[i].field_80;

val_quantity = data.records[i].field_81;

val_sessionorder = data.records[i].field_82;

val_itemtotal = data.records[i].field_86;

val_menuarea = data.records[i].field_94;

val_session = data.records[i].field_108;

val_company = data.records[i].field_160;

cell.offset(index, 0).setValue(val_name);

cell.offset(index, 1).setValue(val_quantity);

cell.offset(index, 2).setValue(val_sessionorder);

cell.offset(index, 3).setValue(val_itemtotal);

cell.offset(index, 4).setValue(val_menuarea);

cell.offset(index, 5).setValue(val_session);

cell.offset(index, 6).setValue(val_company);

index++;

}


}

 

6

Perfect, works great. Thank you so much. 

7

No way to pull all fields without naming them that I know of.

I believe the problem is that on your "for" statement, you should have "data.total_records-1".  You are missing the -1 to prevent it from reading past the end of the database.

Also, I don't know what the statement "id=i" is for but you need to either get rid of it or put a semi-colon at the end.

Good luck

8

Is there a way to pull in all the fields without having to name each one?

9

Great, thank you. I added all the fields I needed so now it says 

  var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response.getContentText());

var index = 0;

for (i = 0; i < data.total_records; i++) {

id=i

val_name = data.records[i].field_80;

val_quantity = data.records[i].field_81;

val_sessionorder = data.records[i].field_82;

val_itemtotal = data.records[i].field_86;

val_menuarea = data.records[i].field_94;

val_session = data.records[i].field_108;

val_company = data.records[i].field_160;

cell.offset(index, 0).setValue(val_name);

cell.offset(index, 1).setValue(val_quantity);

cell.offset(index, 2).setValue(val_sessionorder);

cell.offset(index, 3).setValue(val_itemtotal);

cell.offset(index, 4).setValue(val_menuarea);

cell.offset(index, 5).setValue(val_session);

cell.offset(index, 6).setValue(val_company);

cell.offset(index, 7).setValue(id);

index++;

}


}

 There error I get is: TypeError: Cannot read property “field_80” from undefined. (line 23, file “Code”)


10

It is only loading two fields because you only have field_80 and field_160 in the code.  You will have to add all of the field you want into your code.

What error are you getting and on what statement?

11

Fist, not the greatest at all of this so please excuse me if I ask something dumb. I'm trying to set up a google sheet to import all of my data from one object. I've combined what you all have said but I'm still getting an error and it's only importing the two fields. Any help would be greatly appreciated. I'm just trying to clear all the data and then import new   

function get_records(){

var doc = SpreadsheetApp.getActiveSpreadsheet();

var cell = doc.getRange(‘a1’);

var index = 0;


var url = ‘https://api.knackhq.com/v1/objects/object_13/records?format=html&rows_per_page=1000’;

var options = {

‘method’:‘GET’,

‘headers’:{

‘X-Knack-Application-Id’:‘myID’,

‘X-Knack-REST-API-Key’:‘MyKey’     }

};

var range = doc.getRange(“A1:D10000”);

range.clearContent();


var response = UrlFetchApp.fetch(url, options);

var data = JSON.parse(response.getContentText());


var index = 0;

for (i = 0; i < data.total_records; i++) {

val_name = data.records[i].field_80;

val_break = data.records[i].field_160;

cell.offset(index, 0).setValue(val_name);

cell.offset(index, 1).setValue(val_break);

index++;

}


}

  

12

Thanx!

13

Just add something like this in your script to clear the range you want before refreshing.  Change the cell range A1:D1000 to whatever makes sense for you. 

  var range = doc.getRange("A1:D1000");
  range.clearContent();

 

14

Thanx for this. 

I just have one question - if you could help?

I am getting the added records (from Knack updates) pulled into the google sheet, but when I delete a record in Knack, the row that record used to occupy in the Google sheet doesn't get deleted, but is rather replaced by the data of another record, so that I now have (2 or more) rows with the same data.

What should I do to fix that?


15

This was very useful!  I needed a way to notify our users when records arrived in a certain status from a few different knack apps.  I did have a couple of issues/needs and thought I would share what I found.

First problem was that the script only returned 25 rows then crashed.  This was because of paging and can be solved by adding the rows_per_page parameter to the url as show below.

The second thing is that I wanted to filter the results so I added an "if" to only include certain statuses.  

You will also notice that I added a heading to the sheet.  This way I can repeat the code for a second knack app/file and keep populating the sheet with whatever apps I like.

 

 function get_records(){

var doc = SpreadsheetApp.getActiveSpreadsheet();

var cell = doc.getRange(‘a1’);

var index = 0;


var url = ‘https://api.knackhq.com/v1/objects/object_4/records?format=html&rows_per_page=1000’;

var options = {

‘method’:‘GET’,

‘headers’:{

‘X-Knack-Application-Id’:‘your_app_ID_here’,

‘X-Knack-REST-API-Key’:‘your_API_key_here’     }

};


var response = UrlFetchApp.fetch(url, options);

var data = JSON.parse(response.getContentText());


if ((data.total_records) > 0) {

cell.offset(index, 0).setValue(“Knack App One”);

index++;

for (i = 0; i < (data.total_records-1); i++) {

val_status = data.records[i].field_3;

if (val_status == “Complete”)  {

val_name = data.records[i].field_1;

val_email = data.records[i].field_2;

cell.offset(index, 0).setValue(val_name);

cell.offset(index, 1).setValue(val_email);

cell.offset(index, 2).setValue(val_status);

index++;

}

}

}

}

 

16

Brad, Nicolas, thanks, it's really only a small bit of Apps Script code. The code fetches the contents of a Knack object, and then writes those records to a google spreadsheet. 

Apps Scrip is google's scripting language. So far as I know, it is basically just javascript with some additional tools. It lets you programmatically interact with google docs, google APIs, external APIs, and so on. Here's a good place to start.

So, in my case, I'm going to need to build records in a target object, based on data in other objects. I plan to use Apps Script to get data from my Knack objects, re-work it a bit, and then post the resulting records into the target object. If there's a better way to do this, I want to hear about it. I did try my best to figure out a way to use tasks and submit rules, but that just did not work out for me. Ultimately what I'm asking for is a cross join.

17

Indeed, this is a great share! Oliver, would you mind explaining to us a bit more what this code does exactly and what it allows you to do? Would be great for those not familiar with Google Apps.

18

Thanks for posting Oliver, the more we share our code the more functionality we'll all get out of knack.

19

Since an apps script can be its own thing, I should have mentioned that this script is attached to a google sheet. Open/create a sheet in google drive, click Tools, click Script editor, and copy the above code into the code.gs file.