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.
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
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 :
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 ?
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.
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)';
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:
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: