Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Not applicable

REST CONNECTOR locate the Authorization token

I am trying to connect to google analytics by following this guide http://help.qlik.com/Connectors/en-US/connectors/#../Subsystems/REST_connector_help/Content/1.0/Crea...

But I get stuck on step 7. Open Developer tools in Google Chrome and locate the Authorization token

How do i locate the token?

Tags (2)
1 Solution

Accepted Solutions
Support
Support

Re: REST CONNECTOR locate the Authorization token

Hi Karl,

Sorry about the delay in responding. I've been playing around a bit with this in Qlik Sense as well, and I managed to alter the parameters for the connection using WITH CONNECTION, in a similar way as above. This worked even in Legacy mode (which was a surprise to me).

There is one big hurdle though and that is when creating Data connections; the connection must be successful or you cannot save the settings. Even when pressing "Save" the connection is checked and if it fails settings won't be saved.

When working with APIs you cannot hardcode parameters in the URL when setting up the connection, basically because not all parameters may be known at this stage.

To get around this I simply created two connections using the REST Connector, one for GET (LIB: Google Analytics API) and one for POST (LIB: Google Authorization) and used an open JSON server at jsonplaceholder.typicode.com wheen creating the connections (you cannot alter the Method GET/POST using WITH CONNECTION). I then followed the same process as above, and used WITH CONNECTION to alter the LIB settings and pointed the URL to the entry-points for Google APIs. Here's a small example script for Sense:

IF vTokenExpires <= now() THEN

  LET vRequestBody ='';

  LET vRequestBody = vRequestBody & 'grant_type=refresh_token';

  LET vRequestBody = vRequestBody & '&client_id=' & '$(vClient_id)';

  LET vRequestBody = vRequestBody & '&client_secret=' & '$(vClient_secret)';

  LET vRequestBody = vRequestBody & '&refresh_token=' & '$(vRefresh_token)';

  LIB CONNECT TO 'Google Authorization';

  access_token:

  SQL SELECT

  "access_token",

  "token_type",

  "expires_in"

  FROM JSON (wrap on) "root"

    WITH CONNECTION (

    URL "https://www.googleapis.com/oauth2/v4/token",

    HTTPHEADER "Content-Type" "application/x-www-form-urlencoded",

  BODY "$(vRequestBody)"

  );

  LET vExpiresIn = peek('expires_in',0,'access_token');

  LET vAccessToken = peek('access_token',0,'access_token');

  LET vTokenExpires = timestamp(now() + $(vExpiresIn)/86400);

ENDIF 

Next step would be to dynamically render the URL to query Google Analytics, using variables. Not done here yet, so will get back to you with that part.

58 Replies
hemhund2016
Contributor

Re: REST CONNECTOR locate the Authorization token

Hi,

Which browser you are using?

Not applicable

Re: REST CONNECTOR locate the Authorization token

‌chrome and firefox.

Not applicable

Re: REST CONNECTOR locate the Authorization token

I have the exact same problem. Can anyone please answer to this question?

Support
Support

Re: REST CONNECTOR locate the Authorization token

After reading a lot of googles help on this it seems like you have to use OAuth 2. Here you get a token that are valid for a certain time and then expires.

The token is generated by google on request when asked for  by the client.

I can generate a token from the OAuth Playground but I can't get that to work using the rest connector in Qlik View.

Is it still possible to connect to google APIs?

Support
Support

Re: REST CONNECTOR locate the Authorization token

Hi Maria,

That's correct, you need to use OAuth2 and get an access token to access google APIs. One can use OAuth 2.0 Playground as mentioned above to request an access token, together with a refresh token.

For a full understanding of he OAuth2 process there is great documentation at Using OAuth 2.0 to Access Google APIs  |  Google Identity Platform  |  Google Developers

Now, there are some challenges with some of the steps in the process when using the REST Connector, although the playground will get you started.

One is how to deal with the refresh token. As you pointed out, the access token will expire after 3600 seconds and needs to be renewed. To request a new access token, basically a POST request needs to be sent to https://www.googleapis.com/oauth2/v4/token including:

  • refresh_token
  • client_id
  • client_secret
  • grant_type (with value = refresh_token)

This will return a new access token with an expire time:

