Using View-Based GET API call in Excel Data Source - How to configure multiple call parameters?

API & Customization
1

Hi,

I have been quite successful in configuring an Excel spreadsheet to suck data out of a Knack database via a tailored View in Knack. Independently of one another, I have managed to filter the records returned and I have also managed to increase the maximum number of records that get returned up to 1000 (which I understand to be the current limit and which is fine for my purposes).

What I can't seem to work out is how to configure both the filter and the rows_per_page on the Excel data source so that they operate together.

g2S6nQKcO-U44sGWjlvwMg.jpeg809×710

As you can in the screenshot above, I have specified ?rows_per_page=1000, which successfully overrides the default of 25 records per page on the View. If I delete that URL part and substitute it with the following:

?filters=[{"field":"field_520", "operator":"contains", "value":"WS3"}]

...this successfully filters the records down as I need, but I only get the first 25 records returned (or 100, if I increase the initial records per page to the maximum in the View definition, using the Knack Builder).

What I need to know is how to specify both these options within the URL, i.e. the rows_per_page plus the filter, using the URL parts fields that Excel provides (as in the above screenshot)

I have tried working this out from the API Developer's Guide and also by just messing around with likely candidates, but none of my attempts to do this have worked. If anyone in the know could assist me with this I would be most grateful.

Kind regards,

Steve

2

Hi Steve,

Thanks a lot for that :). I was trying to find a way to not expose the API key actually since I share my Excel file with others unfortunately. I did manage to download my data through the API though (while still exposing my API Key in the workbook) - so thanks for that.

3

Hi again Ting Ling,

Just been having another look and I am beginning to think that the way I created the data source for my query was by using Excel's 'From Web' link on the Data tab and then selecting the 'Advanced' radio button option. This enabled me to use the 'Add Header' option to add the App ID and API Key and their associated values. See below:

mKobP3f4tnqqtwEks5b6Vw.png807×592

Kind regards,

Steve

4

Hi Ting Ling,

I saw you post and have just spent the last three quarters of an hour messing around in my Excel spreadsheet, trying to remember how I got this stuff to work! Like you, I don't really know Power Bi, I was just messing around in Excel, which bled in to using Power Bi functionality, as part of what I was trying to achieve. One thing I am pretty certain of is that I didn't get into using tokens, because my app was using single sign-in for all pages within it. From recollection, all I had to do was include headers for the X-Knack-Application-ID and the Knack-REST-API-Key when defining the Data Source in anonymous sign-in mode within Excel. I have been trying to get back to the dialogue within Excel where I originally specified these headers, but I just can't find it now. I have found proof that I did enter these keys, however, because when I open the query in the advanced editor I can see them. Here is a screenshot of the Advanced Editor:

RxeqxFIDe0WdX_YgNok0GQ.png1028×549

And here is the content of the query, except I have removed the actual X-Knack-Application-ID and Knack-REST-API-Key values, otherwise anyone could get into my database:

let
Source = Json.Document(Web.Contents("https://api.knack.com/v1/pages/" & "scene_78" & "/views/" & "view_795" & "/records" & "?rows_per_page=100" & "&filters=[{""field"":""field_760"", ""operator"":""contains"", ""value"":""WS2""}]", [Headers=[#"X-Knack-Application-ID"="XXXXXXXXXXXXXXXXXXXXXXXX", #"X-Knack-REST-API-Key"="XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"]])),
records = Source[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"field_387_raw", "field_321_raw", "field_354_raw", "field_322", "field_39", "field_265", "field_249", "field_352_raw", "field_394"}, {"Column1.field_387_raw", "Column1.field_321_raw", "Column1.field_354_raw", "Column1.field_322", "Column1.field_39", "Column1.field_265", "Column1.field_249", "Column1.field_352_raw", "Column1.field_394"})
in
#"Expanded Column1"

I guess one workaround for your current problem might be to set up a page/view within your app that does not require a login and then use the App ID and REST API key to access your data, as I have done above. Sorry I can't really be of more help, I was hoping to have been able to send you some screen shots of how I set this up in Excel, but I just can't work our how I did it, right now!

Good luck with it. I hope the above information might prove to be of some help, in some way.

Kind regards,

Steve

5

Hi guys,

I'm trying to set up my Power Bi to read data from Knack. I think I get the View-based GET request above (well I'm still fiddling with it) - but I was wondering how should I modify the url parts if there's a login required to view the page ?

I read the developer docs saying I need a user token - how do you get Power BI/ Power Query to send a POST request to get the token?

I'm not very good yet at using Power Bi

Thanks for your time anyway.

6

Hi again Tony,

Did the extra testing and can confirm it's all working. Very grateful to you.

Cheers Steve

7

Hi Tony,

Brilliant! I need to do a bit of more detailed testing (i.e. reduce the view options back to displaying 10 records per page, then re-test), but your suggestion didn't cause the connection to fail, which was happening on my previous attempts to add both paramters, so I would say that you've cracked it for me! Many thanks,

Re WS3, no, it's not random, it's the unique value I am using to filter the records that are returned in the view.

Thanks again,

Kind regards,

Steve

8

I haven't tried or tested this combination so I am assuming that it could be similar to something I previously tried: use &

?filters=[{"field":"field_80","operator":"is","value":"5ab144f1f853106f01be0e92"}]&sort_field=field_79&sort_order=asc

Digressing from your question, was WS3 just a random string in your filter?

Tony