Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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.
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
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.
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):
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
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.
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:
Thanks alot for this post. Was very helpful!