Qlik Community

Connectivity & Data Prep

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

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

Labels (1)
62 Replies
madhuparnadhar
Contributor III
Contributor III

Hi Arno,

I think I have a similar issue like you.

When I try to get the refresh token, I get an error in Qlik Sense “QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 401 (Unauthorized): Requested resource requires authentication.”

Can you please help me with how you resolved your error ?

Thanks and Regards,

Madhuparna Dhar

madhuparnadhar
Contributor III
Contributor III

Hi Bjorn,

Your post is written in a great detailed way and is very helpful. However I am still struggling to get the refresh token for Google Authorization.

When I try to get the refresh token, I get an error in Qlik Sense “QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 401 (Unauthorized): Requested resource requires authentication.”

Any idea why this is happening ? I think I might have done some mistake to create the client ID and client secret. I created a project in https://console.developers.google.com and under Create Credentials, I just added https://developers.google.com/oauthplayground as Authorised redirect URIs. Then I provided the client credentials that were generated under OAuth 2.0 configuration. I have also set OAuth endpoints to Custom and set Authorization endpoint/Token endpoint as specified by you. Not sure why this is still not working.

Can you please help me ?

Thanks and Regards,

Madhuparna Dhar

Bjorn_Wedbratt
Support
Support

Hi Madhuparna Dhar,

You get the refresh token from Google Playground. After you selected and authorized the APIs you exchange the Authorization code for a Refresh token and an Access Token.

The Refresh token you get can be used together with ClientID and Client Secret in the script. Access token needs to be refreshed (using the Refresh token) as it will only be valid for a limited time, however the Refresh Token will not expire, unless you revoke permissions for the OAuth client

Untitled.png

madhuparnadhar
Contributor III
Contributor III

Hi Bjorn,

In OAuth 2.0 playground, I have selected and authorised the API, received the authorization code and exchanged it with refresh and access token. But when I try to use this refresh token along with client ID and client secret in Qlik Sense script, I get error "QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 401 (Unauthorized): Requested resource requires authentication".


I may have done some mistake while getting the client ID and client secret. Can you please explain me the steps required to do for creating client ID and client secret? Below are the things that I did :


I created a project in https://console.developers.google.com.

Under Create Credentials, I selected Web application and added https://developers.google.com/oauthplayground as Authorised redirect URIs.

Then I used the client ID and client secret which I received from there in Oauth2 playground.

Bjorn_Wedbratt
Support
Support

Hi Madhuparna,

This is what I did:

So far it looks like we're doing the same when creating the OAuth client

Then in https://developers.google.com/oauthplayground

  • Press the cog-wheel at the top right and check "Use your own OAuth credentials"
  • Add Client ID and secret from above and hit Close
  • Selected Google Analytics API v3 / analytics.readonly and pressed Authorize APIs
  • Confirmed the consent screen that I trust my application to have access to Google Analytics data
  • Recieved authorization code and exchanged it to get a refresh token
  • Used refresh token together with Client ID and secret in the below script

SET vClient_id = '<insert Client ID>';

SET vClient_secret = '<insert secret>';

// Retrieved using OAuth 2.0 Playground, https://developers.google.com/oauthplayground/

SET vRefresh_token = '<insert refresh token>';

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   

My Data Connection (Google Authorization) is set up using REST Connector with a POST method against http://jsonplaceholder.typicode.com/posts‌ with Authentication Schema=Anonymous.

madhuparnadhar
Contributor III
Contributor III

Hi Bjorn,

Thanks for your reply! This works now and when I am running the above script, I am getting an access token. But when I try to use this access token to get Google analytics data, it throws me an error "QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 403 (Forbidden):

The server refused to fulfill the request."

On the other hand, if I don't use my own Oauth credentials in Oauth2 playground and just use the access token that I am getting after authorizing the API, then I am getting correct Google Analytics data.

Can you please help me in correcting what I am doing wrong ?

Thanks and Regards,
Madhuparna Dhar
madhuparnadhar
Contributor III
Contributor III

Hi Bjorn,

The Analytics API was not enabled in my Developers Console. I added it and now it is working as expected. Apologies for the confusion. And thanks again for your help!

Thanks and Regards,

Madhuparna Dhar

