Solved: Re: REST CONNECTOR locate the Authorization token - Page 6 - Qlik Community

Report Inappropriate Content · ‎2015-12-03

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?

madhuparnadhar · ‎2018-11-05

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 · ‎2018-11-05

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 · ‎2018-11-06

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

madhuparnadhar · ‎2018-11-06

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 · ‎2018-11-07

Hi Madhuparna,

This is what I did:

Created a project in https://console.developers.google.com
Selected OAuth client ID (in the drop-down)when creating the credentials
Configured Consent Screen, here i just added an Application name
Selected Web application and added a name. Then https://developers.google.com/oauthplayground as Authorized redirect URIs

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 · ‎2018-11-07

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 · ‎2018-11-07

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

Anonymous · ‎2018-11-07

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.


SET vClient_id = MY CLIENT ID;    
  SET vClient_secret = CLIENT SECRET;    
  SET vRefresh_token = REFRESH TOKEN;    
  IF vTokenExpires <= now() THEN // if access_token expired request a new one using the refresh_token      
LET vRequestBody ='';    
  LET vRequestBodyvRequestBody = vRequestBody & 'grant_type=refresh_token';    
  LET vRequestBodyvRequestBody = vRequestBody & '&client_id=' & '$(vClient_id)';    
  LET vRequestBodyvRequestBody = vRequestBody & '&client_secret=' & '$(vClient_secret)';    
  LET vRequestBodyvRequestBody = vRequestBody & '&refresh_token=' & '$(vRefresh_token)';   
    
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;  
url=https://www.googleapis.com/oauth2/v4/token;  
timeout=30;method=POST;autoDetectResponseType=true;  
keyGenerationStrategy=-1;authSchema=anonymous;  
skipServerCertificateValidation=false;  
useCertificate=No;certificateStoreLocation=CurrentUser;  
certificateStoreName=My;  
queryParameters=refresh_token%----%  
1client_id%---%  
1client_secret%---  
%1grant_type%  
2refresh_token;  
queryHeaders=Authorization%2Bearer ---------;  
PaginationType=None;  
allowResponseHeaders=false;allowHttpsOnly=false;  
XUserId=ALSUeJC;XPassword=JZHBBMD;";  
  
  
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  
    


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 · ‎2018-11-08

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:

FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
HTTPHEADER "Authorization" "Bearer $(vAccessToken)"
);

amien · ‎2019-11-22

Thanks alot for this post. Was very helpful!