Hello everyone, Happy New Year to you all.

For the past few days, I’ve been scratching around various documentation and videos surrounding Qlik Sense SaaS (Cloud) to retrieve data from the REST API. The reason is to store user information in QVD files so I can use them later in Section Access Reduction. Like most things I do if there’s an easier way to do this then please let me know 😉

The Qlik REST Connector enables Qlik Sense and QlikView to efficiently load data into Qlik apps from a REST data source … here is the Qlik Help Page, there is a lot of information there although most examples I could find rely on no or basic authentication. When connecting to the Qlik API you do need to authorise yourself, there are some ways to do this although if you want to scale your solution you’ll probably want to set up an OAuth token and then retrieve an access token from it.

So let’s assume you have an OAuth token with a Client_ID and Client_Secrate. Let’s also assume its set up with the correct privileges !!…

Do you have access to postman or perhaps like me you can use Python Code to check to see if it works… It’s a handy step to check as it will save you a headache later.

Test in Python

Here’s a simple Python program which you can use to check you can retrieve your temporary Access Token

import requests

client_id ='123456789'
client_secret = '123456789'

def get_access_token(host: str, client_id: str, client_secret: str):
   body = {
      "client_id":client_id,
      "client_secret":client_secret,
      "grant_type":"client_credentials"
   }
 
   r = requests.post(f'{host}/oauth/token', data=body)
   token = dict(r.json())['access_token']
   return token

host = 'https://tenanturl.us.qlikcloud.com'
response = get_access_token(host,client_id,client_secret)

print(response)

Change the highlighted lines to your credentials, run the code and you should see a long token printed into your console window.

Move the code to Qlik Sense

We can now do the same thing in QLIK, open up a new app, create a New Data Connection, and Select REST:

• Add in a dummy POST URL (I created mine in a Postman Mock Server)
• Leave the request body empty
• Select POST as the method

• Select Allow “WITH CONNECTION”
• Call it POST

If you’re not sure how to create a Mock Server in Postman here’s a video by Christof Schwarz “Qlik REST Connector Deluxe”, chapters one and two describe setting this up. I had to re-watch it several times as he goes fast although I got there in the end.

You could also search for one of the many examples of free-to-use API’s for a POST endpoint. As long as you don’t need authorisation you’re good. The point here is just to create a connection and it passes the connection test. We will change all the attributes in the code (that’s why you checked Allow “WITH CONNECTION”

The good thing about Postman is you’ll get an example you can connect to with your table fields and saves loads of time! See the below code as an example. As this connected to my PostMan mock-up where I selected data I could see all the fields I needed.

LIB CONNECT TO 'POST';

RestConnectorMasterTable:
SQL SELECT 
	"access_token",
	"scope",
	"token_type",
	"expires_at",
	"expires_in"
FROM JSON (wrap on) "root"

Next, we have to add the WITH CONNECTION stuff…

LIB CONNECT TO 'POST';

RestConnectorMasterTable:
SQL SELECT 
	"access_token",
	"scope",
	"token_type",
	"expires_at",
	"expires_in"
FROM JSON (wrap on) "root"
with CONNECTION (
    URL "$(vBaseAPIurl)/oauth/token"
    ,HTTPHEADER  "accept" "application/json" 
    ,HTTPHEADER  "content-type" "application/json"
    ,BODY "$(vBody)"

);

As you can see I’ve parametrized the URL and BODY, this will make the code more usable especially if you have more than one tenant. Here are my variables, as before change to reflect your credentials:

let vBaseAPIurl = 'https://yourtenanturl.us.qlikcloud.com';

let vBody = '{
      `client_id`:`123456789`,        
      `client_secret`:`123456789`,
      `grant_type`:`client_credentials`
   }';

LET vBody = replace(vBody,'`',chr(34)&chr(34));

We replace the escape character ` with double-double quotes.

If you run the script now it should work, you’ll receive one record which will contain a value for access_token which you can store as a variable. This is your temporary Bearer token you can now use with further requests to the Qlik API. For example, let us retrieve the users…

GET /api/v1/users

Create the request in Postman first and use the bearer token you just retrieved (remember it expires). Save your response as an example, mock that up and use your URL (eg https://566ca603-4b0a-b381-f43487392aea.mock.pstmn.io/api/v1/users <- Don’t use this, use yours!) to create a new REST Data Connection in Qlik, this time for GET (remember to check: Allow “WITH CONNECTION”)

With the new Connection select Get Data, select all the tables from the root and insert it into your script…

You’ll have a lot of code but look for the end of the SQL code, as we’re going to insert the

...
FROM JSON (wrap on) "root" PK "__KEY_root"
with CONNECTION (
    URL "$(vBaseAPIurl)/api/v1/users"
    ,HTTPHEADER  "accept" "application/json" 
    ,HTTPHEADER "Authorization" "Bearer $(vAccessToken)"
);

As you can see I’ve paramitised the access token we just got into a variable and inserted it into the HTTP header. Running the script now will bring all your users from the Tenant into Qlik tables.

I hope this helps

Richard