maryrosman
Contributor
Contributor

Hi Bjorn,

I'm still looking for some guidance on this... can you assist?

I think I have all the pieces here but I can't seem to put them together.

maryrosman wrote:


Hello! I've read through this post quite a few times and am learning as I go, but am still having trouble. I've been able to connect to my GA data using the GET, but not with the refresh token POST connection.

Additionally I've been able to connect using the POST for the refresh token successfully as well (below)...however, I can't get them to go together now.

Here is what I currently have.

  1. SET vClient_id = MY CLIENT ID;   
  2.   SET vClient_secret = CLIENT SECRET;   
  3.   SET vRefresh_token = REFRESH TOKEN;   
  4.   IF vTokenExpires <= now() THEN // if access_token expired request a new one using the refresh_token     
  5. LET vRequestBody ='';   
  6.   LET vRequestBodyvRequestBody = vRequestBody & 'grant_type=refresh_token';   
  7.   LET vRequestBodyvRequestBody = vRequestBody & '&client_id=' & '$(vClient_id)';   
  8.   LET vRequestBodyvRequestBody = vRequestBody & '&client_secret=' & '$(vClient_secret)';   
  9.   LET vRequestBodyvRequestBody = vRequestBody & '&refresh_token=' & '$(vRefresh_token)';  
  10.    
  11. CUSTOM CONNECT TO "Provider=QvRestConnector.exe; 
  12. url=https://www.googleapis.com/oauth2/v4/token; 
  13. timeout=30;method=POST;autoDetectResponseType=true
  14. keyGenerationStrategy=-1;authSchema=anonymous; 
  15. skipServerCertificateValidation=false; 
  16. useCertificate=No;certificateStoreLocation=CurrentUser; 
  17. certificateStoreName=My; 
  18. queryParameters=refresh_token%----% 
  19. 1client_id%---% 
  20. 1client_secret%--- 
  21. %1grant_type% 
  22. 2refresh_token; 
  23. queryHeaders=Authorization%2Bearer ---------; 
  24. PaginationType=None; 
  25. allowResponseHeaders=false;allowHttpsOnly=false
  26. XUserId=ALSUeJC;XPassword=JZHBBMD;"; 
  27.  
  28.  
  29. access_token:   
  30.   SQL SELECT    
  31.   "token_type",   
  32.   "access_token",   
  33.   "expires_in"   
  34.   FROM JSON (wrap on) "root"   
  35.   WITH CONNECTION (   
  36.   BODY "$(vRequestBody)"   
  37.   );   
  38.    
  39.   LET vExpiresIn = peek('expires_in',0,'access_token');   
  40.   LET vAccessToken = peek('access_token',0,'access_token');   
  41.   LET vTokenExpires = timestamp(now() + $(vExpiresIn)/86400);   
  42.   ENDIF 
  43.    

So, what comes after this? Or before it?

This is my get URL...

GET https://www.googleapis.com/analytics/v3/data/ga?ids=ga%--VIEWID--&start-date=2017-06-12&end-date=201...{MY_API_KEY}

Thanks!

Using this in Qlikview desktop.


Bjorn_Wedbratt
Support
Support

Hi Mary,

You managed to get the Access token and have that in the variable vAccessToken?

If so you need to provide the token when you create the GET request. You do this when setting up the REST connection:

- Use the link to Google API with the parameters as you have above (www.googleapis.com/analytics/v3/data...) and put that into the URL

- Make sure Method is GET

- Set Authentication Schema: Anonymous

- Add a Query Header:

Name: Authorization

Value: Bearer <your access token copied from the vAccessToken variable>

Test the connection and use the wizard to build out the SQL SELECT statement

If you inspect the generated connection string you will notice that the query header you added will be included in the connection string. We need to override this, or the same (hardcoded) access token will be used every time and will eventually time-out. To dynamically replace the query header alter the SQL SELECT statement and use WITH CONNECTION to replace the query header:

  1. FROM JSON (wrap on) "root" PK "__KEY_root" 
  2. WITH CONNECTION ( 
  3.   HTTPHEADER "Authorization" "Bearer $(vAccessToken)" 
  4. ); 
amien
Specialist
Specialist

Thanks alot for this post. Was very helpful!