{

  "access_token":"1/fFBGRNJru1FQd44AzqT3Zg",

  "expires_in":3920,

  "token_type":"Bearer",

}

A script in QlikView using the REST Connector to perform the above could look like:

SET vClient_id = 'your client id';

SET vClient_secret = 'your client secret';

SET vRefresh_token = 'your refresh token from OAuth playground';

IF vTokenExpires <= now() THEN // if access_token expired request a new one using the refresh_token

  LET vRequestBody ='';

  LET vRequestBody = vRequestBody & 'grant_type=refresh_token';

  LET vRequestBody = vRequestBody & '&client_id=' & '$(vClient_id)';

  LET vRequestBody = vRequestBody & '&client_secret=' & '$(vClient_secret)';

  LET vRequestBody = vRequestBody & '&refresh_token=' & '$(vRefresh_token)';

  CUSTOM CONNECT TO "Provider=QvRestConnector.exe;

  url=https://www.googleapis.com/oauth2/v4/token;

  timeout=30;method=POST;

  autoDetectResponseType=0;

  keyGenerationStrategy=0;

  useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;

  certificateStoreName=My;

  queryHeaders=Content-Type%2application/x-www-form-urlencoded;

  PaginationType=None;XUserId=IWRHaYA;XPassword=dAXccDB;";

  access_token:

  SQL SELECT

  "token_type",

  "access_token",

  "expires_in"

  FROM JSON (wrap on) "root"

  WITH CONNECTION (

  BODY "$(vRequestBody)"

  );

  LET vExpiresIn = peek('expires_in',0,'access_token');

  LET vAccessToken = peek('access_token',0,'access_token');

  LET vTokenExpires = timestamp(now() + $(vExpiresIn)/86400);

ENDIF

You know have a script the checks expires_in to see if the access_token is still valid, if not a new token is requested.


According to Google documentation, the access token can be used to make calls to Google API on behalf of a user or service account. To do this the access token should be included in the request as an Authorization: Bearer HTTP header.


To use our refreshed access token when making calls to Google API, we need to include it in the request. This can be achieved using the WITH CONNECT statement in the SELECT statement generated by the REST CONNECTOR. As we have our access token in a variable (vAccessToken) from above, we can inject this using a script similar to:


RestConnectorMasterTable:

SQL SELECT

  "kind" AS "kind_u0",

  "username",

  "totalResults",

  "startIndex",

  "itemsPerPage",

  "__KEY_root",

  (SELECT

  "id",

  "kind",

  "selfLink",

  "name",

  "created",

  "updated",

  "__KEY_items",

  "__FK_items",

  (SELECT

  "__KEY_permissions",

  "__FK_permissions",

  (SELECT

  "@Value",

  "__FK_effective"

  FROM "effective" FK "__FK_effective" ArrayValueAlias "@Value")

  FROM "permissions" PK "__KEY_permissions" FK "__FK_permissions"),

  (SELECT

  "type",

  "href",

  "__FK_childLink"

  FROM "childLink" FK "__FK_childLink")

  FROM "items" PK "__KEY_items" FK "__FK_items")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (

  HTTPHEADER "Authorization" "Bearer $(vAccessToken)"

);


Hope the above helps on how to use the access token and refresh token when connecting to Google APIs

Not applicable

Re: REST CONNECTOR locate the Authorization token

Great post Björn,

The custom connect approach doesn't seem to be implemented in Qlik Sense. "CONNECTs other than LIB CONNECT are not available in this script mode"

I got it working in Qlik Sense, using the OAuth2 Playground before, but I wasn't able to set the parameters in the script so I could change the period I requested so I stopped trying. I needed to change the parameters manually in the connection, so using a schedule task didn't work.

Any ideas?

Vänta på funktioner att bli implementerade är Qlik Senses användarens vardag.

Not applicable

Re: REST CONNECTOR locate the Authorization token

Legacy mode is not an option, since I am running Qlik Sense Server

chrisbrain
Valued Contributor

Re: REST CONNECTOR locate the Authorization token

Is Legacy mode not available on Qlik Sense server? I thought it was.

Not applicable

Re: REST CONNECTOR locate the Authorization token

I don't want to enable it since the security implications.